Oracle Database - Statistics - Stale

> Database > Oracle Database > Oracle Database - Optimizer Statistics

1 - 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)

3 - Management

3.1 - Enabled

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

3.2 - List

3.2.1 - _tab_statistics

SELECT * FROM all_tab_statistics WHERE stale_stats = 'YES';

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

Advertising

3.2.2 - 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;
/

4 - Management

4.1 - 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.
db/oracle/statistics/stale.txt · Last modified: 2017/09/06 19:27 by gerardnico