Oracle10046事件和10053事件实例演示

一、10046事件

sql_trace（或者10046跟踪事件）
sql_trace跟踪的内容由三个部分组成:执行语句时造成的等待事件(waits)、执行语句时产生的
性能统计数据，以及语句执行计划和绑定变量的信息。
再回话或者系统启动sql跟踪后，会话结束或者关闭sql跟踪前，会话（或系统中）所有运行的
语句性能统计数据都会记录到udump目录（user_dump_dest参数指定）
文件名格式<oracel_sid>ora.trc
从跟踪文件中，我们可以找到语句的执行计划

alter session set sql_trace=true;  --开启追踪
variable v_empno varchar2 ;
  select * from emp  where empno= :v_empno;
alter session set sql_trace=false;  --关闭追踪

2、获取会话进程的spid

SELECT DISTINCT p.SPID
  FROM v$process p, v$session s, v$mystat m
 WHERE p.ADDR = s.PADDR
           AND s.SID = m.SID
       and s.USERNAME='SCOTT';

看到spid是17488
3、获取追踪文件路径

 show parameter user_dump_dest

4、根据追踪文件路径找到追踪文件

跟踪文件内容如下

Trace file d:\software\oracle11\diag\rdbms\orcl\orcl\trace\orcl_ora_17488.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.2  
CPU                 : 12 - type 8664, 6 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:9229M/16239M, Ph+PgF:3906M/17263M 
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 40
Windows thread id: 17488, image: ORACLE.EXE (SHAD)


*** 2019-06-21 00:25:25.828
*** SESSION ID:(166.307) 2019-06-21 00:25:25.828
*** CLIENT ID:() 2019-06-21 00:25:25.828
*** SERVICE NAME:(SYS$USERS) 2019-06-21 00:25:25.828
*** MODULE NAME:(SQL*Plus) 2019-06-21 00:25:25.828
*** ACTION NAME:() 2019-06-21 00:25:25.828
 
=====================
PARSING IN CURSOR #2 len=32 dep=0 uid=84 oct=42 lid=84 tim=574306453827 hv=1569151342 ad='1f4bfe40' sqlid='4tk6t8tfsfqbf'
alter session set sql_trace=true
END OF STMT
EXEC #2:c=0,e=54,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=574306453824
=====================
PARSING IN CURSOR #4 len=52 dep=0 uid=84 oct=47 lid=84 tim=574306455340 hv=1029988163 ad='7ffc8a761088' sqlid='9babjv8yq8ru3'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE #4:c=0,e=30,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=574306455340
EXEC #4:c=0,e=140,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=574306455537

*** 2019-06-21 00:25:36.835
CLOSE #2:c=0,e=7,dep=0,type=0,tim=574317462168
CLOSE #4:c=0,e=25,dep=0,type=1,tim=574317462325
=====================
PARSING IN CURSOR #2 len=42 dep=0 uid=84 oct=3 lid=84 tim=574317462932 hv=1268895575 ad='7ffc898e73c8' sqlid='0m3zb095u3mur'
  select * from emp  where empno= :v_empno
END OF STMT
PARSE #2:c=0,e=561,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=574317462930
EXEC #2:c=0,e=690,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2949544139,tim=574317463857
FETCH #2:c=0,e=7,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2949544139,tim=574317463970
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=73196 op='TABLE ACCESS BY INDEX ROWID EMP (cr=0 pr=0 pw=0 time=0 us cost=1 size=33 card=1)'
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=73197 op='INDEX UNIQUE SCAN PK_EMP (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)'
=====================
PARSING IN CURSOR #4 len=52 dep=0 uid=84 oct=47 lid=84 tim=574317466192 hv=1029988163 ad='7ffc8a761088' sqlid='9babjv8yq8ru3'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE #4:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=574317466191
EXEC #4:c=0,e=179,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=574317466498

*** 2019-06-21 00:25:41.172
CLOSE #2:c=0,e=9,dep=0,type=0,tim=574321799071
CLOSE #4:c=0,e=9,dep=0,type=3,tim=574321799137
=====================
PARSING IN CURSOR #2 len=33 dep=0 uid=84 oct=42 lid=84 tim=574321799363 hv=525901419 ad='1f4bfe40' sqlid='aam2chsgpj7mb'
alter session set sql_trace=false
END OF STMT
PARSE #2:c=15625,e=205,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=574321799362
EXEC #2:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=574321799439

二、10053跟踪事件

optimizer_trace(10053跟踪事件)可以跟踪优化器生成语句执行计划的整个过程，跟踪内容
都会写入UDMP目录下的一个跟踪文件中命名方式与sql_trace一样。

ALTER SESSION SET EVENTS='10053 trace name context forever, level 1'；
 ALTER SESSION SET EVENTS='10053 trace name context forever, level 2';

Level 2：2级是1级的一个子集，它包含以下内容：
Column statistics
Single Access Paths
Join Costs
Table Joins Considered
Join Methods Considered (NL/MS/HA)
Level 1： 1级比2级更详细，它包含2级的所有内容，在加如下内容：
–解释sql执行计划

 explain plan for select * from emp where empno=:v_empno;

–关闭10053事件

ALTER SESSION SET EVENTS '10053 trace name context off';

2、查看会话spid

SELECT DISTINCT p.SPID
  FROM v$process p, v$session s, v$mystat m
 WHERE p.ADDR = s.PADDR
           AND s.SID = m.SID
       and s.USERNAME='SCOTT';

根据15008再到udmp路径下（user_dump_dest参数指定）查看追踪文件，部分内容如下

我们还可以设定trace 文件名称

  alter session set tracefile_identifier='10053事件';

设置标识的目的就是方便我们查找生成的trace文件。我们只需要在trace目录查找文件名里带有标识的文件即可。

我们再次重复上面步骤

 ALTER SESSION SET EVENTS='10053 trace name context forever, level 2';
explain plan for select * from emp where empno=:v_empno; 
ALTER SESSION SET EVENTS '10053 trace name context off';

然后查看跟踪文件

看到文件名变得更加清楚容易辨认。