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

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Sat, 17 Jan 2009 21:21:40 +0100
Message-Id: <AF33D7DA-E281-4951-90D1-CE51E2A96B2B_at_gmail.com>

Hi List,

After a migration from 9i to 10.2.0.3 I was asked about a strange increasing of AVG_ROW_LEN on a table with a LOB.

Can anyone give me a hint what happens?

I tried to simplify the case and came to this testcase (could be easier, but at least it looks stable and reproduceable):

---

create tablespace LOB datafile '........' size 64m segment space management manual;

create user lobtest identified by lobtest;

grant connect, resource to lobtest;

connect lobtest/lobtest

drop table lobtest;
PURGE TABLE lobtest;

create table lobtest (n number, v varchar(4000), l clob) TABLESPACE LOB; exec dbms_random.seed(0);

insert into lobtest

   select rownum, lpad('x', dbms_random.value(0,200)-1, to_char(rownum)),

                                lpad('x', dbms_random.value(0,200)-1,  

to_char(rownum))

   from dual
   connect by level <=500;
commit;

exec dbms_stats.gather_table_stats(USER, 'LOBTEST');

declare
c number default 500;
begin

   loop
     update lobtest set v = lpad('x', dbms_random.value(0,4000)-1, to_char(rownum)) where lobtest.n = c;

     update lobtest set l = lpad('x', dbms_random.value(0,4000)-1, to_char(rownum)) where lobtest.n = c;

     c:= c - 1;
     exit when c = 0;

   end loop;
   commit;
end;
/

exec dbms_stats.gather_table_stats(USER, 'LOBTEST');

select table_name, --AVG_SPACE, CHAIN_CNT,

            AVG_ROW_LEN --, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS
from user_tables;

select sum(vsize(v))/count(*) from lobtest;

select sum(dbms_lob.getlength(l))/count(*) from lobtest;

select segment_name, blocks from user_segments;

---

here the results:

---

9.2.0.8:

---

TABLE_NAME                     AVG_ROW_LEN
------------------------------ -----------
LOBTEST                               2039

SUM(VSIZE(V))/COUNT(*)

---

               2034.782

SUM(DBMS_LOB.GETLENGTH(L))/COUNT(*)

---

                            2029.312

SEGMENT_NAME                         BLOCKS
-------------------------------- ----------
LOBTEST                                 384
SYS_IL0000084037C00003$$                  8
SYS_LOB0000084037C00003$$               256



========================================

10.2.0.4:

---

TABLE_NAME                       AVG_ROW_LEN
-------------------------------- -----------
LOBTEST                                 3204

SUM(VSIZE(V))/COUNT(*)

---

               2034.782

SUM(DBMS_LOB.GETLENGTH(L))/COUNT(*)

---

                            2029.312

SEGMENT_NAME                         BLOCKS
-------------------------------- ----------
LOBTEST                                 384
SYS_LOB0000063116C00003$$               256
SYS_IL0000063116C00003$$                  8



========================================

11.1.0.6:

---

TABLE_NAME                       AVG_ROW_LEN
-------------------------------- -----------
LOBTEST                                 3204

SUM(VSIZE(V))/COUNT(*)

---

               2034.782

SUM(DBMS_LOB.GETLENGTH(L))/COUNT(*)

---

                            2029.312

SEGMENT_NAME                         BLOCKS
-------------------------------- ----------
LOBTEST                                 384
SYS_LOB0000086118C00003$$               256
SYS_IL0000086118C00003$$                  8


========================================

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.

Every hint appreciated, as always.

thank you,
  Martin

--
http://www.freelists.org/webpage/oracle-l




application/pkcs7-signature attachment: smime.p7s

Received on Sat Jan 17 2009 - 14:21:40 CST