按照信息收集的内容可以分为4个级别:
1、level 1等同于SQL_TRACE
2、level 4在level 1 的基础上增加了收集绑定变量的信息
3、level 8在level 1 的基础上增加了等待事件的信息
4、level 12等同于level4 + level 8同时收集绑定变量的信息和等待事件的信息。
因为level 12是level 4与level 8的整合,由于篇幅关系,只做level 12的实验。
10046事件level 12实验数据的准备:
SQL> conn scott/scott
Connected.
SQL>
Session altered.
SQL> variable x number; --定义两个变量
SQL> variable y varchar2;
SQL> exec :x:=20;
PL/SQL procedure successfully completed.
SQL> exec :y:='T';
PL/SQL procedure successfully completed.
SQL> select object_id,object_name from t where object_id=:x or object_name=:y;
OBJECT_ID OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------------------------
SQL> alter session set events '10046 trace name context off';
Session altered.
查看trace文件中的相关信息:
cat /u01/oracle/admin/ORCL/udump/orcl_ora_4235.trc
=====================
PARSING IN CURSOR #1 len=72 dep=0 uid=54 oct=3 lid=54 tim=1300735198568516 hv=1308460522 ad='3ddcec04'
select object_id,object_name from t where object_id=:x or object_name=:y
END OF STMT
PARSE #1:c=0,e=102,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1300735198568508
BINDS #1:
kkscoacd
Bind#0
Bind#1
EXEC #1:c=1000,e=225,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1300735198568832
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1300735198568876
--等待事件 (level 8)
FETCH #1:c=0,e=93,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=1300735198569009
WAIT #1: nam='SQL*Net message from client' ela= 225 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1300735198569291
--等待事件 (level 8)
WAIT #1: nam='SQL*Net message to client' ela= 6 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1300735198585989
FETCH #1:c=16997,e=16852,p=0,cr=707,cu=0,mis=0,r=2,dep=0,og=1,tim=1300735198586188
WAIT #1: nam='SQL*Net message from client' ela= 23029863 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1300735221616167
STAT #1 id=1 cnt=3 pid=0 pos=1 obj=59633 op='TABLE ACCESS FULL T (cr=711 pr=0 pw=0 time=86 us)'
======================================================================================================
10046事件的用途:
1、找到有问题的SQL。
2、通过跟踪方式对有问题的SQL进行分析,找到原因并为优化提供重要信息。
今天刚刚学习了Oracle的10046事件的相关知识,与大家做个分享。
10046事件
按照信息收集的内容可以分为4个级别:
1、level 1等同于SQL_TRACE
2、level 4在level 1 的基础上增加了收集绑定变量的信息
3、level 8在level 1 的基础上增加了等待事件的信息
4、level 12等同于level4 + level 8同时收集绑定变量的信息和等待事件的信息。
因为level 12是level 4与level 8的整合,由于篇幅关系,只做level 12的实验。
10046事件level 12实验数据的准备:
SQL> conn scott/scott
Connected.
SQL>
Session altered.
SQL> variable x number; --定义两个变量
SQL> variable y varchar2;
SQL> exec :x:=20;
PL/SQL procedure successfully completed.
SQL> exec :y:='T';
PL/SQL procedure successfully completed.
SQL> select object_id,object_name from t where object_id=:x or object_name=:y;
OBJECT_ID OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------------------------
SQL> alter session set events '10046 trace name context off';
Session altered.
查看trace文件中的相关信息:
cat /u01/oracle/admin/ORCL/udump/orcl_ora_4235.trc
=====================
PARSING IN CURSOR #1 len=72 dep=0 uid=54 oct=3 lid=54 tim=1300735198568516 hv=1308460522 ad='3ddcec04'
select object_id,object_name from t where object_id=:x or object_name=:y
END OF STMT
PARSE #1:c=0,e=102,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1300735198568508
BINDS #1:
kkscoacd
Bind#0
Bind#1
EXEC #1:c=1000,e=225,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1300735198568832
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1300735198568876
--等待事件 (level 8)
FETCH #1:c=0,e=93,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=1300735198569009
WAIT #1: nam='SQL*Net message from client' ela= 225 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1300735198569291
--等待事件 (level 8)
WAIT #1: nam='SQL*Net message to client' ela= 6 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1300735198585989
FETCH #1:c=16997,e=16852,p=0,cr=707,cu=0,mis=0,r=2,dep=0,og=1,tim=1300735198586188
WAIT #1: nam='SQL*Net message from client' ela= 23029863 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1300735221616167
STAT #1 id=1 cnt=3 pid=0 pos=1 obj=59633 op='TABLE ACCESS FULL T (cr=711 pr=0 pw=0 time=86 us)'
======================================================================================================
10046事件的用途:
1、找到有问题的SQL。
2、通过跟踪方式对有问题的SQL进行分析,找到原因并为优化提供重要信息。
执行以下命令关闭10046事件:
SQL>
Session altered.