10053事件是oracle提供的用于跟踪sql语句成本计算的内部事件，

它能记载CBO模式下oracle优化器如何计算 sql成本，生成相应的执行计划。

如何设置10053事件

设置本session的10053

开启：

Alter session set events’10053 trace name context forever[,level {1/2}]’;

关闭：

Alter session set events’10053 trace name context off’;

设置其他session的10053

开启：

SYS.DBMS_SYSTEM.SET_EV (<sid>, <serial#>, 10053, {1|2}, '')

关闭：

SYS.DBMS_SYSTEM.SET_EV (<sid>, <serial#>, 10053,0, '')

跟其他跟踪事件不同，10053提供了两个跟踪级别，

但是级别2的跟踪信息比级别1少（其他跟踪事件如10046跟踪级别越高信息越多），

跟踪信息将被记录到user_dump_dest目录底下。

注意，要实现跟踪必须满足两个条件：sql语句必须被hard parse并且必须使用CBO优化器模式。

如果sql语句已经被parse过，那么10053不生成跟踪信息。

如果你使用RULE优化器，那么10053 也不会生成跟踪信息。

***************************************

BASE STATISTICAL INFORMATION

***************************************

Table stats    Table: TEST   Alias: TEST

  TOTAL ::  CDN: 999  NBLKS:  13  AVG_ROW_LEN:  86

-- Index stats

  INDEX NAME: IDX_OBJECT_ID  COL#: 4

    TOTAL ::  LVLS: 1   #LB: 3  #DK: 999  LB/K: 1  DB/K: 1  CLUF: 582

_OPTIMIZER_PERCENT_PARALLEL = 0

***************************************

对于TABLE：

Trace label            dba_tables column               describe

CDN                      NUM_ROWS                          The cardinality = number of rows of the table

NBLKS                   BLOCKS                                The number of blocks below the high water mark

AVG_ROW_LEN     AVG_ROW_LEN                    The average length of a row

对于INDEX：

Trace label          dba_indexes column              describe

LVLS                   BLEVEL                                   The height of the index b-tree

#LB                    LEAF_BLOCKS                         The number of leaf blocks

#DK                   DISTINCT_KEYS                       The number of distinct keys of the index

LB/K                   AVG_LEAF_BLOCKS_PER_KEY  The average number of leaf blocks per key

DB/K                  AVG_DATA_BLOCKS_PER_KEY   The average number of data blocks per key

CLUF                 CLUSTERING_FACTOR               The clustering factor of the index

验证如下：

SQL> select TABLE_NAME,

  2         NUM_ROWS "CDN",

  3         BLOCKS "NBLKS",

  4         AVG_ROW_LEN "AVG_ROW_LEN "

  5    From dba_tables

  6   where table_name = 'TEST'

  7     and owner = 'SYS';

TABLE_NAME                            CDN      NBLKS AVG_ROW_LEN

------------------------------          ---------- ---------- ------------

TEST                                        999         13           86

SQL> select index_name,

  2         BLEVEL "LVLS",

  3         LEAF_BLOCKS "#LB",

  4         DISTINCT_KEYS "#DK",

  5         AVG_LEAF_BLOCKS_PER_KEY "LB/K",

  6         AVG_DATA_BLOCKS_PER_KEY "DB/K",

  7         CLUSTERING_FACTOR "CLUF"

  8    from dba_indexes

  9   where owner = 'SYS'

10     AND index_name = 'IDX_OBJECT_ID';

INDEX_NAME                           LVLS        #LB        #DK       LB/K       DB/K       CLUF

------------------------------          ---------- ---------- ---------- ---------- ---------- - ---------

IDX_OBJECT_ID                           1          3           999          1          1            582