SQL调优和诊断工具之Optimizer Trace（10053 Event）介绍

alter session set tracefile_identifier='10053';
alter session set max_dump_file_size = UNLIMITED;
alter session set timed_statistics=true;
alter session set events '10053 trace name context forever, level 1';
<分析对象的SQL文>
alter session set events '10053 trace name context off';

SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug event 10053 trace name context forever, level 1
<分析对象的SQL文>
SQL> oradebug event 10053 trace name context off

■在执行SQL文前刷新共享池
如：
SQL> alter system flush shared_pool;
■针对SQL文追加一些注释，使其变成不同的游标。
如：
SQL文：select * from dual;
追加注释变为：
select /* for 10053*/ * from dual;

例：
SQL> show parameter user_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /home2/ora10205/app/oracle/admin/ora10205/udump

例如：
SQL> conn /as sysdba
Connected.
SQL> show parameter diagnostic_dest


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /u01/app/oracle

alter session set tracefile_identifier='10053_10046';
alter session set max_dump_file_size = UNLIMITED;
alter session set timed_statistics=true;
alter session set events '10053 trace name context forever, level 1';
alter session set events '10046 trace name context forever, level 12';
<<分析对象的SQL文>>
alter session set events '10046 trace name context off';
alter session set events '10053 trace name context off';
exit

grep -v PARS input10053TraceName.trc |grep -v 'END OF'| grep -v FETCH |grep -v EXEC |grep -v WAIT |grep -v 'STAT ' |grep -v '====' |grep -v 'BINDS #' |grep -v 'value=' |grep -v oacflg |grep -v oacdef |grep -v 'bfp=' > OutputFileName.txt

SQL> alter session set tracefile_identifier='10053';
SQL> alter session set max_dump_file_size = unlimited;
---启用Trace
SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:<SQL_ID>]';
SQL> --<<分析对象的SQL文>> --
---停止Trace
SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*] off';

SQL> alter session set tracefile_identifier='10053';
SQL> alter session set max_dump_file_size = unlimited;
---启用Trace
SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:<SQL_ID1>| <SQL_ID2>]';
SQL> --<<分析对象的SQL文>> --
---停止Trace
SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*] off';

例如：
SQL> alter system set events 'trace[sql_optimizer.*] [sql: gcaxax8tgmjvt]';

SQL> alter system set events 'trace[sql_optimizer.*] [sql: gcaxax8tgmjvt | g2mjrzx0w5ysv ]';

--1.找到要跟踪SQL的SQL ID
SQL> select sql_id, child_number, sql_text from v$sql
where sql_text like '%Part of SQL%';


--2.确认当前的执行计划
SQL> set pages 0
SQL> select * from
table(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>'&sqlid', cursor_child_no =>0,format=>'ALL'));


--3.生成Optimizer Trace（10053）
SQL> BEGIN
DBMS_SQLDIAG.DUMP_TRACE(
P_SQL_ID => '&P_SQL_ID',
P_CHILD_NUMBER => 0,
P_COMPONENT => 'Compiler',
P_FILE_ID => '10053_TRACE');
END;
/

$ more $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);

P_SQL_ID : 指定SQL_ID
P_CHILD_NUMBER : SQL的子游标Number，默认0
P_COMPONENT : Trace的种类，默认为'Optimzer'
'Optimzer'-Optimzer Trace 相当于debug 的SQL_Optimizer级别
'Compiler'-更详细的Trace，相当于debug 的SQL_Compiler级别
P_FILE_ID : 输出文件的标识字符串，用于查找文件，默认NULL

SQL> oradebug doc component SQL_Compiler
SQL_Compiler SQL Compiler ((null))
SQL_Parser SQL Parser (qcs)
SQL_Semantic SQL Semantic Analysis (kkm)
SQL_Optimizer SQL Optimizer ((null))
SQL_Transform SQL Transformation (kkq, vop, nso)
SQL_MVRW SQL Materialized View Rewrite ((null))
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_Plan_Management SQL Plan Managment (kkopm)
SQL_Plan_Directive SQL Plan Directive (qosd)
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)
MPGE MPGE (qksctx)
ADS ADS (kkoads)