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

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.




Other related posts: