Oracle LOB issue

For lob data we should pay attention for two options:

store as (CHUNK NK disable storage in row/enable storage in row) that we specified during the table creation means whether allow the Lob data to be stored in the row or not, If we build the table with lob column "enable storage in row", Oracle will have the lob data(if the lob data < 3964 bytes) stored in the table(data segment), if lob data > 3964 bytes,only LOB locater and some of the CHUNK address will be stored in the table(36-84 bytes), the lob segment will contain the CLOB data. If we build table with lob column "disable storage in row", Oracle will store the lob data sepreately in both table and lob segment, in table there is only a LOB locater which will point to the lob index for the chunk of lob segment.

Chunk N k presents the "disable storage in row", I mean the lob data store in the lob segment,it means the the min-size of LOB block. You can refers to db_block_size for setting, Note that A chunk can only store 1 row for a lob data no matter how much size was set to a chunk, for example, if you specify the 16k chunk size, but you will only use 5k for one row lob data stored in log segment,  you will have to use the 16k size for chunk storage usage.

storage as(cache|nocahce), means wether allow the read/write lob data via buffer cache or not. by default the nocache option will be selected, it mean all the operation related with lob data in this table, will not go through the buffer cache instead of direct read/write with PGA buffer.

I have made a test in Oracle 10gR2 for disable/enable storage in row. See below, Since the default mode for table creation with lob column is "enable storage in row", if the data stored in the column < 3964 bytes, we can seek the data directly from the table(data segment) with highly performance compared with the lob data stored in the column > 3964 bytes.


SQL> conn hujinpei/hujinpei;
Connected.
SQL> create table alantest
  2  (name1 clob,name2 clob) lob(name2) store as (disable storage in row);

Table created.

SQL> declare
  2  v_buffer clob;
  3  v_clob1 clob;
  4  v_clob2 clob;
  5  begin
  6  insert into alantest (name1,name2) values (empty_clob(), empty_clob())
  7  returning name1,name2 into v_clob1,v_clob2;
  8  v_buffer := dbms_random.string('A',4000)||dbms_random.string('A',4000);
  9  dbms_lob.writeappend(v_clob1, length(v_buffer), v_buffer);
 10  dbms_lob.writeappend(v_clob2, length(v_buffer), v_buffer);
 11  commit;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL> select dbms_lob.getlength(name1) len_name1,dbms_lob.getlength(name2) len_name2 from alantest;

 LEN_NAME1  LEN_NAME2
---------- ----------
      8000       8000

SQL> set serveroutput on
SQL> Declare
  2       varC clob;
  3       vRStr varchar2(10000);
  4       ln number(4);
  5       Strt number(4);
  6  Begin
  7       select name1 into varC from alantest;
  8       ln := DBMS_LOB.GetLength(varC);
  9       Strt := 1;
 10       DBMS_LOB.Read(varC, ln, Strt, vRStr);
 11       DBMS_output.put_line('Return: '||vRStr);
 12  End;
 13  /
Return:
QOuMRVjRNObaOTMJrchrpxgJiltSWwprSUeVerkoeUVBjqsKSClEdhBzAneoRYIfjGvtLwyWxCsYnYUB
neWAdqqSIJJvPsUOaQuunqhPFedRGWAoSBovPyxrPFpapTkXTmTbPBRtJARjNqGaLyreqSFqWjVcJRnY
RwsVXShbBGecpDSbDgFglqOpWoDZTFRawKFkJnCMEWGgVYRZPaIvHCOnlwvqXzCyQkMyrVoICLMHOgPm
PMUaHgOiGAbgmFLVGTeMuFiSmVCWidZAIQRefRhWlnfdbzdJfrUJmxEBhXxpJojORsLSXsrrHJqVFLbR
MWgUbeCpnOUpFQlCgqwysVAmBKfrIgSutdKkSlEEHgHcJctnAmUxgcEOhLMjKUkhsfaYBpGnWDbBxuaC
xzppIXIaoWUcGolzLiBKSAbXqwyeXfQeLBZSrsHgBvBHesnimdMPvCZovoTfCYFzYtvyhtgNdCSwtOGc
ZLtShpTHpsyuRNgZpxGLmTvPfeMoRfRVzNyjOYHNmuszHNnSzYIfiBsEqGGyVkcpGxdgmnnbryKtgiif
DUTzhJapTFijJjDsrVftAaurWSZOkaktaayCuCfZCMPvXrUjTqnhJcWansOSkKjtfhmDliSthupYROaq
JMwuDHUNTNVeNOTitCzIZndBlcFbrxRwFLNnSEGzuIiywlcbxpgrCDsupCWuEmJYYiRfSahnKhHWYFdi
RmTtAKquNjiWqQXSqAdPMJOIWoNaIVcHopoFwzSlSzBJqFFBhlmkOrqoahUJfTBCZWbnQqDJAfZCOTJE
ZALXNTvPXIOcSUnsWdwGwJKhRMndLzsAhfgsmxRweqPjLrBWJWJKdTmrMzBYToQEovUFRFpBUMqHAMEa
ixooTfsufiRFliBTlkocwrXdizSdgqRvwpNnTIyTdmKDnqUFwekwDbERxGRAVKpbXesFrAnOWzfjhrjf
eCZjRWMtwfnpSovBQDIerHUSeQglTnwbIrjngCVUJfuJoiQmDoNZzDxISFUkJJVFWWczqQVZjDofqkFh
EuKSrujbxEXpLqagCXJezlMmsCEqoCpXzfDADFgUbApCHvGlaxKpWRTwwPJfxZyxjbqPvGBdXyhGhTYS
zPgLUITwuNgAefSLcfRCCHPxvrwEkrPwfprRiSueoXQamTpzdyJkaoQvHcQFAjsaCYSMxMpRCicoaExZ
qFoLffsmSHWjBmErvjmjfeQghmZBsOVyiEQgOlMUfp........

Both the value/size(=8000bytes) of lob column is the same, the length of them > 3964 bytes, the real value of the lob data was stored outside the row(stored in the lob segment), let us see the statistic for each query.

SQL> alter system flush buffer_cache;

System altered.

SQL> select name1 from alantest;

NAME1
--------------------------------------------------------------------------------
QOuMRVjRNObaOTMJrchrpxgJiltSWwprSUeVerkoeUVBjqsKSClEdhBzAneoRYIfjGvtLwyWxCsYnYUB


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

SQL> select name2 from alantest;

NAME2
--------------------------------------------------------------------------------
QOuMRVjRNObaOTMJrchrpxgJiltSWwprSUeVerkoeUVBjqsKSClEdhBzAneoRYIfjGvtLwyWxCsYnYUB


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

The Physical Read for both lob column read are the same by reason of the Direct Path Read(LOB) with NOCACHE, But the logical read of name2(disable stroage in row) will be higher than than name1, In this example, we can't distinguish more abt the value difference,because of customize value, but we can make a conclusion that the additional logical read is caused by the LOB index scan.

From my understanding:
 
   For column name1(ENABLE STORAGE IN ROW) > 3964 bytes, these are totally 12-20 address of CHUNK and LOB_LOCATER will be stored inside the row.

   For column name2(DISABLE STORAGE IN ROW) > 3964 bytes there is only LOB_LOCATER stored inside the row, all the address of CHUNK will be stored in the LOB INDEX. Oracle will use LOB_LOCATER to seek the LOB INDEX Entry which stored the CHUNK address, then found out the CHUNK store in the LOB segment.

   In this example that i have done, 8000 bytes for each columns will be taken up 2 CHUNKs, The name1 query will scan the LOB data from existing CHUNK stored in table directly but name2 will not. So i think, if we control the lob column size within 12 CHUNK size, the frequency of scaning index will be eliminate, and the performance will be improved. 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12361284/viewspace-582730/，如需轉載，請註明出處，否則將追究法律責任。