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

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