10053事件分析

[size=small]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,")[/size]