Saturday, 26 September 2015

Optimizer Statistical Information

Statistics plays a key role in providing the correct information to the Optimizer while the queries are executed.

For example if we run a query after enabling the optimizer trace

select id from test where id=5

In the trace info we can find

Below is the optimizer statistics about the query

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: FIRST  Alias: FIRST
    #Rows: 1429  #Blks:  5  AvgRowLen:  5.00  ChainCnt:  0.00
Index Stats::
  Index: IDX  Col#: 1
    LVLS: 1  #LB: 4  #DK: 10  LB/K: 1.00  DB/K: 1.00  CLUF: 7.00
Access path analysis for FIRST

After Inserting 50000 records into the table and without running the statistics we can see still see the same info and the explain plan is proportional to the above statistics.

After we run the stats on the table below is the change happened in the statistics information.

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: FIRST  Alias: FIRST
    #Rows: 51429  #Blks:  88  AvgRowLen:  7.00  ChainCnt:  0.00
Index Stats::
  Index: IDX  Col#: 1
    LVLS: 1  #LB: 104  #DK: 50536  LB/K: 1.00  DB/K: 1.00  CLUF: 99.00


Now it has considered all the info.

No comments:

Post a Comment