ORACLE 10046 10053 EVENT

独行侠谁也不带

已于 2022-11-02 09:29:32 修改

阅读量553

点赞数

分类专栏： ORACLE运维文章标签： oracle 数据库 java

于 2022-11-01 17:30:45 首次发布

本文链接：https://blog.csdn.net/xielong8866/article/details/127633603

版权

本文详细介绍了如何利用Oracle的10046和10053事件来捕获和分析SQL语句的执行计划和优化器信息。10046事件关注语句执行过程，包括CPU消耗、等待事件和绑定变量信息，而10053事件则侧重于优化器的trace。通过设置不同级别的事件，可以获取不同层次的数据。文章提供了一套步骤，指导读者如何在当前会话和实例中启用和关闭这两个事件，并展示了查看trace文件的方法，帮助理解两者在跟踪和诊断上的差异。

摘要由CSDN通过智能技术生成

这篇文章介绍一下通过10046 10053事件抓取语句计划的具体方法

问：10046事件和10053事件有什么区别

答：10046事件只包含语句的进行过程，解析，取数据，进行。在这个过程的等待事件，cpu消耗，每一步的消耗时间

10053时间包括对优化器的trace

10046 10053事件中的level按照收集信息内容，可以分成4个级别：
Level 1：等同于SQL_TRACE 的功能
Level 4：在Level 1的基础上增加收集绑定变量的信息
Level 8：在Level 1 的基础上增加等待事件的信息
Level 12：等同于Level 4+Level 8, 即同时收集绑定变量信息和等待事件信息。

对当前session 使用10046事件
1.alter session set events '10046 trace name context forever, level 12'; --启动10046事件
2.执行相关事务
3.alter session set events ‘10046 trace name context off’; -- 关闭10046事件

对当前实例使用10046事件
1.alter session set events '10046 trace name context forever, level 12'; --启动10046事件
2.执行相关事务
3.alter session set events ‘10046 trace name context off’; -- 关闭10046事件

对当前session 使用10053事件
1.alter session set events '10053 trace name context forever, level 12'; --启动10053事件
2.执行相关事务
3.alter session set events ‘10053 trace name context off’; -- 关闭10053事件

对当前实例使用10053事件
1.alter session set events '10053 trace name context forever, level 12'; --启动10053事件
2.执行相关事务
3.alter session set events ‘10053 trace name context off’; -- 关闭10053事件

查看trace路径

set linesize 160
column "trace file name" for a120
select pr.value || '/' || i.instance_name || '_ora_' || to_char(ps.spid) ||
'.trc' as "trace file name"
from gv$session s, gv$process ps, gv$diag_info pr, v$instance i
where s.paddr = ps.addr
and s.inst_id=pr.inst_id
and s.inst_id=1
and pr.name = 'Diag Trace'
and s.sid = 120;

10046 trace和10053 trace的区别

开启10046 trace

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

进行测试语句

SQL> select object_id from xl.test;

 OBJECT_ID
----------
        20
        46
        28
        15
        29
         3
        25
        41
        54
        40
        26

关闭10046trace

SQL> alter session set events '10046 trace name context off';

Session altered.

查看当前trace文件位置

SQL> set linesize 160
column "trace file name" for a120
SQL> SQL> select pr.value || '/' || i.instance_name || '_ora_' || to_char(ps.spid) ||
'.trc' as "trace file name"
from gv$session s, gv$process ps, gv$diag_info pr, v$instance i
where s.paddr = ps.addr
and s.inst_id=pr.inst_id
and s.inst_id=1
and pr.name = 'Diag Trace'
and s.sid = 1;  2    3    4    5    6    7    8

trace file name
----------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_7938.trc

10046 trace内容

=====================
PARSING IN CURSOR #1 len=29 dep=0 uid=0 oct=3 lid=0 tim=1667319152802403 hv=2882163949 ad='89714cc0' sqlid='9af0k1ypwnp7d'
select object_id from xl.test
END OF STMT
PARSE #1:c=1000,e=686,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1357081020,tim=1667319152802402
EXEC #1:c=0,e=16,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1357081020,tim=1667319152802499
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1667319152802513
WAIT #1: nam='Disk file operations I/O' ela= 40 FileOperation=2 fileno=4 filetype=2 obj#=-1 tim=1667319152802590
FETCH #1:c=0,e=92,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=1357081020,tim=1667319152802618
WAIT #1: nam='SQL*Net message from client' ela= 133 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1667319152802772
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1667319152802795
FETCH #1:c=0,e=13,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1357081020,tim=1667319152802803
WAIT #1: nam='SQL*Net message from client' ela= 3967 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1667319152806780
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1667319152806843
FETCH #1:c=0,e=23,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1357081020,tim=1667319152806852
WAIT #1: nam='SQL*Net message from client' ela= 3850 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1667319152810722
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1667319152810788
FETCH #1:c=0,e=24,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1357081020,tim=1667319152810798
WAIT #1: nam='SQL*Net message from client' ela= 4080 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1667319152814898
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1667319152814954
FETCH #1:c=0,e=25,p=0,cr=1,cu=0,mis=0,r=3,dep=0,og=1,plh=1357081020,tim=1667319152814966
STAT #1 id=1 cnt=49 pid=0 pos=1 obj=73425 op='TABLE ACCESS FULL TEST (cr=7 pr=0 pw=0 time=0 us cost=3 size=147 card=49)'

*** 2022-11-02 00:12:42.618
WAIT #1: nam='SQL*Net message from client' ela= 9803971 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1667319162618984
CLOSE #1:c=0,e=13,dep=0,type=0,tim=1667319162619090
=====================
PARSING IN CURSOR #3 len=55 dep=0 uid=0 oct=42 lid=0 tim=1667319162619120 hv=2217940283 ad='0' sqlid='06nvwn223659v'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #3:c=0,e=7,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1667319162619119
EXEC #3:c=0,e=287,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1667319162619639

10053 trace中的计划部分

SQL> alter session set events '10053 trace name context forever, level 12';

Session altered.

进行语句

SQL> select object_id from xl.test;

 OBJECT_ID
----------
        20
        46
        28
        15
        29
         3
        25
        41
        54
        40
        26

关闭10053 trace

SQL> alter session set events '10053 trace name context off';

Session altered.

10053 trace内容

*******************************************
Peeked values of the binds in SQL statement
*******************************************

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TEST"."OBJECT_ID" "OBJECT_ID" FROM "XL"."TEST" "TEST"
kkoqbc: optimizing query block SEL$1 (#0)
        
        :
    call(in-use=10