Length of text to be filled will be between 0 and some hundreds of (UTF8) characters (rarely more than 4000 characters).

Due to the facts that, in the first phase of our project, we have a lot of insertion and slow response time when OUT of LINE storage, I will privilege IN LINE storage.

I will examine the possibility of OUT OF LINE in a later phase of the project.

What you answer does not tell about is why such a big difference between IN LINE and OUT OF LINE storage of clob ?

The reason might be that instead of inserting in one data block when IN LINE storage, three data blocks (data + index + lob blmocks) are inserted when OUT OF LINE storage.

But when inserting in a table with 2 indexes (so 1 data block + 2 index blocks) versus inserting in a table with no index, I do not expect such a big difference in time insertion !

Thank you for your answer.

I have this situation:
Unix , Oracle 8.1.7.4
Block size is 4k.
Chunk size is 4k
Enable storage in row for LOB.
Locally managed uniform tablespace both for table and LOB
Seperate tablespaces for LOB,table.
Tablespace1 extent size 4Mb
TablespaceLOB extentsize 128M
The table was initially allocated some space.(also initial was 
specified for the lob column storage clause).

Table1 has 35 columns out of which one is a blob  column(table1blob).

SQL>Analyze table1 compute statistics;

SQL>select segment_name "Table Name" ,tablespace_name "Table space" ,
sum(bytes)/1024 "Allocated Kb" ,  sum(bytes)/1024/1024 "Allocated Mb",
sum(blocks) "Allocated Blocks" from user_extents where 
segment_name='TABLE1' group by segment_name,tablespace_name ;

Table Name    Table space   Allocated Kb   Allocated Mb    Alloc Blocks
------------- ------------  ----------     ------------    ----------
TABLE1        TABLESPACE1   258048         252             64512


SQL>select segment_name from user_lobs where table_name='TABLE1';

SEGMENT_NAME
------------------------------
SYS_LOB0000007813C00015$$

SQL>select segment_name "Table Name" ,tablespace_name "Table space" 
,sum(bytes)/1024 "Allocated Kb" ,  sum(bytes)/1024/1024 "Allocated Mb"
,sum(blocks) "Allocated Blocks" from user_extents where segment_name=
'SYS_LOB0000007813C00015$$' group by segment_name,tablespace_name ;

Table Name                 Table space      AllocKb   AllocMb  AllocBlks
-------------------------- ------------------------------ ------------ 
SYS_LOB0000007813C00015$$  TABLESPACE_LOB   1572864   1536      393216



SQL>select Table_name "Table Name" ,tablespace_name "Tablespace name" ,
avg_space "Avg free space", blocks "Blocks used" , 
empty_blocks "Empty blocks" , 
((blocks*4) - (avg_space*blocks/1024))/1024 "Used space Mb", 
(empty_blocks*4/1024)-(((blocks*4)-(avg_space*blocks/1024))/1024) 
"Space left Mb",
num_rows "Rows" , avg_row_len "Row length",CHAIN_CNT from 
user_tables where table_name='TABLE1';


Table Name  Tablespace    Avgfreespace Blocksused Emptyblocks 
----------------------------------------------------------------
TABLE1      TABLESPACE1   1613         5974       58537       


Used space Mb   Space left Mb  Rows  Rowlength
 -------------- -----------    ------------  ----------
14.1462727      214.513884     6306  2253

CHAIN_CNT  FREELISTBLOCKS
---------  --------------
     5997  3


SQL>select count(*)  from TABLE1 where dbms_lob.getlength(TABLE1blob)
<2000;

  COUNT(*)
----------
      2479

SQL>select count(*)  from TABLE1 where dbms_lob.getlength(TABLE1blob)>2000 
and dbms_lob.getlength(TABLE1blob)<4000;

  COUNT(*)
----------
      3577

SQL>select count(*)  from TABLE1 where dbms_lob.getlength(TABLE1blob)>4000;

  COUNT(*)
----------
       242

SQL>select count(*)  from TABLE1 where dbms_lob.getlength(TABLE1blob)<4000;

  COUNT(*)
----------
      6064

SQL>select count(*)  from TABLE1 where dbms_lob.getlength(TABLE1blob)<=4000;

  COUNT(*)
----------
      6064
      
SQL>select count(*)  from TABLE1 where dbms_lob.getlength(TABLE1blob)>8000;

  COUNT(*)
----------
        47


SQL>select count(*)  from TABLE1 where dbms_lob.getlength(TABLE1blob)>16000;

  COUNT(*)
----------
         5

SQL>select count(*)  from TABLE1 where dbms_lob.getlength(TABLE1blob)>32000;

  COUNT(*)
----------
         0      
      

SQL>select avg(dbms_lob.getlength(TABLE1BLOB))  from TABLE1 ;

AVG(DBMS_LOB.GETLENGTH(TABLE1BLOB))
--------------------------------------
                            2367.24215
      

SQL>select max(dbms_lob.getlength(TABLE1BLOB))  from TABLE1 ;

MAX(DBMS_LOB.GETLENGTH(TABLE1BLOB))
--------------------------------------
                                 22780
                                 
SQL>select min(dbms_lob.getlength(TABLE1BLOB))  from TABLE1 ;

MIN(DBMS_LOB.GETLENGTH(TABLE1BLOB))
--------------------------------------
                                   743                                 


SQL>exec show_space('TABLE1');
Free Blocks.............................3
Total Blocks............................64512
Total Bytes.............................264241152
Unused Blocks...........................58537
Unused Bytes............................239767552
Last Used Ext FileId....................8
Last Used Ext BlockId...................13329
Last Used Block.........................855      


SQL>column segment_name new_val lob
SQL>select segment_name from user_lobs
  2  where table_name = 'TABLE1';

SEGMENT_NAME
------------------------------
SYS_LOB0000007813C00015$$

SQL>exec show_space('&lob',user,'LOB');
Free Blocks.............................2
Total Blocks............................393216
Total Bytes.............................1610612736
Unused Blocks...........................392663
Unused Bytes............................1608347648
Last Used Ext FileId....................18
Last Used Ext BlockId...................229393
Last Used Block.........................553

I have the following questions:

1. The table above has sample loading(of actual data). Actual number of 
rows will be in millions(max 20,000,000). Increases by 1,000,000 
monthly to the max specified. Most of the blob is less than 3k.
Why is there a chaint_cnt almost equal to the number of rows?
Is it getting displayed because of the ENABLE_IN_ROW ? Is it ok for it 
to be this way then? Enable in row was chosen because most of the 
data is less than 4k. I do not know how to get the space the
lob locater will hold. Out of the 1,000,000 rows per month abt 10% blobs 
will have size above 4k...to any max (inside limit).hence enable in row was
chosen.



2. I need to do a 'select distinct datecolumn' on the above table1 to get last5 days 
dates (exclude sat/sun) if soem conditions are satisfied .
      
  SQL>desc table2 (small table , abt 100 rows , wont increase much..max 300 )
Name                                      Null?    Type----------------------------------------- -------- ------------------
table2ref                                   NOT NULL RAW(16)
namecol                                      VARCHAR2(50)
    
SQL>desc table1 (huge table abt 20,000,000)
Name                                      Null?    Type
---------------------------------------- -------- ----------table1ref                                  NOT NULL RAW(16)
col1                                         VARCHAR2(58)
col2                                         VARCHAR2(60)
col3                                         VARCHAR2(80)
col5                                          VARCHAR2(80)TABLE1BLOB                                      BLOB
table2ref                              NOT NULL RAW(16)
DATEcol                                NOT NULL NUMBER(10)
     ....and so on 


       
1 Indexe on table2 - > on table2ref column
6 Indexes on table1-> on table2ref column , on col1 column, col2 column, col3 column, 
on datecol column,col5 columncol1,col2,col3,col5 are nullable.....(will index not be 
used though present because of these nullable columns?). I cannot make it not null
(a 3rd party db).

select
      DISTINCT to_char(to_date(t1.DATEcol,'j'),DD/MON/YYYY') 
   FROM
       table1 t1,
       table2 t2
   WHERE 
       t1.table2ref = t2.table2ref AND
       TO_DATE(t1.DATEcol,'j') <= (sysdate-1) AND
       TO_DATE(t1.DATEcol,'j') >= (sysdate-7) AND 
       UPPER(t2.namecol) LIKE  '%XXXX%' AND
         UPPER(t1.col1) = 'P' AND
         UPPER(t1.col5) IN ('T','O') AND
     (t1.col3= 'L' OR UPPER(t1.col2) IN ('R,I,U'));


Elapsed: 00:00:26.76

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=38)
   1    0   SORT (UNIQUE) (Cost=6 Card=1 Bytes=38)
   2    1     NESTED LOOPS (Cost=2 Card=1 Bytes=38)
   3    2       TABLE ACCESS (FULL) OF 'TABLE2' (Cost=1 Card=1 Byt
          es=22)

   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'TABLE1' (Cost=1 C
          ard=1 Bytes=16)

   5    4         INDEX (RANGE SCAN) OF 'TABLE1_TABLE2REF_IDX'
          (NON-UNIQUE)





Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
      12091  consistent gets
       3837  physical reads
          0  redo size
        132  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed


Later i pumped in more data ..upto 100,000 rows into table1 (with last 7 days dates  
and others too). 
Execution time is more than this and the plan is the same. I can have about 
1,000,000/22= abt 500,000
records per date.
How do i reduce response time? any other way to get the dates if this is not a right approach?



3. When made another table table3 , same as table1 , copied all the columns but the 
blob into it, and executed the same
   query above on the new table, it gets executed fast...less than a second. why? 
   Similar execution plan:.
   Avg rwo length of the new table is 435. thats why?
   
   Elapsed: 00:00:00.31
   
   Execution Plan
   ----------------------------------------------------------
      0      SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=1 Bytes=35)
      1    0   SORT (UNIQUE) (Cost=38 Card=1 Bytes=35)
      2    1     NESTED LOOPS (Cost=36 Card=1 Bytes=35)
      3    2       TABLE ACCESS (FULL) OF 'TABLE2' (Cost=1 Card=1 Byt
             es=21)
   
      4    2       TABLE ACCESS (BY INDEX ROWID) OF 'TABLE3' (Cost=35
             Card=1 Bytes=14)
   
      5    4         INDEX (RANGE SCAN) OF 'TABLE3_TABLE2REF_IDX'
             (NON-UNIQUE) (Cost=6 Card=1)
Statistics
---------------------------------------------------------
          0  recursive calls
          4  db block gets
        116  consistent gets
          0  physical reads
          0  redo size
        220  bytes sent via SQL*Net to client
        314  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
          0  rows processed

 

Hi Tom,
Thanks for the above info.
I created another table - table_new specifying disble in row for the LOB column.
The new table was created using the same storage and tablespace clauses as table1
except for disable in row for lob in the new table (as opposed to enable in row
for lob in table1).
table1 data is in tablespace1
table1 lob is in TABLESPACE_LOB
table_new data is in tablespace1
table1_new lob is in TABLESPACE_LOB

Then I did a
insert into table_new select * from table1;

Then i analyzed the new table and found the avg row length lesser . how? No chained rows because of that.
How can the avg row length decrease in the new table?
I checked the length of teh lobs in both the tables, they are same.

here are the details:

TABLE1 has 30 columns - one blob column.
The remaining 29 columns as follows : 10 varchar2(30) , 12 number (38) , 6 raw(8), 1 char(1)

select segment_name from user_lobs where table_name='TABLE_NEW';

SEGMENT_NAME
------------------------------
SYS_LOB0000008509C00015$$


select segment_name from user_lobs where table_name='TABLE';

SEGMENT_NAME
------------------------------
SYS_LOB0000007813C00015$$

select Table_name "Table Name" ,tablespace_name "Tablespace name" ,avg_space "Avg free space", blocks "Blocks used" , empty_blocks "Empty blocks" , ((blocks*4) - (avg_space*blocks/1024)) "Used space Kb", num_rows "Rows" , avg_row_len "Row length" ,chain_cnt from user_tables;

Table Name Tablespace name Avg free space Blocks used Empty blocks Used space Kb Rows Row length CHAIN_CNT
------------------------------ ------------------------------ -------------- ----------- ------------ ------------- ---------- ---------- ----------
TABLE1 TABLESPACE1 1613 5974 58537 14485.7832 6306 2253 5997




Table Name Tablespace name Avg free space Blocks used Empty blocks Used space Kb Rows Row length CHAIN_CNT
------------------------------ ------------------------------ -------------- ----------- ------------ ------------- ---------- ---------- ----------
TABLE_NEW TABLESPACE1 472 205 818 725.507813 6306 113 0


The above queries with distinct etc work much faster because of the lesser row length.

continuing from above,
if the row length decreased because of only the lob locators getting inserted in the new table (table_new)..then how do i make a ne wtable with disable in row instead of enable in row
using insert into table_new select * from table1?
thanks.

can this be a way to increase performance?
I mean, in my queries (as above using distinct etc etc)
i dont use the LOB column at all. I just need to update /retreive certain other fields .
So if i create a view on the base table (with lob) as
'select everything except blob column' ' and use that view in my queries, it will be faster?
Is this a good approach for retreiving /updating/inserting
non blob columns?

Thanks Tom,
One more point that I didnt understand:

1)If i have a table t ( x int, y date, z varchar2(20), a clob ).
Two cases:
A)I store lob out of line.(disbale in row)
B)I store lob inline.(enable in row)

Now i have indexes on x and y...so when i write a query to say ...

select distinct x,y,z from t where x= somethign and y=something.

I write it such that i dont do a full table scan of t but make it use indexes on x and y.

So a) Vs b) should not make any difference right? I am not selecting LOB column at all and am not doing a full table scan either.

But this is not the case with me. If i store lob out of line
performance is much much faster(for the reasons you have already explained ). But i didnt understand how it is making a difference if i am not doing a full t scan in both cases.
The queries run very very fast now!! with disable in row.

2)When i do a count (*) on a table, does all the rows (withe all columns) get read before counting?

3)when i do a 'select distinct x,y,z from t where..' or 'select x,y,z from t where...' does all columns get read(x,y,z and a) or only x,y,z ? (assuming no full table scan).

thanks.

I did a test with real sized data and found, as you said that, Storing clob data out of line is as efficient as in line, however when changing some of the storage parameters like (NOCAHCE etc) I see this: (run on 8.1.7.3)

set timing on
drop table t;
create table t (
A VARCHAR2 (30) NOT NULL,
B VARCHAR2 (30) NOT NULL,
C clob default empty_clob()
)
NOCACHE
LOGGING
lob(c) store as t_name_lo(enable storage in row);

begin
for i in 1..1000 loop
insert into t (a, b, c)
select 'a','b',rpad('*',32000,'*') from dual;
end loop;
end;
/
rollback
/
Elapsed: 00:00:42.05

--FIine an these are the other results:
noCACHE,noLOGGING:
Elapsed: 00:00:42.07
--
CACHE,LOGGING
Elapsed: 00:00:42.01
--
CACHE,NOLOGGING
Elapsed: 00:00:43.01

All results are about the same, that's OK.

But when I changed the CLOB for I LONG and rerun the insert (from sqlplus):

--col c is a LONG
Elapsed: 00:00:09.08

Over 4 times Faster????

Please explain? I realise LONGs are deprecated so I want to use CLOBS, but if I have , as I do , a scenario where I just want to write once no read of the long/LOB col. Then is LONG better?

Thanks in advance

Hi Tom,

You said that the LONGs are in the buffer cache
and using nocache means you have to wait for a direct write to disk. which is fine

However I've tried the insert with all combinations:

NOCACHE,NOLOGGING: Elapsed: 00:00:42.07
CACHE,LOGGING: Elapsed: 00:00:42.01
CACHE,NOLOGGING: Elapsed: 00:00:43.01

But when I changed the CLOB for a LONG and rerun the insert
LONG: Elapsed: 00:00:09.08

LONG is still faster even when the CLOB is ion the buffer cache.

Does this mean that despite the fact that LONGS are deprecated we should still use them for write once (no update) type operations?

Thanks again

well, your test is a tad "flawed".  if you check it out -- you'll discover you inserted 4000 bytes -- rpad returned 4000 bytes when used in SQL.  But in any event, I cannot reproduce.  This was run in noarchivelog mode (so nocache is nologging and cache is logging).  I cannot reproduce findings similar to yours at all.

First just with "wall clock" timings:

ops$tkyte@ORA920> drop table t_nocache;

Table dropped.

Elapsed: 00:00:00.34
ops$tkyte@ORA920> create table t_nocache (a number, b clob)
  2  lob (b) store as (enable storage in row nocache )
  3  /

Table created.

Elapsed: 00:00:00.15
ops$tkyte@ORA920>
ops$tkyte@ORA920> drop table t_cache;

Table dropped.

Elapsed: 00:00:00.26
ops$tkyte@ORA920> create table t_cache (a number, b clob)
  2  lob (b) store as (enable storage in row cache )
  3  /

Table created.

Elapsed: 00:00:00.10
ops$tkyte@ORA920>
ops$tkyte@ORA920> drop table t_long;

Table dropped.

Elapsed: 00:00:01.99
ops$tkyte@ORA920> create table t_long(a number, b long);

Table created.

Elapsed: 00:00:00.09
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> set timing on
ops$tkyte@ORA920> declare
  2          l_data long := rpad('*',32000,'*');
  3  begin
  4          for i in 1 .. 1000
  5          loop
  6                  insert into t_nocache ( a, b ) values ( i, l_data );
  7          end loop;
  8          commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.27
ops$tkyte@ORA920> declare
  2          l_data long := rpad('*',32000,'*');
  3  begin
  4          for i in 1 .. 1000
  5          loop
  6                  insert into t_cache ( a, b ) values ( i, l_data );
  7          end loop;
  8          commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.57
ops$tkyte@ORA920> declare
  2          l_data long := rpad('*',32000,'*');
  3  begin
  4          for i in 1 .. 1000
  5          loop
  6                  insert into t_long ( a, b ) values ( i, l_data );
  7          end loop;
  8          commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.70
ops$tkyte@ORA920>

<b>
14, 3, 14 seconds....

Now using runstats and comparing the cached lob with the long</b>


ps$tkyte@ORA920> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.11
ops$tkyte@ORA920> declare
  2          l_data long := rpad('*',32000,'*');
  3  begin
  4          for i in 1 .. 1000
  5          loop
  6                  insert into t_cache ( a, b ) values ( i, l_data );
  7          end loop;
  8          commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.53
ops$tkyte@ORA920> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.13
ops$tkyte@ORA920> declare
  2          l_data long := rpad('*',32000,'*');
  3  begin
  4          for i in 1 .. 1000
  5          loop
  6                  insert into t_long ( a, b ) values ( i, l_data );
  7          end loop;
  8          commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:15.40
ops$tkyte@ORA920> exec runstats_pkg.rs_stop(1000);
Run1 ran in 259 hsecs
Run2 ran in 1541 hsecs
run 1 ran in 16.81% of the time

Name                                  Run1        Run2        Diff
STAT...free buffer requested         4,158       5,224       1,066
STAT...Elapsed Time                    271       1,545       1,274
STAT...consistent gets               2,460       6,617       4,157
LATCH.cache buffer handles               4       6,930       6,926
STAT...dirty buffers inspected           3      10,297      10,294
STAT...free buffer inspected             3      10,506      10,503
LATCH.redo allocation               10,387      21,551      11,164
STAT...redo entries                 10,324      21,506      11,182
STAT...db block changes             16,245      28,364      12,119
STAT...db block gets                22,816      37,066      14,250
LATCH.checkpoint queue latch            16      17,988      17,972
STAT...session logical reads        25,276      43,683      18,407
LATCH.cache buffers lru chain        4,159      22,959      18,800
LATCH.cache buffers chains          77,235     162,816      85,581
STAT...redo size                33,552,760  34,459,324     906,564

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
112,677     253,011     140,334     44.53%

PL/SQL procedure successfully completed.


<b>it was faster, it was infinitely more efficient latch wise</b>

 

Tom,

As you mentioned that Cache Lobs are much faster as compared to NoCache Lobs, So in this regard, I have two questions.

1) Why Default setting for Lob is NoCache/Logging?
2) Is there any overhead of changing default to Cache/Logging.

Thanks in advance.

Thanks Tom for your helps and guidance. You are really great. I changed the No_cache clob to Cache Clob and performance has been improved drastically. As you said, we should also consider to use Cache Clob if the size of the clob is on smaller side, in my case, It was 20K.

Hi Tom,
As you said,if the blob size is more than 4000 bytes it will use the out line store?
But suppose I am using the block size of say 16KB, the BLOB size is say 13KB and I am defining to use inline store.
What will Oracle use Inline store or out line store?
Request your suggestions and recommendations.
Thanks

Hi,

Our block size is 8k.
We have many tables which are uding CLOB

+++++++++++
select Table_name "Table Name",
avg_row_len "Row length" ,chain_cnt from user_tables where table_name=TXN_ERPXX002'; 2

Table Name Row length CHAIN_CNT
------------------------------ ---------- ----------
TXN_ERPXX002 1126 44

++++++++++++


Show Row lenght as 1126 and chain cnt as 44.
In this situation will it be better to go for out of line clob?

Or

Why chain cnt is showing value if avg row length

Hi Tom,

thanks to your two hints about automatic inline storage of text <4000 Bytes and caching, I was able to take new, successful measures in tuning a query.

Now for the bad news: I used autotrace in SQL*Plus to compare run times and statistics of different approaches, but there seems to be an issue with CLOBs. When I run the query without tracing, the output of ~15,000 rows (out of 185,000) finished in about 13s. With autotrace traceonly, I get the statistics after 3 minutes! This doesn't happen when I omit the LOB column in my statement. I this a known issue? Should I build a test case for you?

Best regards,
--==/ Uwe \==--

I'm trying to measure the effect of a full table scan on a table that has a LOB column storing inline vs out-of-line. Why isn't there much difference in terms of logical IOs and cost?  I was expecting both these numbers to be much higher for t_in_row which has LOBs stored inline. Is my test case flawed?


SQL> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle9i Release 9.2.0.7.0 - Production
PL/SQL Release 9.2.0.7.0 - Production
CORE    9.2.0.7.0    Production
TNS for 32-bit Windows: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

SQL> 
SQL> DROP TABLE t_in_row;

Table dropped.

SQL> DROP TABLE t_out_row;

Table dropped.

SQL> 
SQL> 
SQL> CREATE TABLE t_in_row (
  2  col1    VARCHAR2(30),
  3  theCLOB CLOB
  4  )
  5  LOB (theCLOB) STORE AS (ENABLE STORAGE IN ROW)
  6  /

Table created.

SQL> 
SQL> 
SQL> CREATE TABLE t_out_row (
  2  col1    VARCHAR2(30),
  3  theCLOB CLOB
  4  )
  5  LOB (theCLOB) STORE AS (DISABLE STORAGE IN ROW)
  6  /

Table created.

SQL> 
SQL> 
SQL> INSERT INTO t_in_row
  2  SELECT RPAD('x', 30, 'x'), RPAD('y', 2000, 'y')
  3  FROM   all_objects;

24333 rows created.

SQL> 
SQL> INSERT INTO t_out_row
  2  SELECT RPAD('x', 30, 'x'), RPAD('y', 2000, 'y')
  3  FROM   all_objects;

24333 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> 
SQL> ANALYZE TABLE t_in_row COMPUTE STATISTICS;

Table analyzed.

SQL> ANALYZE TABLE t_out_row COMPUTE STATISTICS;

Table analyzed.

SQL> 
SQL> 
SQL> col segment_name for a15
SQL> 
SQL> SELECT segment_name, bytes, blocks, extents
  2  FROM   user_segments
  3  WHERE  segment_name IN ('T_IN_ROW', 'T_OUT_ROW');

SEGMENT_NAME         BYTES     BLOCKS    EXTENTS
--------------- ---------- ---------- ----------
T_IN_ROW           3145728        384         18
T_OUT_ROW          2097152        256         17

SQL> set autotrace traceonly
SQL> 
SQL> COLUMN plan_plus_exp FORMAT a100
SQL> 
SQL> SELECT col1
  2  FROM   t_in_row;

24333 rows selected.


Execution Plan
----------------------------------------------------------
   0
SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=24333 Bytes=729990)
   1    0
  TABLE ACCESS (FULL) OF 'T_IN_ROW' (Cost=31 Card=24333 Bytes=729990)


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
       1919  consistent gets
          0  physical reads
          0  redo size
     318379  bytes sent via SQL*Net to client
      18345  bytes received via SQL*Net from client
       1624  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      24333  rows processed

SQL> SELECT col1
  2  FROM   t_out_row;

24333 rows selected.


Execution Plan
----------------------------------------------------------
   0
SELECT STATEMENT Optimizer=CHOOSE (Cost=25 Card=24333 Bytes=729990)
   1    0
  TABLE ACCESS (FULL) OF 'T_OUT_ROW' (Cost=25 Card=24333 Bytes=729990)


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
       1863  consistent gets
          0  physical reads
          0  redo size
     318379  bytes sent via SQL*Net to client
      18345  bytes received via SQL*Net from client
       1624  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      24333  rows processed

SQL> spool off

 

SQL> SELECT segment_name, bytes, blocks, extents
  2  FROM   user_segments
  3  WHERE  segment_name IN ('T_IN_ROW', 'T_OUT_ROW');

SEGMENT_NAME         BYTES     BLOCKS    EXTENTS
--------------- ---------- ---------- ----------
T_IN_ROW           3145728        384         18
T_OUT_ROW          2097152        256         17

that would indicate that the tables are "basically the same size".


The LIO's are an artifact of your arraysize - so this, run your autotrace selects with arraysizes of

2
10
50
100
500
1000

and see what you see.  But basically, these tables are about the same size, not sure why would expect super large differences.

A lob locator takes room - not 2000 bytes, but room.  So, the tables are pretty much the same. 

I was expecting a large difference because I was expecting a LOB locator to take much less room comparing to 2K of data stored inline for t_in_row. I thought a full table scan on t_out_row would definitely be much faster than a full table scan on t_in_row for the above reason.

I just noticed something

ops$tkyte@ORA10GR2> select 2000*24333 - 3145728 from dual;

2000*24333-3145728
------------------
          45520272


If your row is 2000 bytes
AND you have 24,333 of them
THEN you have some 45,520,272 of data (at least, you have more)
and that is less than 3,145,728 (of allocated space)

So, whats up with that?  how did that happen?

I noticed that because on my 8k blocksize database - well, it was different. 

In the thread above you said for the Question
3)when i do a 'select distinct x,y,z from t where..' or 'select x,y,z from t where...' does all columns get read(x,y,z and a) or only x,y,z ? (assuming no full table scan)

and your answer was
3) just xyz, unless A is in front of xyz that is (then we have to read over A of course to find xyz.

Please correct me if I'm wrong here.
I thought when you have distinct in your select or any sql, it is applied on the entire row.
so if this is true then no matter what position of column A is, it would be read even if your query is 'select distinct x,y,z from t where..'

Appreciate your help.

I reran the test again. It's strange that the avg_row_len (user_tables) for t_in_row is 75!

Is there a query you want me to run to get more info about this situation?


SQL> 
SQL> DROP TABLE t_in_row;

Table dropped.

SQL> DROP TABLE t_out_row;

Table dropped.

SQL> 
SQL> CREATE TABLE t_in_row (
  2     col1    VARCHAR2(30),
  3     theCLOB CLOB
  4  )
  5  LOB (theCLOB) STORE AS (ENABLE STORAGE IN ROW)
  6  /

Table created.

SQL> 
SQL> 
SQL> CREATE TABLE t_out_row (
  2     col1    VARCHAR2(30),
  3     theCLOB CLOB
  4  )
  5  LOB (theCLOB) STORE AS (DISABLE STORAGE IN ROW)
  6  /

Table created.

SQL> 
SQL> 
SQL> INSERT INTO t_in_row
  2  SELECT RPAD('x', 30, 'x'), RPAD('y', 2000, 'y')
  3  FROM   all_objects;

27675 rows created.

SQL> 
SQL> INSERT INTO t_out_row
  2  SELECT RPAD('x', 30, 'x'), RPAD('y', 2000, 'y')
  3  FROM   all_objects;

27675 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> 
SQL> ANALYZE TABLE t_in_row COMPUTE STATISTICS;

Table analyzed.

SQL> ANALYZE TABLE t_out_row COMPUTE STATISTICS;

Table analyzed.

SQL> 
SQL> 
SQL> SELECT COUNT(*)
  2  FROM   t_in_row;

  COUNT(*)
----------
     27675

SQL> 
SQL> 
SQL> SELECT COUNT(*)
  2  FROM   t_out_row;

  COUNT(*)
----------
     27675

SQL> 
SQL> 
SQL> col segment_name for a15
SQL> 
SQL> SELECT segment_name, bytes, blocks, extents
  2  FROM   user_segments
  3  WHERE  segment_name IN ('T_IN_ROW', 'T_OUT_ROW');

SEGMENT_NAME         BYTES     BLOCKS    EXTENTS
--------------- ---------- ---------- ----------
T_IN_ROW           3145728        384         18
T_OUT_ROW          2097152        256         17

SQL> 
SQL> 
SQL> SELECT table_name, num_rows, blocks, avg_space, avg_row_len
  2  FROM   user_tables
  3  WHERE  table_name IN ('T_IN_ROW', 'T_OUT_ROW');

TABLE_NAME                       NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN
------------------------------ ---------- ---------- ---------- -----------
T_IN_ROW                            27675        306       1106          75
T_OUT_ROW                           27675        244       1605          55

SQL> 
SQL> spool off

 

We only use one block size: 8K.

SQL> show parameter db_block

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
db_block_buffers                     integer     0
db_block_checking                    boolean     FALSE
db_block_checksum                    boolean     TRUE
db_block_size                        integer     8192

 

SQL> select * from v$version;

BANNER
------------------------------------------------------
Oracle9i Release 9.2.0.7.0 - Production
PL/SQL Release 9.2.0.7.0 - Production
CORE    9.2.0.7.0       Production
TNS for 32-bit Windows: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

SQL> select dbms_metadata.get_ddl('TABLE','T_OUT_ROW') txt from dual;

TXT
-----------------------------------------------------------------------

  CREATE TABLE "PKLIN"."T_OUT_ROW"
   (    "COL1" VARCHAR2(30 CHAR),
        "THECLOB" CLOB
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 LOB ("THECLOB") STORE AS (
  TABLESPACE "USERS" DISABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

SQL> select dbms_metadata.get_ddl('TABLE','T_IN_ROW') txt from dual;

TXT
-----------------------------------------------------------------------

  CREATE TABLE "PKLIN"."T_IN_ROW"
   (    "COL1" VARCHAR2(30 CHAR),
        "THECLOB" CLOB
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 LOB ("THECLOB") STORE AS (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
 

Both tables have the same lobsegment size, but very different lobindex size. Also inserting into t_in_row was much faster than inserting into t_out_row--almost twice the speed.

col value for a20

SELECT *
FROM nls_database_parameters
WHERE parameter = 'NLS_CHARACTERSET';

PARAMETER VALUE
------------------------------ --------
NLS_CHARACTERSET AL32UTF8


col table_name for a12
col segment_name for a25
col index_name for a24
col segment_type for a10

SELECT b.table_name, a.segment_name, a.segment_type, a.bytes, a.blocks, a.extents
FROM user_segments a, user_lobs b
WHERE b.table_name IN ('T_IN_ROW', 'T_OUT_ROW') AND
a.segment_name = b.segment_name
UNION ALL
SELECT b.table_name, a.segment_name, a.segment_type, a.bytes, a.blocks, a.extents
FROM user_segments a, user_lobs b
WHERE b.table_name IN ('T_IN_ROW', 'T_OUT_ROW') AND
a.segment_name = b.index_name;


TABLE_NAME SEGMENT_NAME SEGMENT_TY BYTES BLOCKS EXTENTS
------------ ------------------------- ---------- ---------- ---------- ----------
T_IN_ROW SYS_LOB0000028215C00002$$ LOBSEGMENT 234881024 28672 99
T_OUT_ROW SYS_LOB0000028218C00002$$ LOBSEGMENT 234881024 28672 99
T_IN_ROW SYS_IL0000028215C00002$$ LOBINDEX 65536 8 1
T_OUT_ROW SYS_IL0000028218C00002$$ LOBINDEX 2097152 256 17


TRUNCATE TABLE t_in_row;
TRUNCATE TABLE t_out_row;


set timing on

INSERT INTO t_in_row
SELECT RPAD('x', 30, 'x'), RPAD('y', 2000, 'y')
FROM all_objects;

27699 rows created.

Elapsed: 00:00:25.02

INSERT INTO t_out_row
SELECT RPAD('x', 30, 'x'), RPAD('y', 2000, 'y')
FROM all_objects;

27699 rows created.

Elapsed: 00:00:42.00

commit;

Hi Tom,

I did a test as you mentioned in one of your examples here and indeed with the "cached lobs" it is much faster.

I want to benefit from this "feature" for imports.

Can I "alter" an existing table something like:

"alter table t1 lob (b) store as (enable storage in row cache )" ?

Many thanks in advance.

ops$tkyte@ORA10GR2> create table t ( y clob );
 
Table created.
 
ops$tkyte@ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;
 
DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------
 
  CREATE TABLE "OPS$TKYTE"."T"
   (    "Y" CLOB
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 LOB ("Y") STORE AS (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
 
 
 
ops$tkyte@ORA10GR2> alter table t modify lob(y) (cache);
 
Table altered.
 
ops$tkyte@ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;
 
DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------
 
  CREATE TABLE "OPS$TKYTE"."T"
   (    "Y" CLOB
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 LOB ("Y") STORE AS (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  CACHE
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
 
 

As far as I know, analyze is the only way in pre-10g (actually 10.2) db's to get the correct rowsize for LOBS. See bug 1954150 for details. dbms_stats doesn't calculate the correct row size value for in or out of line LOBS.

Hi Tom,

Thanks a lot for your prompt reply!

Would you please explain me what actualy does "cache" ?

Is it as I pin the tables into buffer_keep?

Many thanks in advance!

Hi Tom,

So, if I want to speed up my import process, will be better to have all the LOBs INLINE?

How can I do this?

Thanks!

Hi Tom

in your followup above on June 6, 2003 - 11am US/Eastern:

you have shown that insert into lob (cache) performs better than the long .

i did try with the long raw - but it appears that it is faster than the blob insert.

i also read into the application developer guide:here
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96591/adl06faq.htm#127729
-- it says that long raw insert perfoms better than blob.

Why is Inserting into BLOBs Slower than into LONG Raws?
Question
Why is writing into BLOBs slower than inserting into LONG RAWs?

Answer
It is true that inserting data in BLOBs with JDBC Thin is slower as it still uses the DBMS_LOB package. With JDBC OCI the inserts are faster as native LOB APIs are used.

what is your thought on this.

i have a oracle 10gr2 database and there are many tables having long raw data type. the insert into these tables shows as top resource consumer (i/o) .so was trying the option of converting the long raws into blob , but during the test cases , i found long raw insert is better than blob insert.

just took your examples and tested.

long raw inserts :
-----------------

PTUSER@DEVCPQ3-308>declare
2 l_data long raw ;
3 begin
4 select rawtohex(rpad('*',32000,'*')) into l_data from dual ;
5 for i in 1 .. 1000
6 loop
7 insert into t_long ( a, b ) values ( i, l_data );
8 end loop;
9 commit;
10 end;
11 /
Elapsed: 00:00:00.08
PTUSER@DEVCPQ3-308>drop table t_long;
Elapsed: 00:00:00.04
PTUSER@DEVCPQ3-308>create table t_long(a number, b long raw) tablespace SLTC_QUOTEDAT01;
Elapsed: 00:00:01.03
PTUSER@DEVCPQ3-308>declare
2 l_data long raw := utl_raw.cast_to_raw( rpad( '0', 32123, '0' ) );
3 begin
4 --select rawtohex(rpad('*',32000,'*')) into l_data from dual ;
5 for i in 1 .. 1000
6 loop
7 insert into t_long ( a, b ) values ( i, l_data );
8 end loop;
9 commit;
10 end;
11 /
Elapsed: 00:00:02.04

--
--blob inserts.

PTUSER@DEVCPQ3-308>create table t_cache (a number, b clob) tablespace SLTC_QUOTEDAT01
2 lob (b) store as (enable storage in row cache )
3 /
Elapsed: 00:00:00.04
PTUSER@DEVCPQ3-308>declare
2 l_data long := rpad('*',32000,'*');
3 begin
4 for i in 1 .. 1000
5 loop
6 insert into t_cache ( a, b ) values ( i, l_data );
7 end loop;
8 commit;
9 end;
10 /
Elapsed: 00:00:06.01
--

ops$tkyte%ORA10GR2> create table t_long(a number, b long raw) ;

Table created.

ops$tkyte%ORA10GR2> create table t_cache (a number, b blob)
  2    lob (b) store as (enable storage in row cache )
  3  /

Table created.




ops$tkyte%ORA10GR2> declare
  2              l_data long raw := utl_raw.cast_to_raw( rpad( '0', 32123, '0' ) );
  3  begin
  4          for i in 1 .. 1000
  5          loop
  6                  insert into t_long ( a, b ) values ( i, l_data );
  7          end loop;
  8          commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> declare
  2          l_data long raw := utl_raw.cast_to_raw( rpad( '0', 32123, '0' ) );
  3  begin
  4          for i in 1 .. 1000
  5          loop
  6                  insert into t_cache ( a, b ) values ( i, l_data );
  7          end loop;
  8          commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.


INSERT INTO T_LONG ( A, B ) VALUES ( :B2 , :B1 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   1000      3.47       4.26          4       5130      29440        1000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1001      3.47       4.26          4       5130      29440        1000

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         4        0.02          0.02
  rdbms ipc reply                                 4        0.04          0.05
  log file switch completion                      3        0.04          0.07
********************************************************************************
INSERT INTO T_CACHE ( A, B ) VALUES ( :B2 , :B1 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   1000      3.33       4.22          0       3462      22119        1000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1001      3.33       4.22          0       3462      22119        1000

Thank you Tom.


I tried again using tkprof - here is my results

INSERT INTO T_LONG ( A, B )
VALUES
( :B2 , :B1 )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000 0.62 1.95 0 338 23748 1000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1001 0.62 1.95 0 338 23748 1000


INSERT INTO T_CACHE ( A, B )
VALUES
( :B2 , :B1 )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000 0.47 1.63 0 7444 21914 1000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1001 0.47 1.63 0 7444 21914 1000

why query or cr so high in case of insert into t_cache.
(comarision to t_long).

is there anything i'm doing wrong .

Hi Tom,

I have an update statement that is run frequently through our application. One of the columns is a clob. The average size of the clob is 500K. Our sga is 16 GB ona 12 cpu machine. DB is 10gR2.

In this case setting the clob to cache is probably not a good idea as it will not scale well and cause other parts of the application sql to fall out of the buffer cache.

The ADDM reports that HW Enque contention is the biggest wait for this update.

UPDATE ORDERUPDATE SET EXTENDEDATTRS = :B7 , CURRENTSESSIONID = :B6 , LASTUPDATEDATE = :B5 , CURRENTSTATE = :B4 , UPDATESTATUSTERMID = :B3 , UPDATEDBY_FK = :B2 WHERE OBJECTID = :B1

Enqueue Type (Request Reason): HW-Segment High Water Mark
Requests: 11,608
Succ Gets: 11616
Failed Gets: 0
Waits: 6622
Wt Time (s): 8353
Av Wt Time(ms): 1261.45

We are using LMTs. Should we pre-allocate extents even though we are using LMTs ?

Is there any way to get the HW enq waits down ?

Any help is appreciated.

Thanks

Hi Tom,

Thank you for your quick response.

How do we allocate extents to a LOB segment ?
alter table allocate... will allocate to the main table segment.

In this case each session works with one row in the table. The row is not read or modified by another session until the first session is done with it.

Thanks
Sam

Hi Tom,

drop table orderupdate;

CREATE TABLE ORDERUPDATE
(
  OBJECTID              NUMBER(19)              NOT NULL,
  EXTENDEDATTRS         VARCHAR2(4000 BYTE),
  CURRENTSESSIONID      VARCHAR2(255 BYTE),
  STARTDATE             TIMESTAMP(3),
  LASTUPDATEDATE        TIMESTAMP(9),
  UPDATESTATUSTERMID    VARCHAR2(255 BYTE),
  CURRENTSTATE          CLOB,
  USERID                VARCHAR2(255 BYTE),
  USELOCKUNTILTIME      TIMESTAMP(9),
  MACHINEID             VARCHAR2(255 BYTE),
  MACHINELOCKUNTILTIME  TIMESTAMP(9),
  WORKORDER_FK          NUMBER(19),
  UPDATEDBY_FK          NUMBER(19)
)
TABLESPACE SPI2_VW_H
LOGGING 
NOCOMPRESS 
LOB (CURRENTSTATE) STORE AS 
      ( TABLESPACE  W_CLOB 
        DISABLE      STORAGE IN ROW
        CHUNK       8192
        PCTVERSION  0
        NOCACHE
      )
NOCACHE
PARALLEL
MONITORING;

ALTER TABLE ORDERUPDATE ADD (
  PRIMARY KEY
 (OBJECTID)
    USING INDEX 
    TABLESPACE W_INDX);

I ran the update in question through the application. The update was run 100 times with an average execution time of 3.5 secs. Most of the 100 executions ran for a single record in the table.

select segment_name, segment_type, bytes, blocks, extents
from dba_segments
where owner = 'XXX'
AND tablespace_name = 'W_CLOB';

segment_type  bytes   blocks   extents
------------ ------  ------  -------
LOBINDEX 65536  8  1
LOBINDEX 65536  8  1
LOBINDEX 65536  8  1
LOBINDEX 65536  8  1
LOBINDEX 65536  8  1
LOBINDEX 65536  8  1
LOBSEGMENT 2097152  256  17
LOBSEGMENT 65536  8  1
LOBSEGMENT 1048576  128  16
LOBSEGMENT 65536  8  1
LOBSEGMENT 524288  64  8
LOBSEGMENT 65536  8  1

From AWR for same time period:

Event: enq: HW - contention  
Waits: 9  
Time(s): 10  
Avg Wait(ms): 1,077  
% Total Call Time: 2.3

This tells me that extent allocation is an issue here. pctversion is set to 0 as each row is accessed by one session only. In reality this statement is run thousands of times an hour by the application.

Thanks

Hi Tom,

Pleas take a look at the above 2 posts and let me know how to allocate extents to a clob column as we are still seeing enq HW waits in production.

I have searched a lot of documentation, but was unable to find the syntax for it.

Thanks for you time.

Thanks

Tom:

Thank you for your prompt reply.

Here is the ddl for the table and tablespaces.

CREATE TABLE "VWDATA"."ORDERUPDATE" 
( 
"OBJECTID" NUMBER(19), 
"EXTENDEDATTRS" VARCHAR2(4000), 
"INITIALSTATE" CLOB, "USERID" VARCHAR2(255), 
"WORKORDER_FK" NUMBER(19), 
"UPDATEDBY_FK" NUMBER(19), 
"CURRENTSTATE" CLOB, 
CONSTRAINT "ORDERUPDATEUPDATEDBY1_FKC" 
FOREIGN KEY ("UPDATEDBY_FK") REFERENCES "VWDATA"."PARTY" ("OBJECTID") VALIDATE , 
CONSTRAINT "ORDERUPDATEWORKORDER1_FKC" 
FOREIGN KEY ("WORKORDER_FK") REFERENCES "VWDATA"."WORKORDER" ("OBJECTID") VALIDATE , 
CONSTRAINT "ORDERUPDATE_WORKORD1_UNK" UNIQUE ("WORKORDER_FK") VALIDATE , 
PRIMARY KEY ("OBJECTID") VALIDATE 
) 
TABLESPACE "SPI2_VW_H" 
PCTFREE 10 INITRANS 64 MAXTRANS 255 
STORAGE ( INITIAL 64 k BUFFER_POOL DEFAULT) LOGGING 
LOB ("INITIALSTATE") STORE AS 
( TABLESPACE "SPI2_VW_CLOB" DISABLE STORAGE IN ROW CHUNK 8192 
STORAGE ( INITIAL 8388608 k BUFFER_POOL DEFAULT)
PCTVERSION 5 NOCACHE LOGGING ) 
LOB ("CURRENTSTATE") STORE AS 
( TABLESPACE "SPI2_VW_CLOB" DISABLE STORAGE IN ROW CHUNK 8192 
STORAGE ( INITIAL 10485760 k BUFFER_POOL DEFAULT)
PCTVERSION 5 NOCACHE LOGGING 
);

CREATE SMALLFILE TABLESPACE "SPI2_VW_CLOB" 
DATAFILE '/path/data1.dbf' 
SIZE 20000M REUSE 
LOGGING 
EXTENT MANAGEMENT LOCAL 
SEGMENT SPACE MANAGEMENT AUTO;

CREATE SMALLFILE TABLESPACE "SPI2_VW_H" 
DATAFILE '/path/data2.dbf' SIZE 30720M REUSE 
LOGGING 
EXTENT MANAGEMENT LOCAL 
SEGMENT SPACE MANAGEMENT AUTO;

I dropped and re-created the table allocating bigger sizes to the clobs. The segments now show:

NAME   TYPE       BYTES       BLOCKS     EXTENTS
------------------------------------------------
S$$    LOBINDEX   436207616   53248      52
S$$    LOBINDEX   536870912   65536      64
S$$    LOBSEGMENT 8589934592  1048576    128
S$$    LOBSEGMENT 10770972672 1314816    161

I still see enq: HW Contention

Please let me know what I can do to remedy this situation.

Thanks for your time.

Hi Tom:

Thank you for your quick response. We had a tar open on metalink for this issue.

The analyst said that this is related to Bug 6376915. But you are right (as usual) this would also have been fixed by switching ASSM to Manual and increasing freelists.

After applying the patch, the HW enq contention was still present, but at a much lower level.

Since then we made application changes and switched the Clob to a blob by compressing the clob data in the application. We have not seen the waits since, but the waits mught popup again as load increases.

Thank You for your help.

Dear Tom,

good day to you as always. We are working on design of an application and if you can please give your inputs on the below.

Our application will be receiving message from a third party application on MQ, component of our application on will be calling Stored Procedure in DB to insert the messages in table. The message will be stored in CLOB data type column. There will be multiple sessions formed by the server using connection pooling and each one calling the Stored procedure and passing messages.

We will be storing some other information like message id , date and status ( will be updated after processing from another procedure). I am thinking of using disable in row storage of CLOB as a start, will you please recommend some storage settings for this particular table we should take care of from start so that there's less/no contention for the blocks of this table. We will be using either 10g/11g.

We will be doing benchmarking in next few days but if you can suggest some settings to be taken care of as base or any other pointers it will be really helpful.

Thanks for your help and time on this.

Regards,
VS.

Hello Tom,
We have a table with 3 CLOB columns in which 2 are stored as IN ROW and one as OUT ROW in Oracle 9i. Iam trying to understand why is the size of the table so huge.

CREATE TABLE EMAIL_DATA
(
<<other columns>>.......
SUBJECT1 CLOB,
SUBJECT2 CLOB,
SUBJECT3 CLOB
TABLESPACE TS_LOG
PCTUSED 40
PCTFREE 20
INITRANS 20
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL KEEP
)
LOGGING
NOCOMPRESS
LOB (SUBJECT1) STORE AS
( TABLESPACE TS_LOG
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 25
NOCACHE
STORAGE (
INITIAL 40K
NEXT 50M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
)
LOB (SUBJECT2) STORE AS
( TABLESPACE TS_LOG
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
NOCACHE
STORAGE (
INITIAL 40K
NEXT 50M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
)
LOB (SUBJECT3) STORE AS
( TABLESPACE TS_LOG
DISABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
NOCACHE
STORAGE (
INITIAL 40K
NEXT 50M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL
NOMONITORING
/

select sum(bytes)/1024/1024/1024
from user_segments
where segment_name ='EMAIL_DATA'

15.5

select sum(bytes)/1024/1024/1024
from user_segments
where segment_name in (
select segment_name from user_lobs
where table_name ='EMAIL_DATA')

17.6


select segment_name, sum(bytes)/1024/1024/1024
from user_segments
where segment_name in (select segment_name from user_lobs where table_name ='EMAIL_DATA')
group by segment_name

SYS_LOB0000431291C00026$$ 11.96
SYS_LOB0000431291C00176$$ 5.12
SYS_LOB0000431291C00208$$ 0.46


select
sum(dbms_lob.getlength(SUBJECT1))/1024/1024/1024
from EMAIL_DATA
where dbms_lob.getlength(SUBJECT1) > 4000

6.5

select
sum(dbms_lob.getlength(SUBJECT2))/1024/1024/1024
from EMAIL_DATA
where dbms_lob.getlength(SUBJECT2) > 4000

3.7

The CLOB column which has been stored as OUT ROW is occupying only 500MB as only 48 rows have data out of 1575211 rows.

The space occupied by non-CLOB columns is less than 1GB only (select sum(vsize(col1)) + sum(vsize(col2) .... from table).

Following are my question:

1. Can we say the space occupied by 2 CLOB columns (for column length less than 4000 characters are stored with the table data) is around 14.5GB (15.5GB - 1GB)?

2. And where column length is more than 4000 characters are stored in LOB segment (which are shown in USER_LOBS) occupies 17.6GB of space. Is my understanding correct?

3. Does this mean sum(dbms_lob.getlength(SUBJECT1))/1024/1024/1024 which is 6.5GB in fact is occupying 11.96GB of space and for sum(dbms_lob.getlength(SUBJECT2))/1024/1024/1024
which is 3.7GB is occupying 5.12GB?


Thanks

Dear Tom,
first of all a Very Happy and Prosperous New Year to you and your family.

I am not sure if this is the best thread for my question and I have multiple questions related to same problem so hope it's fine.

We have a table which has two xmltype columns both are with storage in row, out of these two one is updated as the processing of the records goes on and the other is not changed, the amount of data in both the columns for a row will be varying length. Below are the statistics of the table

Blocks 72991
Rows 80728
empty blocks 462
avg space 3259
chain cnt 28326
avg row len 412

As it can be seen there are chained/migrated rows. My questions are as follows.

1. If the due to limitation of size (4000 characters) the clob is automatically being stored out of row ( my guess as the avg row len is very less) why do we have chained/migrated rows, also am I right on my guess.

2. performing alter table move doesn't resolve the row chaining/migration, I think I will have to move the rows to another table, delete the rows and re-insert, have you faced a situation like this?

3. this is little out of the main topic, but have you faced situations where in due to chained rows the index is avoided for table. I understand that this is quite generic question but if you have faced any such situation can you please share how to investigate if due to chaining the index is being avoided it will be very helpful.

We are using 10g on Solaris platform, you help in this regard is much appreciated as always.

Regards,
JV

ops$tkyte%ORA11GR2> create table t
  2  ( x char(200),
  3    y char(200),
  4    a clob,
  5    b clob
  6  )
  7  /

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t values ( 'x', 'y', rpad( '*', 3800, '*' ), rpad('+',3800,'+') );

1 row created.

ops$tkyte%ORA11GR2> insert into t select 'x','y', null,null from dual connect by level <= 1000;

1000 rows created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> analyze table t compute statistics;

Table analyzed.

ops$tkyte%ORA11GR2> select NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tables where table_name = 'T';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
      1001         65            7       1684          1         413

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> truncate table t;

Table truncated.

ops$tkyte%ORA11GR2> insert into t select 'x','y', null,null from dual connect by level <= 1000;

1000 rows created.

ops$tkyte%ORA11GR2> analyze table t compute statistics;

Table analyzed.

ops$tkyte%ORA11GR2> select NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tables where table_name = 'T';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
      1000         65            7       1809          0         405

ops$tkyte%ORA11GR2> update t set a = rpad( '*', 3000, '*' ), b = rpad( '+', 3000, '+' );

1000 rows updated.

ops$tkyte%ORA11GR2> analyze table t compute statistics;

Table analyzed.

ops$tkyte%ORA11GR2> select NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tables where table_name = 'T';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
      1000       1000           24       1546        941        6489

ops$tkyte%ORA11GR2> alter table t move;

Table altered.

ops$tkyte%ORA11GR2> analyze table t compute statistics;

Table analyzed.

ops$tkyte%ORA11GR2> select NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tables where table_name = 'T';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
      1000       1024            0       1561          0        6483

Tom

I need to move some lob columns to out of line storage permanently due to larger lob size.

Does it involve any code change at application level or client side ?

Currently LOB is manipulated via JDBC code.

Thanks

Hi Tom,

I heasrd that we can put BLOB in partitions. But I am not very sure what is this all about.

Could you please suggest if we can improve the performance of a table when the BLOB column is kept with partition?

I am not very sure of this concept so could not provide you an example. Apologies for that.

Hi Tom,

Thanks for the explanation.

In which case, do we partition the LOB tables then?

Does it help in performance when we put the BLOB columns in a seperate tablespace?

Just the follow up of your below comment.
Hi Tom,
As you said,if the blob size is more than 4000 bytes it will use the out line store?
But suppose I am using the block size of say 16KB, the BLOB size is say 13KB and I am defining to
use inline store.
What will Oracle use Inline store or out line store?
Request your suggestions and recommendations.
Thanks



Followup September 17, 2004 - 8pm Central time zone:

4000, like a varchar2

My Doubt:
When block size is 16kb and defining inline storage, then if BLOB size is say 13kb, i think it will store inline if BLOB size is less than 16kb.

Full of confusion....
Why row length 4kb limit is applied when we have 4k,8k,16k or 32k block size, i think it should be able to store the string(BLOB or CLOB) inline as block size is greater than string length 4kb.

Could you please help me in clarifying my above doubts in detail.

Tom -

I need to perform analysis of an existing schema with many CLOB columns to determine which, if any, should be stored out-of-line (disable storage in row). I've read the following in the SecureFiles and Large Objects Developer's Guide:

+++++++++++++++++++++++++++++++++++
Performance Guidelines for Small BasicFiles LOBs
If most LOBs in your database tables are small in size—8K bytes or less—and only a few rows have LOBs larger than 8K bytes, then use the following guidelines to maximize database performance:
■ Use ENABLE STORAGE IN ROW.
■ Set the DB_BLOCK_SIZE initialization parameter to 8K bytes and use a chunk size of 8K bytes.
■ See "LOB Storage Parameters" on page 11-4 information on tuning other parameters such as CACHE, PCTVERSION, and CHUNK for the LOB segment.
+++++++++++++++++++++++++++++++++++

I have two questions in regard to the above: 1) For the purposes of my analysis why would I be concerned about LOBS being larger/smaller than 8k when ~4k is the point beyond which the value is always stored out-of-line? It seems like the 4k threshold should enter into the analysis somewhere. 2) When determining if most of the LOB values are small what is a reasonable threshold to use? 80%? I.e. if 80% of the LOBS are smaller than the 8k (or 4k) threshold then is it reasonable to say that most of the LOBS are small (and therefore ENABLE STORAGE IN ROW should be used)?

Thanks in advance...
Randy

Hi Tom

we are noticing huge amount of buffer busy waits on frequently accessed clob field. We are 11.1.0.7 Real application clusters, 8k block size.

The avg size is 8k and chunk size 8k.
We tried several different storage options (basicfile/securefile, cache / no cache/ cache reads disable/enable in row) but still could not eliminate the buffer busywaits on the LOB segments.

Since each clob is stored in single block (or more), not sure how block level contention could happen on this LOB segment.

The clob is inserted/updated/read a lot. First updated with empty_clob() followed by the lob data

update px_promoarg set detail=empty_clob() , OPTCOUNTER=COALESCE(NULLIF(OPTCOUNTER, 32767)+1, 1) where orders_id=:1


Any advise on how to get to the bottom of this?

Thanks
Balaji

Here is some details

Testing Method: Load runner executing user transactions (100 virtual users)

Duration: 1 hour 30 minutes

LOB details:
LOB Tablespace index chunk pctversion Cache Securefile
SYS_LOB0000455456C00002$$ LOBDAT01 SYS_IL0000455456C00002$$ 8192 20 No NO


Segments by Global Cache Buffer Busy


Tablespace Name Object Name Obj.Type GC Buffer Busy % of Capture

LOBDAT01 SYS_LOB0000455456C00002$$ LOB 20,893 16.06


Segments by Direct Physical Reads

Tablespace Name Object Name Obj. Type Direct Reads %Total
LOBDAT01 SYS_LOB0000455456C00002$$ LOB 54,943 12.64

Segments by Direct Physical Writes

Tablespace Name Object Name Obj. Type Direct Writes %Total
LOBDAT01 SYS_LOB0000455456C00002$$ LOB 55,790 18.75


Thanks
Bala