Chunk Size Estimations
Kasturi, September 24, 2001 - 11:30 am UTC
Hello Tom,
Thanks for the reply. I was trying to find out why this particular table is taking so much of space in the database with just 8000 rows in it.
So I used
alter table move ...
lob(y) store as (CHUNK 1024)... because the lob segment is storing email attachments in it. But How do i really estimate what the perfect size of the chunk is? But i cannot do this in a production environment.
Finally my question is How do i size a table with lob columns in it.
September 24, 2001 - 12:54 pm UTC
the chunk size is always an even multiple of the blocksize.. If you have a 4k block -- the minimum chunk is 4k. chunks are allocated in blocks -- the data from one lob will be on a block all by itself -- no other lob will be on the same block, ever.
If the lobs are so small, smaller then 4000 bytes, you can have them stored inline .
In order to estimate the perfect chunk size you would need to know exactly how big your data was. In order to use the least space, use a chunk size that is your blocksize.
To size a table -- see
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:406873025890 <code>
...
Varchar2 to CLOB migration
Andre Whittick Nasser, October 31, 2001 - 2:49 pm UTC
Tom,
We are changing some character columns to CLOB columns. We are aware of the TO_LOB() function.
Regarding space management, we know about the ENABLE/DISABLE STORAGE IN ROW and STORAGE clauses, as well as the behavior regarding 4000-byte issues.
The problem is that varchar2 is self-managed in terms of compression and expansion.
However, with LOBs, it seems I have to approaches as to shrinking:
a) When the LOB is in-line, it will only shrink it by using the DBMS_LOB.TRIM procedure.
b) When it's out-of-line, I must regard the LOB as any other segment, having to explicitly deallocate extents.
Questions:
1) Am I correct as to a) and specially b) ?
2) I did not find a way to do this explicit deallocation. How is it done ?
3) Is there a chance that CLOB data will be "scattered" along it, so that there will be "holes", as when I delete rows from a table ?
4) In the latter case, should I reorganize the data ? How to do it ?
Thanks for your holy patience !
October 31, 2001 - 3:25 pm UTC
You don't need to_lob() to go from varchar2 to CLOB. I simple update or insert will do it. In 9i, an even simplier "alter table modify" will do it.
Think of a LOB just like an OS file (the underlying mechanisms are really a file system!). When you write to a file and make it grow to 100m, it'll be 100m -- you can seek all over it and change bits and bytes here and there but its a 100m file. If you want to "shrink" it you can, if you want to grow it you can. But changing the DATA within has no effect on the size. A lob is exactly the same in this regards.
You have to use trim or set it equal to a different lob or string all together:
DOC>create table t ( user_id int, username clob );
DOC>
DOC>insert into t select user_id, username from all_users;
DOC>*/
ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> select user_id, username, dbms_lob.getlength(username) from t where user_id = uid;
USER_ID USERNAME DBMS_LOB.GETLENGTH(USERNAME)
---------- ------------------------------ ----------------------------
46612 OPS$TKYTE 9
ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> update t set username = rpad('*',4000,'*') where user_id = uid;
1 row updated.
ops$tkyte@ORA8I.WORLD> select user_id, dbms_lob.getlength(username) from t where user_id = uid;
USER_ID DBMS_LOB.GETLENGTH(USERNAME)
---------- ----------------------------
46612 4000
ops$tkyte@ORA8I.WORLD> update t set username = user where user_id = uid;
1 row updated.
ops$tkyte@ORA8I.WORLD> select user_id, dbms_lob.getlength(username) from t where user_id = uid;
USER_ID DBMS_LOB.GETLENGTH(USERNAME)
---------- ----------------------------
46612 9
ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> declare
2 l_data varchar2(30000);
3 begin
4 l_data := rpad( '*', 30000, '*' );
5 update t set username = l_data where user_id = uid;
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA8I.WORLD> select user_id, dbms_lob.getlength(username) from t where user_id = uid;
USER_ID DBMS_LOB.GETLENGTH(USERNAME)
---------- ----------------------------
46612 30000
ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> declare
2 l_data varchar2(30000);
3 begin
4 l_data := rpad( '*', 3000, '*' );
5 update t set username = l_data where user_id = uid;
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA8I.WORLD> select user_id, dbms_lob.getlength(username) from t where user_id = uid;
USER_ID DBMS_LOB.GETLENGTH(USERNAME)
---------- ----------------------------
46612 3000
for example. In plsql you can manipulate upto 32k of the lob like a string in 816/817. it was 4000 bytes before that.
dbms_lob.trim is the way to explicitly "deallocate" the lob data (but you seem to have found that already ?)
There is no chance of lob data being scattered. It is allocated in chunks, chunks are added at the "end" and as you trim back -- those chunks are freed. You never "erase the middle" of a lob, you trim the end of it off.
What about out-of-line LOBs ?
Andre Whittick Nasser, October 31, 2001 - 8:26 pm UTC
Thanks for your enlightment.
But, what about, say, a CLOB segment in a LOB_DATA tablespace ?
DBMS_LOB.TRIM() only works on in-line LOBS. Let us suppose it has INITIAL 1M and NEXT 1M. It can grow up to 4GB. If I look for it in DBA_SEGMENTS, there it is, so it is a segment, all right.
For example, if I continually update this CLOB column with bigger amounts of data, the segment will grow.
Imagine there are "tons" of LOBS all around my database, always growing. Varchar2s don't "overflow". CLOBS stop short only at 4 gigs and are more prone to crowd my DB.
What is the best way to manage this expantion ? How can I deallocate the extra extents ?
October 31, 2001 - 9:09 pm UTC
where did you get the idea that dbms_lob.trim works on in-line lobs only?
ops$tkyte@ORA717DEV.US.ORACLE.COM> CREATE TABLE t (x int, y clob)
2 LOB ("Y") STORE AS (TABLESPACE "SYSTEM" DISABLE STORAGE IN ROW )
3 /
Table created.
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> declare
2 l_data varchar2(32000) default rpad( '*',32000,'*' );
3 begin
4 insert into t values ( 1, l_data );
5 end;
6 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA717DEV.US.ORACLE.COM> select x, dbms_lob.getlength(y) from t;
X DBMS_LOB.GETLENGTH(Y)
---------- ---------------------
1 32000
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> begin
2 for x in ( select * from t )
3 loop
4 dbms_lob.trim( x.y, 2 );
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA717DEV.US.ORACLE.COM> select x, dbms_lob.getlength(y) from t;
X DBMS_LOB.GETLENGTH(Y)
---------- ---------------------
1 2
The segment will not grow (well, it'll grow big enough to hold the lob and the pctversion for the lob but thats it). It will not grow wildly. You can setup very simple tests to see this.
CLOBS are ONLY as big as their length -- you divide your chunksize and take the ceil of that to figure out how much space they are using. Say its 65k with a chunksize of 16k -- you will have 5 chunks of 16k allocated to hold that clob. If you make the clob grow -- it'll use that much, if you trim it -- it'll GIVE back the space onto the free list for this segment.
Clobs MAX OUT at 4 gig, they don't stop short.
I don't no where you are getting your information from but its wrong. Its managed just like a table -- if you insert rows and we grow the table, then you delete them -- the space goes onto the freelist and will be used for the next insert. Clobs segments aren't much different from table or index segments in this regards.
Varchar2 to CLOB migration (contd)
Andre Whittick Nasser, November 01, 2001 - 7:52 am UTC
Tom,
Sorry, I think I mixed up the concepts "internal" and "in-line". Below is an extract from the Reference Guide:
TRIM Procedure
This procedure trims the value of the ***internal*** LOB to the length you specify in the newlen parameter. Specify the length in bytes for BLOBs, and specify the length in characters for CLOBs.
By internal, I understand LOBs stored in database tablespaces, as opposed to LOBs kept in OS filesystems -- these would be "external".
So, ok, I can "trim" the LOB explicitly.
But, sorry about insisting... How to avoid LOBs staying too big ?
I'll try to give a clearer example of what I am thinking of.
Ok, you showed me that the "length" of the CLOB varies according to the length of the string stored therein.
"abcd" --> length=4
"12345678901234567890" --> length=20
"x" --> length=1
But this metric is more of a "logical" nature.
If I insert, say, 1 meg, the CLOB will grow -- creating extents -- and the only thing I can do to release space is by "trimming" it.
What I was looking for is some form of behavior like what happens to rollback segments, which (should) srihnk when allocated space is not necessary any longer -- which, in "logical" terms is what VARCHAR2 is supposed to do.
We here cannot afford (or would prefer not) to keep track of a LOB that is big because some time in the past it contained a 2-gig string but now holds only ten bytes. In this case, it would maintain the old size (2 gigs) and the only thing I could possibly do is a TRIM. Right ?
In sum, the expectation of us living-dead geeks in this cold dark office facing sunny Copacabana beach is that the CLOB's behavior would approach as much as possible that of a humble VARCHAR2.
Thanks again !
November 01, 2001 - 8:44 am UTC
You can release the space back to the freelist for the segment not only by trimming it but by:
o deleting the row
o setting the lob locator to empty_clob or null
o pointing the lob locator to new data.
I'm not getting whats so hard to see here. Its just like rows in a table. you insert them, they take space. You delete them and the block will go onto the freelist for the table segement.
You insert a clob, it takes space from the lob segment. You change their size in any number of ways and their free space will go onto the freelist for that segment.
If you HAD a 2gig lob and it is now a 2 byte lob, you will have 2gig free in that log segment for subsequent lobs that come along (ANY lob in that table will reuse that space).
Look at it this way: If you HAD a 2gig table and it is now a 2 byte table, you will have 2 gig free in that table segment for subsequent inserts and updates that come along...
You don't NEED trim. If you
update t set lob_col = 'xx' where id = 5
(using BIND variables of course in place of xx and 5) and lob_col used to be 2gig, and it is now 2 bytes -- thats it, it shrunk, space released, done deal, over and out.
(so if you treat it like a varchar2 as I demonstrated over and over, you got what you want)
OTOH if you:
select lob_col into l_clob from t where id = 5 for update;
dbms_lob.write( l_clob, 2, 1, 'xx' );
AND you want it to be 2 bytes (think about it, just because you wrote 2 bytes doesn't mean the clob should be 2 bytes) you will now need to trim it. However, the comparision with the varchar2 is long gone, you've already used dbms_lob.write. Think how annoying it would be if dbms_lob.write trimmed the data -- it would be *wrong*.
The cool thing is -- if you disagree, just write your OWN dbms_lob_write subroutine that does exactly what you want. No muss, no fuss.
Varchar2 to CLOB migration (contd)
Andre Whittick Nasser, November 01, 2001 - 8:25 am UTC
Tom, me again....
In the process of migrating varchar2's to CLOBS, I would not like to lose the contents of the old varchar2 fields.
I am using a test database, and this will run on a production environment, although there is little data in it -- not a "critical" issue.
What do you think of this ?
ALTER TABLE T ADD ( AUX CLOB );
UPDATE T SET AUX=TEXT; -- TEXT is the old varchar2 column
ALTER TABLE T DROP COLUMN TEXT;
ALTER TABLE T ADD ( TEXT CLOB );
UPDATE T SET TEXT=AUX;
ALTER TABLE T DROP COLUMN AUX;
I think the only major "collateral" effect of this is the new position of the column.
( Tired of saying "thanks"... Obrigado ! -- that's in Portuguese ! )
November 01, 2001 - 8:49 am UTC
yes, that will work however since MOST programs (pre-oracle9i) will have to be changed to some degree to accomidate the clob -- you might consider renaming the column (skipping that last add and update).
Varchar2 to CLOB migration (contd)
Andre Whittick Nasser, November 01, 2001 - 9:55 am UTC
Tom,
I wrote a script for the review above:
-- Start of Script ----------------------------
set serveroutput on size 10000
set feedback off
set termout off
spool change_clob.sql
begin
for rt in ( select table_name,column_name, data_length
from user_tab_columns
where data_type='VARCHAR2' and
data_length=4000 ) loop
dbms_output.put_line('-- Tabela '||rt.table_name||' / Coluna: '||rt.column_name);
dbms_output.put_line('ALTER TABLE '||rt.table_name||' ADD ( AUX CLOB );');
dbms_output.put_line('UPDATE '||rt.table_name||' SET AUX='||rt.column_name||';');
dbms_output.put_line('ALTER TABLE '||rt.table_name||' DROP COLUMN '||rt.column_name||';');
dbms_output.put_line('ALTER TABLE '||rt.table_name||' ADD ( '||rt.column_name||' CLOB );');
dbms_output.put_line('UPDATE '||rt.table_name||' SET '||rt.column_name||'=AUX;');
dbms_output.put_line('ALTER TABLE '||rt.table_name||' DROP COLUMN AUX;');
dbms_output.put_line('--');
end loop;
end;
/
spool off
set feedback on
set termout on
-- End of Script ----------------------------
I know I could have used EXECUTE IMMEDIATE, but I had to have an output script generated.
Extract from output script:
--
-- Tabela TB_TVG_TLN_FA_CLUBE / Coluna: DE_TXT_FA_CLUBE_TXT
ALTER TABLE TB_TVG_TLN_FA_CLUBE ADD ( AUX CLOB );
UPDATE TB_TVG_TLN_FA_CLUBE SET AUX=DE_TXT_FA_CLUBE_TXT;
ALTER TABLE TB_TVG_TLN_FA_CLUBE DROP COLUMN DE_TXT_FA_CLUBE_TXT;
ALTER TABLE TB_TVG_TLN_FA_CLUBE ADD ( DE_TXT_FA_CLUBE_TXT CLOB );
UPDATE TB_TVG_TLN_FA_CLUBE SET DE_TXT_FA_CLUBE_TXT=AUX;
ALTER TABLE TB_TVG_TLN_FA_CLUBE DROP COLUMN AUX;
--
-- Tabela TB_TVG_TLN_FA_CLUBE / Coluna: DE_INFO_ADIC_FA_CLUBE_TXT
ALTER TABLE TB_TVG_TLN_FA_CLUBE ADD ( AUX CLOB );
UPDATE TB_TVG_TLN_FA_CLUBE SET AUX=DE_INFO_ADIC_FA_CLUBE_TXT;
ALTER TABLE TB_TVG_TLN_FA_CLUBE DROP COLUMN DE_INFO_ADIC_FA_CLUBE_TXT;
ALTER TABLE TB_TVG_TLN_FA_CLUBE ADD ( DE_INFO_ADIC_FA_CLUBE_TXT CLOB );
UPDATE TB_TVG_TLN_FA_CLUBE SET DE_INFO_ADIC_FA_CLUBE_TXT=AUX;
ALTER TABLE TB_TVG_TLN_FA_CLUBE DROP COLUMN AUX;
The rest of the script ran ok, but I got:
ALTER TABLE TB_TVG_TLN_FA_CLUBE DROP COLUMN DE_INFO_ADIC_FA_CLUBE_TXT
*
ERRO na linha 1:
ORA-00955: name is already used by an existing object
ALTER TABLE TB_TVG_TLN_FA_CLUBE ADD ( DE_INFO_ADIC_FA_CLUBE_TXT CLOB )
*
ERRO na linha 1:
ORA-01430: column being added already exists in table
UPDATE TB_TVG_TLN_FA_CLUBE SET DE_INFO_ADIC_FA_CLUBE_TXT=AUX
*
ERRO na linha 1:
ORA-00932: inconsistent datatypes
After that, I have:
anasser@GLBVIG> desc TB_TVG_ENT_EPISODIO
Nome Nulo? Tipo
----------------------------------------------------------- -------- ------------------------------
CD_EPISODIO_ID NOT NULL NUMBER(38)
CD_SITE_ID NOT NULL NUMBER(38)
DE_TITULO_EPIS_TXT VARCHAR2(100)
DT_EXIBICAO_EPIS_DATE DATE
DE_RESUM_EPIS_DESTAC_TXT VARCHAR2(2000)
CT_PUBLICADO_REG VARCHAR2(1)
DT_PUBLICADO_DATE DATE
DE_TXT_COMPLT_EPIS_TXT CLOB
I tried to look it up, but I got no clue...
Do you any idea what might be wrong ?
November 01, 2001 - 3:45 pm UTC
don't know whats happening to you -- you are altering a totally different table then you describe and all. I ran this and it worked just dandy:
ops$tkyte@ORA717DEV.US.ORACLE.COM> @test
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> drop table t;
Table dropped.
ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t ( x int, text varchar2(4000) );
Table created.
ops$tkyte@ORA717DEV.US.ORACLE.COM> insert into t values ( 1, 'hello world' );
1 row created.
ops$tkyte@ORA717DEV.US.ORACLE.COM> set echo off
ops$tkyte@ORA717DEV.US.ORACLE.COM> @change_clob
ops$tkyte@ORA717DEV.US.ORACLE.COM> -- Tabela T / Coluna: TEXT
ops$tkyte@ORA717DEV.US.ORACLE.COM> ALTER TABLE T ADD ( AUX CLOB );
Table altered.
ops$tkyte@ORA717DEV.US.ORACLE.COM> UPDATE T SET
2 AUX=TEXT;
1 row updated.
ops$tkyte@ORA717DEV.US.ORACLE.COM> ALTER TABLE T DROP COLUMN
2 TEXT;
Table altered.
ops$tkyte@ORA717DEV.US.ORACLE.COM> ALTER TABLE T ADD ( TEXT
2 CLOB );
Table altered.
ops$tkyte@ORA717DEV.US.ORACLE.COM> UPDATE T SET
2 TEXT=AUX;
1 row updated.
ops$tkyte@ORA717DEV.US.ORACLE.COM> ALTER TABLE T DROP COLUMN AUX;
Table altered.
ops$tkyte@ORA717DEV.US.ORACLE.COM> --
ops$tkyte@ORA717DEV.US.ORACLE.COM> set echo off
Andre - Bug
A MetaLink User, November 01, 2001 - 11:36 am UTC
Andre:
Check out bug #1769244 in MetaLink... it may be relevant.
Space Reuse in Lobsegment and Lobindex
Moorthy Rekapalli, December 13, 2004 - 6:45 pm UTC
Tom,
1) Will a lobsegment inherit PCTUSED property from the table? Reason for asking this is - when we delete rows in a table that has a CLOB, will the lobsegment's blocks be qualified for future inserts after they fall below pctused mark?
2) What about lobindex. If we delete rows from a table, there will be dead space in the Btree indexes. To re-claim the dead space, once in a while we rebuild the indexes on volatile tables. Do we need to do the same for lobindex? If so, how to do that?
3) Can we change the "disable storage in row" to "enable storage in row" and vice versa for a table that has data in it.
I learn a lot from your site and can not thank you enough.
Thanks,
Moorthy.
December 13, 2004 - 7:31 pm UTC
1) lobs don't work with pctused. A lob allocates space a CHUNK at a time (a chunk is some multiple of blocks)
a chunk is either
a) used by a lob
b) not used by a lob
there is pctversion which controls how much of the lob space is used for multi-versioning. So the lob chunks that are not used anymore are aged out of the "version" space on a FIFO manner.
So, as soon as some other modification bumps it out of the version pool, it'll be entirely up for reuse
2) nope -- there are degenerate cases whereby an index populated by a sequence, with deletes *MIGHT* (really *MIGHT*) need a coalesce. But an index on say "NAME" would not. lobs are spread out. no rebuild possible actually (you'd have to move the entire lob segment)
3) the enable/disable in row would require a "move" of the lob (which involves a move of the table). everything needs to be rebuilt to do this (things inline need to be moved out of line, things out of line could need to be moved inline...)
Question with Show_Space
A reader, July 31, 2007 - 9:08 am UTC
Tom, I am trying to use the Show_space procedure on a lob segment, but I don't quite understand what it is showing..
All rows are insert only with a majority of the rows deleted at the end of each day(approx 15k rows). So in the morning, I would think I should see alot of blocks on the free list but I don't..
Database is 9.2.0.7
Tablespace is set up with
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;
Here is the table.
CREATE TABLE ENACWSTRANSACTION
(
TR_ID NUMBER(38) NOT NULL,
TRANSACTIONID NUMBER(17) NOT NULL,
CR_TIMESTAMP TIMESTAMP(6),
ACCOUNTXML CLOB
)
TABLESPACE IC_IND02
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 4M
NEXT 4M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCOMPRESS
LOB (ACCOUNTXML) STORE AS
( TABLESPACE IC_IND02
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
NOCACHE
STORAGE (
INITIAL 4M
NEXT 4M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
);
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DESC ENAC.ENACWSTRANSACTION;
Name Null? Type
----------------------------------------- -------- ---------------------
TR_ID NOT NULL NUMBER(38)
TRANSACTIONID NOT NULL NUMBER(17)
CR_TIMESTAMP TIMESTAMP(6)
ACCOUNTXML CLOB
SQL>
SQL> SELECT COUNT (1)
2 FROM enac.enacwstransaction;
COUNT(1)
----------
1467
SQL>
SQL> SELECT ROUND (AVG (SYS.DBMS_LOB.getlength (accountxml)))
2 FROM enac.enacwstransaction;
ROUND(AVG(SYS.DBMS_LOB.GETLENGTH(ACCOUNTXML)))
----------------------------------------------
16537
SQL>
SQL>
SQL> BEGIN
2 show_space ('SYS_LOB0000054533C00004$$', 'ENAC', 'LOB');
3 END;
4 /
Unformatted Blocks ..................... 1
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 756,439
Total Blocks............................ 758,272
Total Bytes............................. 6,211,764,224
Total MBytes............................ 5,924
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 26
Last Used Ext BlockId................... 445,449
Last Used Block......................... 512
PL/SQL procedure successfully completed.
Show_space has "Full Blocks" at 756,439. There is know way this can be right. Right?
Thanks for your time..
August 02, 2007 - 9:37 am UTC
why not? space in lob segments is managed very differently.
It isn't managed in the way other data is, that is what makes "lobs" special. They do not generate undo (pctversion is unique to the lob, it controls how space it reused and such).
LOB segment size
Andrei Kübar, August 16, 2007 - 5:15 am UTC
Hello,
I have an issue with a LOB segment, which I'm struggling with the third day. I have found the same type of questions on the internet but all of them were left unanswered. I have also searched metalink and oracle forums and still no luck. So maybe You could clarify this for us..
So my test goes like this:
1. I created a tablespace called "smallblock" with 2K blocksize
2. I created a table with a CLOB type field and specified the smallblock tablespace as a storage for the LOB segment:
SCOTT@andrkydb> create table t1 (i int, b clob) lob (b) store as
t1_lob (chunk 2K disable storage in row tablespace smallblock);
3. I insert data into the table, using a bit less than 2K of data for the clob type column:
SCOTT@andrkydb> begin
2 for i in 1..1000 loop
3 insert into t1 values (mod(i,5), rpad('*',2000,'*'));
4 end loop;
5 end;
6 /4. Now I can see that I have an average of 2000 bytes for each lob item:
SCOTT@andrkydb> select avg(dbms_lob.getlength(b)) from t1;
AVG(DBMS_LOB.GETLENGTH(B))
--------------------------
2000and that all together they take up:
SCOTT@andrkydb> select sum(dbms_lob.getlength(b)) from t1;
SUM(DBMS_LOB.GETLENGTH(B))
--------------------------
2000000But when I take a look at how much is the LOB segment actually taking, I get a result, which is being a total mystery to me:
SCOTT@andrkydb> select bytes from dba_segments where segment_name = 'T1_LOB';
BYTES
----------
5242880What am I missing? Why is LOB segment is being ~2 times bigger than it is required by the data?
I am on 10.2.0.3 EE, Solaris 5.10 sparc 64bit.
August 20, 2007 - 9:56 pm UTC
because there is block overhead and it is more than likely that you need 2 blocks for each 2000 byte lob (which SHOULD be stored inline probably)