Trace File 10053 to understand the Optimizer (CBO)

The reason to trace the CBO is to understand why the Optimizer made some decisions.

In this example we are going to see why same query sometimes uses an index and others it doesn’t, same example was explained HERE

It is always useful to have a “GOOD” and “BAD” execution, this way it is easier to see the differences.

We are going to use Beyond Compare to compare text files.

Now lets create the traces from good and bad execution:

Good (query using Index):

    SQL> alter session set tracefile_identifier='10053_compare';
    Session altered.
    SQL> alter session set events '10053 trace name context forever, level 1';
    Session altered.
    
    SQL> EXPLAIN PLAN FOR
    select * from ORDERS where CUSTOMER = 'TOM';  
    Explained.
    
    SQL> alter session set events '10053 trace name context OFF';
    Session altered.
    SQL> 

Bad (query usingFull TableScan):

    SQL> alter session set tracefile_identifier='10053_compare';
    Session altered.
    SQL> alter session set events '10053 trace name context forever, level 1';
    Session altered.
    
    SQL> EXPLAIN PLAN FOR
    select * from ORDERS where CUSTOMER = 'RUBEN';  
    Explained.
    
    SQL> alter session set events '10053 trace name context OFF';
    Session altered.
    SQL> 

Now lets see the traces generated:

In the “good” execution we can see:

    Actual sample size: 10040
    filtered sample card: 10  <<--- less than 1% 
    
    Cost of using TableScan: 17
    Cost of using Index: 2
    
    Result: Best access path is with Index

vs the “bad”:

    Actual sample size: 10040
    filtered sample card: 10000  <<--- nearly all rows
    
    Cost of using TableScan: 17
    Cost of using Index: 84
    
    Result: Best access path is with Full Table Scan

As we can see, the CBO decides to use Full Table Scan because we are returning most of the rows and using an Index would take more time.

We also need to keep in mind that we need accurate statistics, if not, this query could use the Index when it should not.