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@xxxxxxxxx> wrote: > From: Martin Berger <martin.a.berger@xxxxxxxxx> > Subject: Re: different avg_row_len in 9i and 10g+ at table with LOB > To: yong321@xxxxxxxxx > Cc: oracle-l@xxxxxxxxxxxxx > 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. -- //www.freelists.org/webpage/oracle-l