Re: detecting chained rows and other stats stuff

Ben wrote:
> 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....

analyze table <table_name> list chained rows into <chain table, created by $ORACLE_HOME/rdbms/admin/utlchn.sql (rowid version) or $ORACLE_HOME/rdbms/admin/utlchn1.sql (urowid version)
>

-- 
Sybrand Bakker
Senior Oracle DBA