Re: detecting chained rows and other stats stuff

sybrandb wrote:
> 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

So I have to run that along with dbms_stats. Does it take as long as doing an analzye compute?
I understand that dbms_stats only generates stats that the CBO uses, but it's just aggravating to have to do all the extra work to find answers that used to be generated when statistics were gathered. I guess I'm just bitching about it in general.. oh well. Received on Mon Nov 13 2006 - 09:39:01 CST