oracle 10053 trace
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
