Optimizer statistic in Oracle
You can collect exact or estimated statistics about physical storage characteristics and data distribution in these schema objects by using the DBMS_STATS package.
- Number of leaf blocks
- System statistics: (of the operating System) during a regular workload (otherwise non appropriate values for the CPU costing for instance)
- I/O performance and utilization
- CPU performance and utilization
- Fixed objects (dynamic performance tables)
For example, if a table has not been analyzed since it was created, and if it has less than DB_FILE_MULTIBLOCK_READ_COUNT blocks under the high water mark, then the optimizer thinks that the table is small and uses a full table scan. (Review the LAST_ANALYZED and BLOCKS columns in the ALL_TABLES table to examine the statistics)
- If no statistics are available when using query optimization, the optimizer will do dynamic sampling.
This may cause slower parse times so for best performance, the optimizer should have representative optimizer statistics.
- If the optimizer uses the cost-based approach for a SQL statement, and if some tables accessed by the statement have no statistics, then the optimizer uses internal information, such as the number of data blocks allocated to these tables, to estimate other statistics for these tables.
statistics are stale when 10% or more of the rows in the table have changed since the last time statistics were gathered.
Optimizer Statistics on Database Objects (tables, indexes, columns) are stored in the data dictionary. To view statistics in the data dictionary, query the appropriate data dictionary view (USER, ALL, or DBA). These DBA_* views include the following:
The GATHER_OPTIMIZER_STATISTICS hint instructs the optimizer to enable statistics gathering during the following types of bulk loads:
- CREATE TABLE … AS SELECT
- INSERT INTO … SELECT into an empty table using a direct-path insert
The GATHER_DATABASE_STATS_JOB_PROC procedure collects statistics on database objects when:
- the object has no previously gathered statistics
- or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).
- is an internal procedure (You don't see it in the DBMS_STATS package)
- it operates in a very similar fashion to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The primary difference is that the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure prioritizes the database objects that require statistics, so that those objects which most need updated statistics are processed first. This ensures that the most-needed statistics are gathered before the maintenance window closes.