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

  • From: Yong Huang <yong321@xxxxxxxxx>
  • To: Martin Berger <martin.a.berger@xxxxxxxxx>
  • Date: Sun, 18 Jan 2009 19:38:32 -0800 (PST)

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


Other related posts: