Optimizer Statistics

To know about the database and the objects in the database we use optiimizer statistics.In simple optimzer statistics provides information about the database and the objects in the database. For every SQL statement there is execution plan, that execution plan is decided by query optimizer.Optimzer statistics include the Table statistics, Index statistics, Column statistics, System statistics:

- Table statistics

• Number of rows
• Number of blocks
• Average row length 

- Column statistics

• Number of distinct values(NDV) in column
• Number of nulls in column
• Data distribution

- Index statistics 

• Number of leaf blocks
• Levels
• Clustering factor 

- System statistics

• I/O performance and utilization 
• CPU performance and uitilization 

By using data dictionary you can view the optimizer statistics that are stored in the data dictionary and the views are:

• DBA_TABLES
• DBA_INDEXES
• DBA_CLUSTERS
• DBA_TAB_STATISTICS
• DBA_IND_STATISTICS
• DBA_TAB_PARTITIONS
• DBA_OBJECT_TABLES

People who read this post also read :

Posted in: Data Dictionary