Refreshing Stale Statistics

The cost based optimizer (CBO) relies on accurate statistics to make the correct choices when determining execution plans for queries. Several mechanisms are available to refresh server statistics including the ANALYZE command, the DBMS_UTILITY package and the DBMS_STATS package. The DBMS_STATS package allows you to refresh only those statistics that are out of date, or stale. This feature is very important for large and complex databases where refreshing statistics for all objects can cause a heavy drain on server resources. This article describes how the process of gathering stale stats can be implemented.

First we create a table with the monitoring option switched on by using the MONITORING clause.

CREATE TABLE departments (
  dept_id      NUMBER(10)   NOT NULL,
  description  VARCHAR2(50) NOT NULL)
MONITORING;

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk
  PRIMARY KEY (dept_id));

ANALYZE TABLE departments COMPUTE STATISTICS;

The ALTER TABLE command is used to switch the monitoring option on or off for existing tables.

ALTER TABLE departments NOMONITORING;
ALTER TABLE departments MONITORING;

In Oracle9i, table monitoring can be switched on and off for a schema or the whole database with a single command.

-- Schema level.
EXEC DBMS_STATS.alter_schema_tab_monitoring('MYSCHEMA', TRUE);  -- On
EXEC DBMS_STATS.alter_schema_tab_monitoring('MYSCHEMA', FALSE); -- Off

-- Database level.
EXEC DBMS_STATS.alter_database_tab_monitoring(TRUE);  -- On
EXEC DBMS_STATS.alter_database_tab_monitoring(FALSE); -- Off

In Oracle8i, a similar result is achieved by using the following scripts:

The MONITORING column of the %_TABLES views is used to identify the current monitoring status of a table.

SELECT table_name,
       monitoring
FROM   user_tables;

TABLE_NAME                     MON
------------------------------ ---
DEPARTMENTS                    YES

The current statistics for the DEPARTMENTS table are displayed below.

SELECT table_name,
       num_rows,
       avg_row_len
FROM   user_tables;

TABLE_NAME                       NUM_ROWS AVG_ROW_LEN
------------------------------ ---------- -----------
DEPARTMENTS                             0           0

Next we add some rows to the DEPARTMENTS table.

BEGIN
  FOR c_number IN 1 .. 1000 LOOP
    INSERT INTO departments (dept_id, description)
    VALUES (c_number, 'Description - ' || To_Char(c_number));
  END LOOP;
  COMMIT;
END;
/

After some time Oracle populates the %_TAB_MODIFICATIONS views with information relating to the extent of the modification made to monitored tables.

SELECT *
FROM   user_tab_modifications;

This information is used when refreshing stale statistics using the DBMS_STATS package and the GATHER AUTO option.

BEGIN
  DBMS_STATS.gather_schema_stats (
    ownname          => USER,
    cascade          => TRUE,
    options          => 'GATHER AUTO');
END;
/

The results of the statistics refresh are evident in the %_TABLES views, as shown below.

SELECT table_name,
       num_rows,
       avg_row_len
FROM   user_tables;

TABLE_NAME                       NUM_ROWS AVG_ROW_LEN
------------------------------ ---------- -----------
DEPARTMENTS                          1000          21

To automate the gathering of statistics, simply schedule a job to the call the DBMS_STATS package. The example below refreshes stale statistics for for schema "TIM" at midnight every day.

SET SERVEROUTPUT ON
DECLARE
  l_job  BINARY_INTEGER;
BEGIN
  DBMS_JOB.submit (
    job => l_job,
    what => 'BEGIN DBMS_STATS.gather_schema_stats (' ||
            'ownname => ''TIM'', cascade => TRUE, options => ''GATHER AUTO''); END;',
    next_date => SYSDATE,
    interval => '/* 1 Day Interval */ TRUNC(SYSDATE) + 1');
  COMMIT;
  DBMS_OUTPUT.put_line('Job: ' || TO_CHAR(l_job));
END;
/

Alternatively we could set monitoring on for all database tables, with the exception of those in the SYS schema, and use the following procedure:

DBMS_STATS.gather_database_stats(cascade => TRUE, options => 'GATHER AUTO');

Since only those tables with stale or empty stats are processed the overhead on the system is greatly reduced.

Note. In Oracle 10g, gathering stale statistics for the database once a day happens by default.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.