10053 Event to Trace CBO Choices

Why does autotrace sometimes not show me the actual plan used:-

1. explain plan does not bind variable peek, a real query would.
   
2. explain plan sees all binds as varchar2's - in real life they are numbers, dates, varchar2's and so on. Hence, implicit conversions may or may not be observed by explain plan that would be observed in real life.
   
3. explain plan does a hard parse, uses the statistics/dynamic samples that are IN PLACE RIGHT NOW.  A query that was run for real "a little while ago" or "a little while from now" might see different inputs.
   
4. explain plan uses the current operational environment to come up with a plan.  You might be explaining in a FIRST_ROWS session, whereas the query was actually executed in a ALL_ROWS session (or any one of the optimizer parameters could be different).
   
5. any combination of 1, 2, 3, 4...

refer: https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:63445044804318

Note that this trace event is undocumented, unsupported, and works only in certain 
circumstances.

They provide information to the support analysts that can be useful in diagnosing 
optimizer-related issues. However, an example will show you what you might expect to see 
in the trace file generated by this event. In order to generate the CBO trace file, we 
need to set an event and then simply parse a query. One technique is as follows:

big_table@ORA920> ALTER SESSION SET EVENTS 
  2 '10053 trace name context forever, level 1';
Session altered.

big_table@ORA920> explain plan for 
  2 select * from big_table where object_id = 55;
Explained.



Now we are ready to inspect the trace file. You can use the same technique outlined in 
the "TKPROF" section of Chapter 2 to get a trace filename for your session (see that 
chapter for details if you do not know how to identify your session's trace file). Upon 
exiting SQL*Plus and editing the trace file, you will see something like this:

/usr/oracle/ora920/OraHome1/admin/ora920/udump/ora920_ora_23183.trc

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
ORACLE_HOME = /usr/oracle/ora920/OraHome1
System name:    Linux
Node name:  tkyte-pc-isdn.us.oracle.com
Release:    2.4.18-14
Version:    #1 Wed Sep 4 13:35:50 EDT 2002
Machine:    i686
Instance name: ora920
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 23183, image: oracle@tkyte-pc-isdn.us.oracle.com 
This is just the standard trace file header. The interesting stuff comes next:
*** SESSION ID:(15.1158) 2003-01-26 16:54:53.834
QUERY
explain plan for select * from big_table where object_id = 55
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
OPTIMIZER_FEATURES_ENABLE = 9.2.0
OPTIMIZER_MODE/GOAL = Choose
 lots chopped out here
DB_FILE_MULTIBLOCK_READ_COUNT = 16
_NEW_SORT_COST_ESTIMATE = TRUE
_GS_ANTI_SEMI_JOIN_ALLOWED = TRUE
_CPU_TO_IO = 0
_PRED_MOVE_AROUND = TRUE
***************************************

 There, you will find the parameters that affect the CBO. The parameters that start with _ 
are undocumented parameters. Most of the other parameters are discussed in the previous 
sections. These are the parameters you have free access to modify as you see fit.

Caution: Never set _ parameters in a real system without the expressed guidance and 
consent of support. This is not just me "stating the company line" here; this is for 
real. Undocumented parameters have unanticipated side effects (they could be seriously 
damaging to your data, your security, and many other things). Undocumented parameters 
change in meaning from release to release. Every time you upgrade, you need to ask 
support, "Do I still need this undocumented parameter?" I will not discuss the meaning, 
range of values, or use of any of these undocumented parameters.

Next in the trace file you'll find the base statistical information used:

BASE STATISTICAL INFORMATION
***********************
Table stats    Table: BIG_TABLE   Alias: BIG_TABLE
  TOTAL ::  CDN: 1833857  NBLKS:  22188  AVG_ROW_LEN:  84
-- Index stats
  INDEX NAME: OBJECT_ID_IDX  COL#: 4
    TOTAL :: LVLS: 2  #LB: 4202  #DK: 1833792  LB/K: 1  DB/K: 1  CLUF: 21921
  INDEX NAME: OBJECT_TYPE_IDX  COL#: 6
    TOTAL :: LVLS: 2  #LB: 5065  #DK: 27  LB/K: 187  DB/K: 2414  CLUF: 65187
_OPTIMIZER_PERCENT_PARALLEL = 0

You'll see this for all of the referenced objects in the query. This in itself can help 
you diagnose the problem right away. If you know that the cardinality of the table 
BIG_TABLE is really 1 and the number of blocks is 1, right here you can see what the 
problem is. The optimizer sees cardinality (CDN) of 1,833,857 rows and 22,188 blocks 
(NBLKS). So, the statistics would be out of date if there were really just one row. 
Here's a quick explanation of what the abbreviations mean:

CDN    Cardinality, a count of rows
NBLKS    Number of blocks
AVG_ROW_LEN    The computed average row length
COL#    Column numbers in the table the index is on (select * from user_tab_columns where 
column_id = 4 and table_name = 'BIG_TABLE' would reveal the name of the column in this 
case)
LVLS    Number of levels in the B*Tree
#LB    Number of leaf blocks
#DK    Number of distinct keys
LB/K    Number of leaf blocks per key value on average
DB/K    Number of base table data blocks per key value; how many table accesses (logical 
I/O's) would be made using an equality predicate on this index.  Directly related to the 
cluster factor below.
CLUF    Clustering factor of this index; a measure of how sorted a base table is with 
respect to this index.

Many times, a quick scan of this information can pinpoint the problem right away.