Oracle 有关10053事件,你知道这两个知识点么?

来自:bisal的个人杂货铺,作者:bisal

在Oracle中,10053事件可以创建优化器的trace,因此也叫优化器跟踪文件(Optimizer trace file),这个文件,告诉你Oracle为什么选择这种,而不是另一种执行计划,相应的成本值等一系列信息,让你更加了解CBO的选择。众所周知,10053事件的trace文件可以通过alter session set event来创建,但前提是必须真正执行这条SQL,题外话是,这也是为什么10053,能得到这条语句,对应的真实执行计划。


1. 关于10053的相关知识,可以参考《探索索引的奥秘 - 10053事件》。

2. 有哪些方法可以得到SQL真实执行计划,可以参考《一个执行计划异常变更的案例 - 外传之查询执行计划的几种方法》。


如下步骤,是10053常见的一种创建过程,tracefile_identifier是为了标识10053的跟踪文件名,说白了,就是USER_DUMP_DEST变量指定的路径中好找。要想退出100053,一种是示例中操作的使用alter session ... context off,另一种是可以直接退出会话,

alter session set tracefile_identifier='MYTEST_10053';
alter session set events '10053 trace name context forever';
select /* hard parse comment */ * from emp where ename = 'SCOTT';
alter session set events '10053 trace name context off';


问题

是否有其他方法,可以创建10053的跟踪文件?


11g丰富了诊断事件的架构,提供了很多种debug输出的级别,可以控制SQL的编译,oradebug命令可以展示这些级别,

SQL> oradebug doc component SQL_Compiler

 SQL_Compiler           SQL Compiler
   SQL_Parser           SQL Parser (qcs)
   SQL_Semantic         SQL Semantic Analysis (kkm)
   SQL_Optimizer         SQL Optimizer
     SQL_Transform         SQL Transformation (kkq, vop, nso)
 SQL_MVRW         SQL Materialized View Rewrite
 SQL_VMerge         SQL View Merging (kkqvm)
 SQL_Virtual         SQL Virtual Column (qksvc, kkfi)
     SQL_APA           SQL Access Path Analysis (apa)
     SQL_Costing         SQL Cost-based Analysis (kko, kke)
 SQL_Parallel_Optimization SQL Parallel Optimization (kkopq)
   SQL_Code_Generator         SQL Code Generator (qka, qkn, qke, kkfd, qkx)
     SQL_Parallel_Compilation SQL Parallel Compilation (kkfd)
     SQL_Expression_Analysis  SQL Expression Analysis (qke)
     SQL_Plan_Management      SQL Plan Managment (kkopm)
   MPGE           MPGE (qksctx)
   ADS            ADS (kkoads)


如上10053创建过程,可以改为,

alter session set tracefile_identifier='MYTEST_SQL_Compiler_TRACE';
alter session set events 'trace [SQL_Compiler.*]';
select /* hard parse comment */ * from emp where ename = 'SCOTT';
alter session set events 'trace [SQL_Compiler.*] off';


无论哪种方法,最大的缺点就是,依赖于两个前提:

1. 你必须执行SQL文本。

2. 执行过硬解析,即经历了真实SQL编译过程。


问题

是否可以不用执行语句,得到一个已经执行并且还在游标缓存中的SQL语句10053跟踪文件?


之所以这么说,因为有些场景,不具备以上操作的条件,例如几页的SQL语句,执行起来困难,或者你没有用户密码,但又需要10053。


11g下,DBMS_SQLDIAG包有个存储过程DUMP_TRACE,可以实现此功能。原理是系统会自动触发一次语句的硬解析以创建跟踪文件。

The procedure will automatically trigger a hard parse of the statement to generate the trace.


但是,DUMP_TRACE并未写入DBMS_SQLDIAG包的官方文档中。Greg Rahn写过一篇文章,并且在$ORACLE_HOME/rdbms/admin/dbmsdiag.sql有dump_trace的介绍和定义,

$ORACLE_HOME/rdbms/admin/dbmsdiag.sql

-------------------------------- dump_trace ---------------------------------
-- NAME: 
--     dump_trace - Dump Optimizer Trace
--
-- DESCRIPTION:
--     This procedure dumps the optimizer or compiler trace for a give SQL 
--     statement identified by a SQL ID and an optional child number. 
--
-- PARAMETERS:
--     p_sql_id          (IN)  -  identifier of the statement in the cursor 
--                                cache
--     p_child_number    (IN)  -  child number
--     p_component       (IN)  -  component name
--                                Valid values are Optimizer and Compiler
--                                The default is Optimizer
--     p_file_id         (IN)  -  file identifier
------------------------------------------------------------------------------
PROCEDURE dump_trace(
             p_sql_id         IN varchar2,
             p_child_number   IN number   DEFAULT 0,
             p_component      IN varchar2 DEFAULT 'Optimizer',
             p_file_id        IN varchar2 DEFAULT null);


从上面介绍的debug级别以及dump_trace定义可知,p_component可以接收SQL_Compiler或者SQL_Optimizer两个事件,p_file_id则是和tracefile_identifier相同,表示trace文件标识符,用于快速定位。


上面得到10053的四个步骤,仅需要执行这一个存储过程,即可完成,

SQL> begin
 2    dbms_sqldiag.dump_trace(p_sql_id=>'6yf5xywktqsa7',
 3                            p_child_number=>0,
 4                            p_component=>'Compiler',
 5                            p_file_id=>'MY_TRACE_DUMP');
 6  end;
 7  /

PL/SQL procedure successfully completed.


使用这种方法,生成的10053跟踪文件,注释部分会增加/* SQL Analyze(1443,0) */,表示是用DBMS_SQLDIAG.DUMP_TRACE创建的,而且是由Oracle,自动做了一次硬解析,

Enabling tracing for cur#=9 sqlid=as9bkjstppk0a recursive
Parsing cur#=9 sqlid=as9bkjstppk0a len=91 
sql=/* SQL Analyze(1443,0) */ select /* hard parse comment */ * from emp where ename = 'SCOTT'
End parsing of cur#=9 sqlid=as9bkjstppk0a
Semantic Analysis cur#=9 sqlid=as9bkjstppk0a
OPTIMIZER INFORMATION

******************************************
----- Current SQL Statement for this session (sql_id=as9bkjstppk0a) -----
/* SQL Analyze(1443,0) */ select /* hard parse comment */ * from emp where ename = 'SCOTT'
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
 object      line  object
 handle    number  name
0x16fd3a368       145  package body SYS.DBMS_SQLTUNE_INTERNAL
0x16fd3a368     12085  package body SYS.DBMS_SQLTUNE_INTERNAL
0x18e7fead8      1229  package body SYS.DBMS_SQLDIAG
0x16fdbddd0         1  anonymous block
*******************************************


需要注意的是,每次存储过程的执行,都会触发一次硬解析操作,因此频繁的执行,对于系统的影响程度,就需要你来了解和控制了。


参考文献:

1. 《Capturing 10053 trace files continued》

https://blogs.oracle.com/optimizer/capturing-10053-trace-files-continued

2. 《Creating Optimizer Trace Files》

http://structureddata.org/2011/08/18/creating-optimizer-trace-files/

推荐↓↓↓
数据库开发
上一篇:MySQL 从零开始:02 MySQL安装 下一篇:深入解析:你听说过Oracle数据库的更新重启动吗?