1.什么是10053事件
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 (, , 10053, {1/2}, '')
关闭:
SYS.DBMS_SYSTEM.SET_EV (, , 10053,0, '')
跟其他跟踪事件不同,10053提供了两个跟踪级别,但是级别2的跟踪信息比级别1少(其他跟踪事件如10046跟踪级别越高信息越多),跟踪信息将被记录到user_dump_dest目录底下。注意,要实现跟踪必须满足两个条件:sql语句必须被hard parse并且必须使用CBO优化器模式。如果sql语句已经被parse过,那么10053不生成跟踪信息。如果你使用RULE优化器,那么10053也不会生成跟踪信息。
2.生成10053事件实验
SQL> alter session set events '10053 trace name context forever,level 1';
Session altered.
SQL> explain plan for select ename from scott.emp where empno=7788;
SQL> select value from v$diag_info where name = 'Default Trace File';
VALUE
-----------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5007.trc
SQL> alter session set events '10053 trace name context off';
Session altered.
3.查看10053 trace文件
SQL> ho vim /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5007.trc
........
****************
QUERY BLOCK TEXT
****************
select ename from scott.emp where empno=7788
.........
***************************************
1-ROW TABLES: EMP[EMP]#0
Access path analysis for EMP
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for EMP[EMP]
Table: EMP Alias: EMP
Card: Original: 14.000000 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
Access Path: TableScan
Cost: 3.00 Resp: 3.00 Degree: 0
Cost_io: 3.00 Cost_cpu: 38427
Resp_io: 3.00 Resp_cpu: 38427
Access Path: index (UniqueScan)
Index: PK_EMP
resc_io: 1.00 resc_cpu: 8341
ix_sel: 0.071429 ix_sel_with_filters: 0.071429
Cost: 1.00 Resp: 1.00 Degree: 1
Access Path: index (AllEqUnique)
Index: PK_EMP
resc_io: 1.00 resc_cpu: 8341
ix_sel: 0.071429 ix_sel_with_filters: 0.071429
Cost: 1.00 Resp: 1.00 Degree: 1
One row Card: 1.000000
Best:: AccessPath: IndexUnique
Index: PK_EMP
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 1.00 Bytes: 0
***************************************
.........
============
Plan Table
============
-----------------------------------------------+-------------------------------
----+
| Id | Operation | Name | Rows | Bytes | Cost | Time
|
-----------------------------------------------+-------------------------------
----+
| 0 | SELECT STATEMENT | | | | 1 |
|
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 10 | 1 | 00:00
:01 |
| 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 |
|
-----------------------------------------------+-------------------------------
----+
Predicate Information:
----------------------
2 - access("EMPNO"=7788)
4.收集统计信息
----收集hr用户下employees表统计信息
SQL> exec dbms_stats.gather_table_stats('HR','EMPLOYEES',cascade=>true);
PL/SQL procedure successfully completed.
SQL> analyze table hr.employees compute statistics;
Table analyzed.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30130773/viewspace-2108487/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30130773/viewspace-2108487/