8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Refreshing Stale Statistics
This is a very old article. You may need to read something a little newer, like these.
- Cost-Based Optimizer (CBO) And Database Statistics
- Automatic Optimizer Statistics Collection
- Statistics Collection Enhancements in Oracle Database 11g Release 1
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.
In Oracle 10g, gathering stale statistics for the database once a day happens by default.
For more information see:
- Automatic Optimizer Statistics Collection
- Statistics Collection Enhancements in Oracle Database 11g Release 1
- DBMS_STATS (8i)
- DBMS_STATS (9iR1)
- DBMS_STATS (9iR2)
- DBMS_STATS (10gR1)
- DBMS_STATS (10gR2)
- DBMS_STATS (11gR1)
- DBMS_STATS (11gR2)
Hope this helps. Regards Tim...