Oracle10046事件和10053事件实例演示

野性酷女 2022-01-12 07:45 187阅读 0赞

###  ###

*   *  一、10046事件
     *  二、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;  --关闭追踪

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70]

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';

![在这里插入图片描述][20190621144818747.png]  
看到spid是17488  
3、获取追踪文件路径

show parameter user_dump_dest

![在这里插入图片描述][20190621144837236.png]  
4、根据追踪文件路径找到追踪文件  
![在这里插入图片描述][20190621144849511.png]  
跟踪文件内容如下

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';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 1]

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';

![在这里插入图片描述][20190621145126240.png]

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 2]  
我们还可以设定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';

然后查看跟踪文件  
![在这里插入图片描述][20190621145212632.png]  
看到文件名变得更加清楚容易辨认。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70]: /images/20220109/37e85fe1b39f4d2e90c43bb7b2616a31.png
[20190621144818747.png]: /images/20220109/cb8bdedcf7744656a5d3e7b6c15c7190.png
[20190621144837236.png]: /images/20220109/5525eb36b3054627b2dbb6776de5ef95.png
[20190621144849511.png]: /images/20220109/cb5e371af09648c789c70b8165fcdd55.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 1]: /images/20220109/4ec89a478d844436ac024db799e250ed.png
[20190621145126240.png]: /images/20220109/196b143feef54f8b9fe0dc8be809abfc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 2]: /images/20220109/cde90cd6d689430d988858a441638691.png
[20190621145212632.png]: /images/20220109/99946d6d9e504f2ca6a96f7c00464398.png