Re: different avg_row_len in 9i and 10g+ at table with LOB

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Mon, 19 Jan 2009 21:05:09 +0100
Message-Id: <0D434F47-E7E0-4EF4-8601-099271929296_at_gmail.com>



Hi Yong,

I got an aditional hint to Note:66431.1 - LOBS - Storage, Redo and Performance Issues.
This node indicates the limit between in-row and out of row is 3964 bytes.

I can only guess why avg_row_len does not account OOL LOBs: in-line LOBs are accessed directly and therefore can be accounted as ordinary part of the row in the CBO.
out of row LOBs need at least 2 additional IOs (1x index, 1xLOB segment). They are also not cached by default. It seems they are so 'different' to normal rows for the CBO, so there was a decision to not account these objects.

br
  Martin

Am 19.01.2009 um 04:38 schrieb Yong Huang:

> I was not familiar with sys_op_opnsize. I found an explanation in
> Note:6655241
> (DBMS_STATS.GATHER_TABLE_STATS FOR A TABLE WITH LOB COLUMN SLOW IN
> 10.2.0.3):
>
> "In 10gR2 fix for bug 1954150 adds a new internal operator
> SYS_OP_OPNSIZE()
> which then allows DBMS_STATS to get information about LOB columns
> to help determine data sizes for SORT etc.. operations.
> .
> ...SYS_OP_OPNSIZE( col ) which for out of line LOB data just returns
> the size
> of the locator and does not access the out of line LOB data itself."
>
> I verified this by creating the table with in-line (which is default
> for a
> small table) and out-of-line lob. In the OOL case, sys_op_opnsize
> indeed
> returns a much smaller value.
>
> Why does Oracle choose to include in-line LOB in calculating
> avg_row_len but
> only include LOB locator for OOL LOB? I guess maybe common usage of
> an OOL
> LOB is in the select list, NOT in the where clause, while in-line
> LOB may
> be in the where clause? Or it's simply that avg_row_len is for one
> segment,
> not for two segments in whatever way they're related?
>
> You can find out if the LOB is in-line or OOL by looking at
> xxx_lobs.in_rows.
>
> Yong Huang
>
> --- On Sun, 1/18/09, Martin Berger <martin.a.berger_at_gmail.com> wrote:
>
>> From: Martin Berger <martin.a.berger_at_gmail.com>
>> Subject: Re: different avg_row_len in 9i and 10g+ at table with LOB
>> To: yong321_at_yahoo.com
>> Cc: oracle-l_at_freelists.org
>> Date: Sunday, January 18, 2009, 2:22 PM
>> Yong,
>>
>> I checked BUG:1954150.
>> Even it seems LOBs stored in row are now accounted in
>> avg_row_len,
>> LOBs stored in their own Segment are still not accounted.
>> I'm not sure what's the desired method is. I will
>> ask Oracle ;-)
>>
>> But I'm sure, currently sys_op_opnsize does not match
>> DBMS_LOB.GETLENGTH.
>> A quick check on my 10.2.0.4 testcase:
>>
>> select sum(dbms_lob.getlength(l)), sum(sys_op_opnsize(l))
>> from lobtest;
>>
>> SUM(DBMS_LOB.GETLENGTH(L)) SUM(SYS_OP_OPNSIZE(L))
>> -------------------------- ----------------------
>> 1014656 581776
>>
>> Still searching and investigating ...
>>
>> An additional question:
>> Does anyone knows how to find out if a LOB is stored in row
>> or in
>> their own Segment (block dumping is fine, but v$ or
>> anything similar
>> is more appreciated)
>>
>> thnx so far :)
>> Martin
>>
>> --
>> http://berxblog.blogspot.com
>>
>> Am 18.01.2009 um 18:52 schrieb Yong Huang:
>>
>>> Martin,
>>>
>>> Your guess is correct. See bug 1954150
>> "DBMS_STATS.GATHER_TABLE_STATS
>>> CALCULATES AVG_ROW_LEN FOR CLOBS INCORRECTLY".
>> It's fixed in 10gR2.
>>> I can
>>> reproduce it in 10gR1. Tablespace can be ASSM as well.
>>>
>>> Yong Huang
>>>
>>>> 9.2.0.8:
>>>> =====
>>>> TABLE_NAME AVG_ROW_LEN
>>>> ------------------------------ -----------
>>>> LOBTEST 2039
>>>>
>>>> ...
>>>>
>>>> 10.2.0.4:
>>>> ======
>>>> TABLE_NAME AVG_ROW_LEN
>>>> -------------------------------- -----------
>>>> LOBTEST 3204
>>>> ...
>>>> In 9i it looks as if AVG_ROW_LEN is simply the
>> average length of all
>>>> rows except these LOBs.
>>>> I'm not 100% sure what happens in 10g. Maybe
>> it's the average length
>>>> of all rows excelt these LOBs in the dedicated LOB
>> Segments.
>
>
>



--
http://www.freelists.org/webpage/oracle-l


  • application/pkcs7-signature attachment: smime.p7s
Received on Mon Jan 19 2009 - 14:05:09 CST

Original text of this message