10053事件分析

1）10053介绍：
10053 事件是oracle 提供的用于跟踪sql 语句成本计算的内部事件，它能记载CBO 模式下oracle 优化器如何计算sql 成本，生成相应的执行计划。
用来描述oracle如何选择执行计划的过程，然后输出到trace文件里，因为我们经常看执行计划怎么执行的消耗了哪些资源，而不是常看执行计划怎么选择出来了的。

2）10053特点：
(1) 只可以了解oracle执行计划的选择过程
(2) 无法获知代价的计算公式，因为这是oracle内部的商业机密，而且每个oracle版本的优化器计算公式都不相同差距还是蛮大的，不同版本的同一个语句的代价也不一样，优化器现在还不是很成熟，还有待完善。
(3) 在这个里面我们重点要了解的是“代价”是如何计算出来的，然后我们才能了解执行计划是如何选择的。
(4) 在10053中可以了解哪些因素影响sql的执行代价
(5) 包含cost等价IO+CPU+网络+等待事件+其他代价

说明：
1、跟其他跟踪事件不同，10053提供了两个跟踪级别，但是级别2的跟踪信息比级别1少（其他跟踪事件如10046跟踪级别越高信息越多），跟踪信息将被记录到user_dump_dest目录底下
2、10053只对CBO有效，而且如果一个sql语句已经解析过，就不会产生新的trace信息。
3、如果你使用RULE优化器，那么10053也不会生成跟踪信息。
4、10053事件产生的trace文件不能用tkprof格式化。

3）使用10053事件的方法

SQL> create table t4 as select object_id id ,object_Name from dba_objects;
Table created.
SQL> create index t4_idx on t4(id);
Index created.
SQL> exec dbms_stats.gather_table_stats('TINA','T4',cascade=>true);  --收集统计信息
PL/SQL procedure successfully completed.
SQL> alter session set tracefile_identifier='tina04'; 
Session altered. 
SQL> alter session set events '10053 trace name context forever,level 1';
Session altered.
SQL> explain plan for select id,count(*) from t4 where id<2000 group by id;
Explained.
SQL> alter session set events '10053 trace name context off';
Session altered.

查看:
[oracle@oratest trace]$ cd /u01/diag/rdbms/tinadb/tinadb/trace
[oracle@oratest trace]$ ll -t |head -10
total 17552
-rw-r-----. 1 oracle oinstall  150199 Dec 25 17:30 tinadb_mmon_21083.trc
-rw-r-----. 1 oracle oinstall   16262 Dec 25 17:30 tinadb_mmon_21083.trm
-rw-r-----. 1 oracle oinstall   81658 Dec 25 17:30 tinadb_ora_5860_tina04.trc   --刚刚标记tina04的trace文件
-rw-r-----. 1 oracle oinstall   31379 Dec 25 17:30 tinadb_ora_5860_tina04.trm
-rw-r-----. 1 oracle oinstall  528149 Dec 25 17:19 alert_tinadb.log
-rw-r-----. 1 oracle oinstall    8878 Dec 25 17:19 tinadb_arc1_21174.trc
-rw-r-----. 1 oracle oinstall    1884 Dec 25 17:19 tinadb_arc1_21174.trm
-rw-r-----. 1 oracle oinstall  126591 Dec 25 17:12 tinadb_ckpt_21077.trc
-rw-r-----. 1 oracle oinstall    3288 Dec 25 17:12 tinadb_ckpt_21077.trm

[oracle@oratest trace]$ more tinadb_ora_5860_tina04.trc  ---只抽取部分内容显示
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T4[T4]
  Table: T4  Alias: T4
    Card: Original: 74797.000000  Rounded: 1956  Computed: 1955.98  Non Adjusted: 1955.98
  Access Path: TableScan
    Cost:  102.84  Resp: 102.84  Degree: 0
      Cost_io: 102.00  Cost_cpu: 17594333
      Resp_io: 102.00  Resp_cpu: 17594333
  Access Path: index (IndexOnly)
    Index: T4_IDX
    resc_io: 6.00  resc_cpu: 433929
    ix_sel: 0.026151  ix_sel_with_filters: 0.026151
    Cost: 6.02  Resp: 6.02  Degree: 1
  Best:: AccessPath: IndexRange
  Index: T4_IDX
         Cost: 6.02  Degree: 1  Resp: 6.02  Card: 1955.98  Bytes: 5

Join order[1]:  T4[T4]#0
***********************
Best so far:  Table#: 0  cost: 6.0206  card: 1955.9757  bytes: 9780
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000

*********************************
Number of join permutations tried: 1
*********************************
Enumerating distribution method (advanced)

GROUP BY adjustment factor: 1.000000
GROUP BY cardinality:  1956.000000, TABLE cardinality:  1956.000000
    SORT ressource         Sort statistics
      Sort width:         228 Area size:      200704 Max Area size:    40264704
      Degree:               1
      Blocks to Sort: 4 Row size:     16 Total Rows:           1956
      Initial runs:   1 Merge passes:  0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 21984656
      Total Temp space used: 0
Trying or-Expansion on query block SEL$1 (#1)
Transfer Optimizer annotations for query block SEL$1 (#1)
id=0 frofkke[i] (index stop key) predicate="T4"."ID"<2000
GROUP BY adjustment factor: 1.000000
Final cost for query block SEL$1 (#1) - All Rows Plan:
  Best join order: 1
  Cost: 6.0206  Degree: 1  Card: 1956.0000  Bytes: 9780
  Resc: 6.0206  Resc_io: 6.0000  Resc_cpu: 433929
  Resp: 6.0206  Resp_io: 6.0000  Resc_cpu: 433929
kkoqbc-subheap (delete addr=0x7f86edf8faa0, in-use=13728, alloc=16408)
kkoqbc-end:
        :
    call(in-use=13416, alloc=49184), compile(in-use=134352, alloc=149736), execution(in-use=175072, alloc=175312)

kkoqbc: finish optimizing query block SEL$1 (#1)
apadrv-end
          :
    call(in-use=13416, alloc=49184), compile(in-use=135264, alloc=149736), execution(in-use=175072, alloc=175312)


Starting SQL statement dump

user_id=84 user_name=TINA module=SQL*Plus action=
sql_id=cz79brb4k269b plan_hash_value=1839880947 problem_type=3
----- Current SQL Statement for this session (sql_id=cz79brb4k269b) -----
explain plan for select id,count(*) from t4 where id<2000 group by id
sql_text_length=70


4）设置其他session的10053
开启：
   sys.dbms_system.set_ev(<sid>,<serial#>,10053,{1|2},")
关闭：
   sys.dbms_system.set_ev(<sid>,<serial#>,10053,0,")