Oracle Database - Statistics - Stale

About

If a monitored table has been modified more than 10%, then these statistics are considered stale and gathered again.

This is determined by looking at the *_tab_modifications views. This table shows the approximate number of INSERTs, UPDATEs, and DELETEs for that table and whether the table has been truncated, since the last time statistics were gathered. A data-modification may propagate with a few minutes delay(Otherwise, use the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure)

Articles Related

Management

Enabled

This monitoring is enabled by default when STATISTICS_LEVEL is set to TYPICAL or ALL.

List

_tab_statistics

select * from all_tab_statistics where stale_stats = 'YES';

user_tab_statistics doesn’t report nested tables, GATHER_SCHEMA_STATS below can.

GATHER_SCHEMA_STATS

The 'LIST STALE' option of the GATHER_SCHEMA_STATS returns list of stale objects as determined by looking at the *_tab_modifications views.

Snippet:

set serveroutput on
DECLARE
  staleObjects SYS.DBMS_STATS.ObjectTab;
  staleObject  SYS.DBMS_STATS.ObjectElem;
  tab CHAR(1) := chr(9);
-- Where the objectTab type is
-- TYPE ObjectElem IS RECORD (
--  ownname     VARCHAR2(30),     -- owner
--  objtype     VARCHAR2(6),      -- 'TABLE' or 'INDEX'
--  objname     VARCHAR2(30),     -- table/index
--  partname    VARCHAR2(30),     -- partition
--  subpartname VARCHAR2(30));    -- subpartition
-- type ObjectTab is TABLE of ObjectElem;
BEGIN
 SYS.DBMS_STATS.GATHER_SCHEMA_STATS ( 'QS_DWH', options => 'LIST STALE', objlist => staleObjects); 
 DBMS_OUTPUT.put_line ('Index' || tab || 'ownname' || tab || 'objtype' || tab || 'objname' || tab || 'partname' || tab || 'subpartname' );
 FOR indx IN 1 .. staleObjects.COUNT LOOP
   staleObject := staleObjects(indx);
   DBMS_OUTPUT.put_line (indx || tab || staleObject.ownname || tab || staleObject.objtype || tab || staleObject.objname || tab || staleObject.partname || tab || staleObject.subpartname );
 END LOOP;
END;
/

Management

Gathering

The GATHER_DATABASE_STATS or GATHER_SCHEMA_STATS procedures gather new statistics for tables with stale statistics when the OPTIONS parameter is set to:

GATHER STALE
or GATHER AUTO.