The PL/SQL package DBMS_STATS lets you generate and manage statistics for cost-based optimization. You can use this package to gather, modify, view, export, import, and delete statistics. You can also use this package to identify or name statistics that have been gathered.
Formerly, you enabled
DBMS_STATS to automatically gather statistics for a table by specifying the
MONITORING keyword in the
TABLE statement. Starting with Oracle Database 11g, the
NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.
Monitoring tracks the approximate number of
DELETE operations for the table since the last time statistics were gathered. Information about how many rows are affected is maintained in the SGA, until periodically (about every three hours) SMON incorporates the data into the data dictionary. This data dictionary information is made visible through the
USER_TAB_MODIFICATIONS views. The database uses these views to identify tables with stale statistics.
To disable monitoring of a table, set the STATISTICS_LEVEL initialization parameter to
BASIC. Its default is
TYPICAL, which enables automatic statistics collection. Automatic statistics collection and the
DBMS_STATS package enable the optimizer to generate accurate execution plans.
Oracle Database Reference for detailed information on the
STATISTICS_LEVEL initialization parameter
Oracle Database Performance Tuning Guide for information on managing optimizer statistics
Oracle Database PL/SQL Packages and Types Reference for information about using the
"About Automated Maintenance Tasks" for information on using the Scheduler to collect statistics automatically