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

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 有关10053事件,你知道这两个知识点么?

预计阅读时间:11分钟

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

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

  1. 关于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

  1. 《Creating Optimizer Trace Files》

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

如果您觉得本文有帮助,欢迎关注转发:bisal的个人杂货铺

有关10053事件,你知道这两个知识点么?
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 有关10053事件,你知道这两个知识点么?


 上一篇
MySql和Sql Server语法和关键字区别 MySql和Sql Server语法和关键字区别
MySQL支持enum,和set类型,SQL Server不支持 MySQL不支持nchar,nvarchar,ntext类型  MySQL的递增语句是AUTO_INCREMENT,而MS SQL是identity(1,1) 
下一篇 
PL,SQL中SQL语句10053创建方法 PL,SQL中SQL语句10053创建方法
预计阅读时间:5分钟 《》介绍了使用alter session … 10053 trace和alter session … trace [SQL_Compiler.*],以及DBMS_SQLDIAG包的存储过程DUMP_TRACE,三种生成