Gather Statistics Intro

Gather Statistics Intro

Optimizer statistics are a collection of data that describe more details about the database and the objects in the database. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement.


Optimizer statistics include the following:

·         Table statistics

o        Number of rows

o        Number of blocks

o        Average row length

·         Column statistics

o        Number of distinct values (NDV) in column

o        Number of nulls in column

o        Data distribution (histogram)

o        Extended statistics

·         Index statistics

o        Number of leaf blocks

o        Levels

o        Clustering factor

·         System statistics

o        I/O performance and utilization

o        CPU performance and utilization

Because the objects in a database can be constantly changing, statistics must be regularly updated so that they accurately describe these database objects. Statistics are maintained automatically by Oracle or you can maintain the optimizer statistics manually using the DBMS_STATS package.

 Hope this help. Regards Rupam