Skip to main content

Search

Welcome to Tibero GTS!

Cases Subject to Automatic Statistics Collection (options=>'Gather Auto') and How to Check

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.
Cautione
Apply 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

 

 

 

Related to