Slide 21
Slide 21 text
© 2022 NTT DATA Corporation 21
2. Utilizing pending statistics –issue #2 in 19c
gather_table_stats doesn't gather related index stats properly when pending stats are
enabled
#2
After gathering pending stats on a table, the num_rows of the index becomes 0, even if the table contains
rows.
Symptom
The (published) num_rows of the table is 0
Pending statistics is enable on the table
Execute gather_table_stats with cascade option TRUE
Condition
Bug (was a new bug and not fixed at least until 19.11)
Cause
Although it is not usual to gather and publish statistics of empty tables, it may require careful consideration
when num_rows of a table becomes 0 such as creating new tables.
For a workaround, check indexes with num_rows=0 by the following SQL and examine the statistics are
correct BEFORE publish. For indexes with wrong statistics, gather the index statistics manually by
dbms_stats.gather_index_stats.
Resolution
Community discussion with reproduceable test case
gather_table_stats doesn't gather related index stats properly when pending stats are enabled
https://community.oracle.com/mosc/discussion/4497632
Reference
-- check sql of index stats
select t.owner, t.table_name, dt.num_rows nrows, t.num_rows p_nrows, i.index_name, i.num_rows p_nrows
from dba_tab_pending_stats t, dba_ind_pending_stats i, dba_tab_statistics dt
where t.owner=i.owner and t.table_name=i.table_name
and dt.owner=t.owner and dt.table_name=t.table_name
and t.num_rows>0 and i.num_rows =0 and dt.num_rows = 0
order by t.owner, t.table_name,i.index_name;
-- check sql of index stats
select t.owner, t.table_name, dt.num_rows nrows, t.num_rows p_nrows, i.index_name, i.num_rows p_nrows
from dba_tab_pending_stats t, dba_ind_pending_stats i, dba_tab_statistics dt
where t.owner=i.owner and t.table_name=i.table_name
and dt.owner=t.owner and dt.table_name=t.table_name
and t.num_rows>0 and i.num_rows =0 and dt.num_rows = 0
order by t.owner, t.table_name,i.index_name;