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.
