Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1740449
  • 博文数量: 310
  • 博客积分: 6853
  • 博客等级: 准将
  • 技术积分: 2833
  • 用 户 组: 普通用户
  • 注册时间: 2005-08-04 16:41
文章分类

全部博文(310)

文章存档

2013年(17)

2012年(42)

2011年(76)

2010年(71)

2009年(99)

2007年(2)

2006年(1)

2005年(2)

分类: Oracle

2011-03-28 18:06:49

ORACLE LOB存储

    不同于普通SEGMENT 的EXTENT, LOB 使用CHUNK来作为自己的空间扩展分配单元,实际上CHUNK也是由N个连续的BLOCK组成,默认CHUNK SIZE 是一个BLOCK。
    数据块跟CHUNK管理信息也存在于BLOCK中,这些管理开销(BlockOverhead)也会占用60字节的空间,比如BLOCK HEADER , LOB HEADER,校验和等信息,这就是为什么我们新建的一个CHUNK 8192的EMPTY LOB ,用dbms_lob.getchunksize()显示的却是8132的原因。
SQL> drop table lyn.water;

Table dropped.

SQL> create table lyn.water(id number,des clob)     
2 lob (des) store as water_des(chunk 8192);

Table created.

SQL> set long 3000
SQL> select dbms_metadata.get_ddl('TABLE','WATER','LYN') from dual;

DBMS_METADATA.GET_DDL('TABLE','WATER','LYN')
--------------------------------------------------------------------------------

CREATE TABLE "LYN"."WATER"
   (    "ID" NUMBER,
        "DES" CLOB
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGIN
G
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POO
L DEFAULT)
TABLESPACE "SYSTEM"
LOB ("DES") STORE AS "WATER_DES"(

DBMS_METADATA.GET_DDL('TABLE','WATER','LYN')
--------------------------------------------------------------------------------
TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 PCTVER
SION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT))

SQL> insert into lyn.water values(1,empty_clob());

1 row created.

SQL> select dbms_lob.getchunksize(des) from lyn.water;

DBMS_LOB.GETCHUNKSIZE(DES)
--------------------------
                      8132

    假如DB_BLOCK_SIZE是8K,CHUNK SIZE设成32K,那真正用来存储数据的可用空间就是32*1024-60*4=32528 bytes.如果我们要存储500K的数据,就需要512000/32528=15.74=16 chunks。
    另外一个存储结构是LOB INDEX。一个LOB INDEX ENTRY最多可以指向8个CHUNK(NumLobPerIndexEntry=8),每个Lob Index Entry会占用46 bytes的管理开销(LobIndexEntryOverhead).
    最后一个影响LOB存储空间的是PCTVERSION跟RETENTION参数,这2个参数都是为了提供consistent read ,防止ora-1555跟ora-22924错误,如果LOB不需要读取BEFORE IMAGE或者是READ ONLY,大可以将PCTVERSION 设成0来提高空间利用。默认的PCTVERSION 是10,RETENTION则参照UNDO_RETENTION,并且RETENTION不会随UNDO_RETENTION的改变而改变,需要手动’同步’RETENTION。一般用的还是PCTVERSION。另外设置这2个参数的时候也有个小BUG http://hi.baidu.com/kywinder/blog/item/79da5d0c1961bee937d122e0.html

The steps for calculating LOB tablespace usage are as follows:
1. Calculate the number of chunks a file will take up by figuring the number of blocks per chunk and then subtracting the BlockOverhead (60 bytes) from the chunk size to get the available space per chunk.
2. Divide the file size by the available space per chunk to get the number of chunks. 
chunks = roundup(FileSize/(ChunkSize-((ChunkSize/BlockSize) * 
BlockOverhead)))
                      
For example, if FileSize = 100,000, ChunkSize = 32768, Blocksize = 8192, and BlockOverhead = 60, then Chunks = roundup (100000 /(32768 - ((32768 / 8192) * 60)))= 4 Chunks.
3. Calculate the amount of disk space for a file by multiplying the number of chunks times the chunk size and then multiplying that result by the PCTVERSION factor. Then add the space for NumLobPerIndexEntry (8) and LobIndexEntryOverhead (46 bytes). 
FileDiskSpaceInBytes = roundup(chunks*ChunkSize*PctversionFactor) +
roundup(chunks/NumLobPerIndexEntry*LobIndexEntryOverhead)
                     
Continuing the example from above, chunks = 4, ChunkSize = 32768, PctversionFactor = 1.1, NumLobPerIndexEntry = 8, and LobIndexEntryOverhead = 46, so FileDiskSpaceInBytes = roundup (4 * 32768 * 1.1) + (roundup(4/8) * 46) = 144226 FileDiskSpaceInBytes.
4. Calculate the total disk space used for file storage by summing up the application of the above formulas for each file to be stored in the LOB. 
     TableSpaceUsage = sum(FileDiskSpaceInBytes) for all files stored

Oracle Files creates multiple LOB columns. The space calculation must be made for each tablespace based upon the amount of content that will qualify for storage in each tablespace.


阅读(1017) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册