2 hours AWR report shows huge number of fetch continued row statistics value. It is not the case all the time even though same activity being done.
SQL> SELECT TABLE_NAME, chain_cnt,
2 round(chain_cnt/num_rows*100,2) pct_chained,
3 avg_row_len, pct_free , pct_used
4 FROM user_tables WHERE chain_cnt>0;
no rows selected
SQL> SELECT INST_ID, CON_ID, 'Chained or Migrated Rows ='||value
2 FROM Gv$sysstat
3 WHERE name = 'table fetch continued row' AND CON_ID=121;
INST_ID CON_ID 'CHAINEDORMIGRATEDROWS='||VALUE
---------- ---------- ---------------------------------------------
2 31 Chained or Migrated Rows =1274028919
1 31 Chained or Migrated Rows =127702986
Is there anyway to find 'table fetch continued row' between two snap_ids and tables that cause or any objects that resulted this high stats? or any other section that help us to find root cause of this.?
SQL> select
2 owner c1,
3 table_name c2,
4 pct_free c3,
5 pct_used c4,
6 avg_row_len c5,
7 num_rows c6,
8 chain_cnt c7,
9 chain_cnt/num_rows c8
10 from dba_tables
11 where
12 owner not in ('SYS','SYSTEM')
13 and
14 table_name not in
15 (select table_name from dba_tab_columns
16 where
17 data_type in ('RAW','LONG RAW','CLOB','BLOB','NCLOB')
18 )
19 and
20 chain_cnt > 0
21 order by chain_cnt desc
22 ;
no rows selected
SQL>
Auto maintenance jobs enabled and maintenance window job get executed everyday.
Does stale stats of tables do anything with this?. How do we prove stale stats really caused huge table fetch continued row stats?. all i know is table fetched row is all about chained rows or migrated rows.!.