Document Type | Technical Information
Category | Administration
Applicable Product Versions | 6FS06, 6FS07
Document Number | TADTI044
Overview
This document explains the cases subject to automatic statistics collection (options => 'Gather Auto') and how to verify them.
Statistics are collected for tables that satisfy one or more of the following four conditions.
- Tables for which statistics have not been collected
- Tables where the row count was recorded as 0 in the last statistics collection result
- Tables where the DML workload exceeds 10% of the total row count
- Tables for which a certain period has passed since the last statistics collection period
Methods
1. Tables for which statistics have not been collected
Refer to the LAST_ANALYZED column in DBA_TABLES.
2. Tables where the row count was recorded as 0 in the last statistics collection result
Refer to the NUM_ROWS column in DBA_TABLES.
3. Tables where the DML workload exceeds 10% of the total row count
This condition can be utilized when the FS06_277051q patch is applied.
DBA_TAB_MODIFICATIONS and DBA_TAB_STATISTICS views have been added.CautioneApply the patch through technical support provided by TmaxTibero.
Example
select owner, object_name from dba_tab_modifications m, (select obj.owner, obj.object_name, dt.row_cnt from _dd_tbl dt, dba_objects obj where dt.obj_id = obj.object_id and obj.object_type like '%TABLE%') t where (m.inserts + m.deletes + m.updates > t.row_cnt/10) and t.owner = m.table_owner and t.object_name = m.table_name order by 1, 2;
select owner, table_name from dba_tab_statistics where stale_stats = 'YES' order by 2;
4. Tables for which a certain period has passed since the last statistics collection period
Refer to the LAST_ANALYZED column in DBA_TABLES, and the collection interval can be adjusted through hidden parameters._DBMS_STATS_GATHER_AUTO_STALED:DAILY,WEEKLY,MONTHLY,NONE