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.
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)
This may cause slower parse times so for best performance, the optimizer should have representative optimizer statistics.
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:
The GATHER_DATABASE_STATS_JOB_PROC procedure collects statistics on database objects when:
The DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC: