detecting chained rows and other stats stuff

From: Ben <balvey_at_comcast.net>
Date: 13 Nov 2006 06:57:16 -0800
Message-ID: <1163429835.925673.114280@b28g2000cwb.googlegroups.com>

9.2.0.5 EntEd AIX5L

So I've went through and ran a little test on a table that I suspected as having chained rows in it. I found that what we see with dbms_stats is totally different than what we were seeing with analyze / compute. I know that some differences are known, but I was unaware that the dbms_stats didn't gather chained row information. Can you all take a look at this and please advise me on how I should be going about finding our chained row tables. Everything that I have seen in the recent times has said to use dbms_stats but if it doesn't correctly calculate chained rows then what else are we supposed to use to get this information?

Here is my test script, I don't know how readable this is going to turn out.

SQL> begin
  2 dbms_stats.delete_table_stats(

  3      ownname => 'PARCTL',
  4      tabname => 'F98860',
  5      cascade_indexes => true
  6      );

  7 end;
  8 /

PL/SQL procedure successfully completed.

select owner, table_name, tablespace_name,

	 num_rows, avg_space, avg_row_len,
	 round(8192/avg_row_len, 2) as "AvgRowBlock",
	 chain_cnt, last_analyzed

from dba_tables
where owner = 'PARCTL'
and table_name = 'F98860'
order by chain_cnt desc

OWNER	     PARCTL
TABLE_NAME	F98860

TABLESPACE_NAME PARCTLT

NUM_ROWS
AVG_SPACE
AVG_ROW_LEN

AvgRowBlock
CHAIN_CNT
LAST_ANALYZED SQL> begin
  2 dbms_stats.gather_table_stats(

  3      ownname => 'PARCTL',
  4      tabname => 'F98860',
  5      cascade => true

  6 );
  7 end;
  8 /

PL/SQL procedure successfully completed.

select owner, table_name, tablespace_name,

	 num_rows, avg_space, avg_row_len,
	 round(8192/avg_row_len, 2) as "AvgRowBlock",
	 chain_cnt, last_analyzed

from dba_tables
where owner = 'PARCTL'
and table_name = 'F98860'
order by chain_cnt desc

OWNER                   PARCTL
TABLE_NAME          F98860
TABLESPACE_NAME  PARCTLT
NUM_ROWS            7359
AVG_SPACE           0
AVG_ROW_LEN      311
AvgRowBlock           26.34
CHAIN_CNT             0

LAST_ANALYZED 11/13/2006 9:32 SQL> begin
  2 dbms_stats.delete_table_stats(

  3      ownname => 'PARCTL',
  4       tabname => 'F98860',
  5      cascade_indexes => true
  6      );

  7 end;
  8 /

PL/SQL procedure successfully completed.

select owner, table_name, tablespace_name,

	 num_rows, avg_space, avg_row_len,
	 round(8192/avg_row_len, 2) as "AvgRowBlock",
	 chain_cnt, last_analyzed

from dba_tables
where owner = 'PARCTL'
and table_name = 'F98860'
order by chain_cnt desc

OWNER                     PARCTL
TABLE_NAME            F98860

TABLESPACE_NAME PARCTLT

NUM_ROWS
AVG_SPACE
AVG_ROW_LEN

AvgRowBlock
CHAIN_CNT
LAST_ANALYZED SQL> analyze table PARCTL.F98860 compute statistics;

Table analyzed.

select owner, table_name, tablespace_name,

	 num_rows, avg_space, avg_row_len,
	 round(8192/avg_row_len, 2) as "AvgRowBlock",
	 chain_cnt, last_analyzed

from dba_tables
where owner = 'PARCTL'
and table_name = 'F98860'
order by chain_cnt desc

OWNER                        PARCTL
TABLE_NAME               F98860
TABLESPACE_NAME    PARCTLT
NUM_ROWS                  7359
AVG_SPACE                 680
AVG_ROW_LEN            1504
AvgRowBlock                 5.45
CHAIN_CNT                   261
LAST_ANALYZED	   11/13/2006 9:34

SQL> begin
  2 dbms_stats.gather_table_stats(

  3      ownname => 'PARCTL',
  4       tabname => 'F98860',
  5      cascade => true

  6 );
  7 end;
  8 /

PL/SQL procedure successfully completed.

select owner, table_name, tablespace_name,

	 num_rows, avg_space, avg_row_len,
	 round(8192/avg_row_len, 2) as "AvgRowBlock",
	 chain_cnt, last_analyzed

from dba_tables
where owner = 'PARCTL'
and table_name = 'F98860'
order by chain_cnt desc

OWNER                      PARCTL
TABLE_NAME             F98860
TABLESPACE_NAME  PARCTLT
NUM_ROWS                7359
AVG_SPACE               680
AVG_ROW_LEN          311
AvgRowBlock               26.34
CHAIN_CNT                 261
LAST_ANALYZED	   11/13/2006 9:35

it looks to me like dbms_stats does nothing with avg_space and then gives an incorrect value for avg_row_len and again does nothing with chain_cnt. This is just aggravating.... Received on Mon Nov 13 2006 - 08:57:16 CST