怎样收集10046跟踪文件来诊断性能问题

收集10046跟踪文件
10046事件是一种标准的方法用来对oracle会话收集扩展的sql_trace信息,对于查询性能问题来说通常要求记录查询的等待和绑定变量信息.这可以使用级别为12的10046跟踪来完成.下面的例子说明了在各种情况下设置10046事件.
跟踪文件的位置
在oracle11g及以上版本中引入了新的诊断架构,跟踪和核心文件存储的位置由diagnostic_dest初始化参数来控制.可以使用下面的命令来显示:
 show parameter diagnostic_dest


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /u01/app/oracle
注意:在有些例子中可能设置了'tracefile_identifier'来帮助找到输出的跟踪文件
会话跟踪
可以在用户会话执行sql语句之前对会话启用跟踪,在会话级别收集10046跟踪
sys@JINGYONG> alter session set timed_statistics=true;


会话已更改。


sys@JINGYONG> alter session set statistics_level=all;


会话已更改。


sys@JINGYONG> alter session set max_dump_file_size=unlimited;


会话已更改。


sys@JINGYONG> alter session set events '10046 trace name context forever,level 12';


会话已更改。


sys@JINGYONG> select * from dual;


D
-
X


sys@JINGYONG>exit
如果会话没有退出可以执行以下语句来禁用10046跟踪
sys@JINGYONG> alter session set events '10046 trace name context off';


会话已更改。








sys@JINGYONG> select value from v$diag_info where name='Default Trace File';  
VALUE
----------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2572_10046.trc
注意:如果会话不是彻底的关闭和禁用跟踪那么重要的跟踪信息可能会从跟踪文件中丢失.
注意:这里statistics_level=all因此它会在这种情况下收集一定程度的统计信息.这个参数有三个参数值all,typical,basic.为了诊断性能问题会要求获得一定程度的统计信息.设置为all可能是不必要的但可以使用typical以此来获得全面的诊断信息.
跟踪一个已经启动的进程
如果要跟踪一个已经存在的会话可以使用oradebug来连接到会话初始化10046跟踪
1.通过某种方法来确定要被跟踪的会话
例如在sql*plus中启动一个会话然后找到这个会话的操作系统进行id(spid):
select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
/
SPID是操作系统进程标识符
PID是oracle进程标识符
如果你不知道要不得被跟踪会话的sid可以使用类似于下面的查询来帮助你识别这个会话:
column line format a79
set heading off
select 'ospid: ' || p.spid ||' pid: '||p.pid || ' # ''' ||s.sid||','||s.serial#||''' '||
s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
from v$session s , v$process p
where p.addr = s.paddr
and s.username <> ' ';
执行结果如下:
sys@JINGYONG> column line format a79
sys@JINGYONG> set heading off
sys@JINGYONG> select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||'''
 '||
  2    s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
  3  from v$session s , v$process p
  4  where p.addr = s.paddr
  5  and s.username <> ' ';


ospid: 2529 # '30,32' Administrator WORKGROUP\JINGYONG SYS sqlplus.exe
注意:在oracle12c中对于多线程进程,在v$process视图中加入了新的列stid来找到特定的线程.因为oracle会组合多个进程到一个单独的ospid中.为了找到这个特定的线程使用下面的语法:
oradebug setospid
2.当确定进程的操作系统进程ID后然后可以使用下面的语句来初始化跟踪:
假设要被跟踪进程的操作系统进程ID是2529
SQL>connect / as sysdba
sys@JINGYONG> oradebug setospid 2529
Oracle pid: 21, Unix process pid: 2529, image: oracle@jingyong
sys@JINGYONG> oradebug unlimit
已处理的语句
sys@JINGYONG> oradebug event 10046 trace name context forever,level 12
已处理的语句
sys@JINGYONG> select * from dual;


X


sys@JINGYONG> oradebug event 10046 trace name context off
已处理的语句
sys@JINGYONG> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2529.trc
注意:连接到一个会话也可以使用oradebug setorapid
在这种情况下PID(oracle进程标识符)将被使用(而不是使用SPID):
sys@JINGYONG> oradebug setorapid 21
Oracle pid: 21, Unix process pid: 2529, image: oracle@jingyong
从显示的信息可知道使用oradebug setorapid 21与oradebug set0spid 2529是一样的
sys@JINGYONG> oradebug unlimit
已处理的语句
sys@JINGYONG> oradebug event 10046 trace name context forever,level 12
已处理的语句
sys@JINGYONG> select sysdate from dual;


11-11月-13


sys@JINGYONG> oradebug event 10046 trace name context off
已处理的语句
sys@JINGYONG> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2529.trc
注意:在oracle12c中对于多线程进程,在v$process视图中加入了新的列stid来找到特定的线程.因为oracle会组合多个进程到一个单独的ospid中.为了找到这个特定的线程使用下面的语法:
oradebug setospid
跟踪产生的跟踪文件名称类似于_.trc
实例级别的跟踪
注意:在实例级别启用跟踪因为每一个会话都会被跟踪这样对性能是有影响的在设置这个跟踪参数后产生的每一个会话都会被跟踪断开的会话将不会被跟踪设置系统级别的10046跟踪是用于当出现了一个问题会话但不能提前识别这个会话的情况下.在这种情况下跟踪可以被短时间地启用,这个问题可能会记录到跟踪文件中然后禁用跟踪在生成的跟踪文件中找到这个问题的原因
启用系统级别的10046跟踪:
alter system set events '10046 trace name context forever,level 12';
对所有会话禁有系统级别的10046跟踪:
alter system set events '10046 trace name context off';
初始化参数的设置:
当实例重新启动后对每一个会话启用10046跟踪.
event="10046 trace name context forever,level 12"
要禁用实例级别的10046跟踪可以删除这个初始化参数然后重启实例或者使用alter system语句
alter system set events '10046 trace name context off';
编写登录触发器
在有些情况下可能要跟踪特定用户的会话活动在这种情况下可以编写一个登录触发器来实现例如:
CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER like '&USERNAME')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END set_trace;
/
注意:为了能跟踪会话用户执行触发器需要显式的被授予'alter session'权限:
grant alter session to username;
使用SQLT来收集跟踪信息
什么是SQLTXPLAIN(SQLT)
SQLTXPLAIN也叫作SQLT,它是由专业的oracle服务技术中心提供了一个工具.SQLT输入一个SQL语句后它会输出一组诊断文件.这些诊断文件会被用来诊断性能低下的sql语句.SQLT连接到数据库并收集执行,基于成本优化的统计信息,方案对象元数据,性能统计,配置参数和类似影响SQL性能的元素.
使用SQLTXPLAIN的Xecute选项可以生成10046跟踪作为SQLT输出的一部分.
使用dbms_monitor包来进行跟踪
dbms_monitor是一个新的跟踪包.跟踪基于特定的客户端标识符或者服务名,模块名和操作名的组合形式来启用诊断和工作负载管理.在有些情况下可能会生成多个跟踪文件(例如对于一个模块启用服务级别的跟踪)使用新的trcsess工具来扫描所有的跟踪文件并将它们合成一个跟踪文件.在合并这一组跟踪文件后可以使用标准跟踪文件分析方法进行分析
查看启用的跟踪
可以查询dba_enabled_traces来检测什么跟踪被启用了.
例如:
sys@JINGYONG>select trace_type, primary_id, QUALIFIER_ID1, waits, binds 
             from DBA_ENABLED_TRACES;


TRACE_TYPE                   PRIMARY_ID  QUALIFIER_ID1           WAITS        BINDS
---------------------- ---------------   ------------------      --------    -------
SERVICE_MODULE         SYS$USERS        SQL*Plus                 TRUE        FALSE
CLIENT_ID              HUGO                                      TRUE        FALSE
SERVICE                v101_DGB                                  TRUE        FALSE
在这个数据库中已经启用了三个不同的跟踪状态
1.第一行记录显示将会对在SQL*Plus中执行的所有sql语句进行跟踪
2.第二行记录显示将会对带有客户端标识符"HUGO'的所有会话进行跟踪
3.第三行记录显示将会对使用服务"v101_DGB'连接到数据库的所有程序进行跟踪
session_trace_enable函数
可以使用session_trace_enable过程来对本地实例的一个指定的数据库会话启用sql跟踪.
语法如下:
启用sql跟踪
dbms_monitor.session_trace_enable(session_id => x, serial_num => y,
waits=>(TRUE|FALSE),binds=>(TRUE|FALSE) );
禁止sql跟踪
dbms_monitor.session_trace_disable(session_id => x, serial_num => y);
其中waits的缺省值是true,binds的缺省值是false.
可以从v$session视图中查询会话id和序列号
SQL> select serial#, sid , username from v$session; 


SERIAL#             SID  USERNAME
-------           -----  --------------
 20                 21   SYS
 
然后可以使用下面的命令来对指定的会话启用跟踪
SQL> execute dbms_monitor.session_trace_enable(21,20);
跟踪状态在数据库重启后就会被删除可以查询dba_enabled_traces视图看到没有记录
sys@JINGYONG> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2529.trc
sys@JINGYONG> select trace_type,primary_id,qualifier_id1,waits,binds
  2  from dba_enabled_traces;


未选定行
当会话断开或者使用下面的命令可以禁止跟踪
SQL> execute dbms_monitor.session_trace_disable(21,20);
client_id_trace_enable函数
在多层架构环境中,一个请求从一个终端客户端通过中间层分发到不同的数据库会话.这意味着终端客户端与数据库会话的联系不是静态的.在oracle10g之前没有方法可以对一个客户端跨不同数据库会话进行跟踪.端到端的跟踪可以通过一个新的属性client_identifier来标识它是唯一标识一个特定的终端客户端.这个客户端标识符对应于v$session视图中的client_identifier列.通过系统上下文也可以查看.
语法如下:
启用跟踪
execute dbms_monitor.client_id_trace_enable ( client_id =>'client x',
waits => (TRUE|FALSE), binds => (TRUE|FALSE) );
禁止跟踪
execute dbms_monitor.client_id_trace_disable ( client_id =>'client x');
其中waits的缺省值是true,binds的缺省值是false.
例如:
可以使用dbms_session.set_identifier函数来设置client_identifier
sys@JINGYONG> exec dbms_session.set_identifier('JY');


PL/SQL 过程已成功完成。


sys@JINGYONG> select sys_context('USERENV','CLIENT_IDENTIFIER') client_id from dual;


JY




sys@JINGYONG> select client_identifier client_id from v$session where sid=30;


JY


sys@JINGYONG> exec dbms_monitor.client_id_trace_enable('JY');


PL/SQL 过程已成功完成。
使用查询来检查跟踪是否已经启用
sys@JINGYONG> select primary_id,qualifier_id1,waits,binds
  2  from dba_enabled_traces where trace_type='CLIENT_ID';
PRIMARY_ID         QUALIFIER_ID1         WAITS    BINDS
----------------   --------------        -------- --------
JY                                       TRUE     FALSE
这个跟踪在数据库重启之后还是有效的你得调用函数来禁用.
sys@JINGYONG> exec dbms_monitor.client_id_trace_disable('JY');


PL/SQL 过程已成功完成。
检查生成的跟文件
Trace file /u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2529.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db
System name: Linux
Node name: jingyong
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine: i686
Instance name: jingyong
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 2529, image: oracle@jingyong




*** 2013-11-11 11:31:56.737
*** SESSION ID:(30.32) 2013-11-11 11:31:56.737
*** CLIENT ID:() 2013-11-11 11:31:56.737
*** SERVICE NAME:(jingyong) 2013-11-11 11:31:56.737
*** MODULE NAME:(sqlplus.exe) 2013-11-11 11:31:56.737
*** ACTION NAME:() 2013-11-11 11:31:56.737


PARSING IN CURSOR #8 len=96 dep=0 uid=0 oct=3 lid=0 tim=1384150635839986 hv=3018843459 ad='275fa5ec' sqlid='3gg23wktyzta3'
select primary_id,qualifier_id1,waits,binds
from dba_enabled_traces where trace_type='CLIENT_ID'
END OF STMT
在启用跟踪后执行的语句被记录到了跟踪文件中.
sys@JINGYONG> select primary_id,qualifier_id1,waits,binds
2 from dba_enabled_traces where trace_type='CLIENT_ID';
未选定行
当你使用MTS时有时将会生成多个跟踪文件,不同的共享服务器进程能执行sql语句这就将会生成多个跟踪文件.对于RAC
环境也是一样.
serv_mod_act_trace_enable函数
端到端跟踪对于使用MODULE,ACTION,SERVICES标识的应用程序能够进行有效地管理和计算其工作量.service名,module和
action名提供了一种方法来识别一个应用程序中重要的事务.你可以使用serv_act_trace_enable过程来对由一组service,module和action名指定的全局会话启用sql跟踪,除非指定了特定的实例名.对于一个会话的service名,module名与v$session视图中的service_name和module列相对应.
语句如下:
启用跟踪
execute dbms_monitor.serv_mod_act_trace_enable('Service S', 'Module M', 'Action A',
waits => (TRUE|FALSE), binds => (TRUE|FALSE), instance_name => 'ORCL' );
禁止跟踪
execute dbms_monitor.serv_mod_act_trace_disable('Service S', 'Module M', 'Action A');
其中waits的缺省值是true,binds的缺省值是false,instance_name的缺省值是null.
例如想要对在数据库服务器使用SQL*Plus执行的所有sql语句进行跟踪可以执行以下命令:
sys@JINGYONG> select module,service_name from v$session where sid=25;
MODULE                                      SERVICE_NAME
-----------------------------               ---------------------
sqlplus@jingyong (TNS V1-V3)                SYS$USERS


sys@JINGYONG> exec dbms_monitor.serv_mod_act_trace_enable('SYS$USERS','sqlplus@j
ingyong (TNS V1-V3)');


PL/SQL 过程已成功完成。


sys@JINGYONG> select primary_id,qualifier_id1,waits,binds
  2  from dba_enabled_traces
  3  where trace_type='SERVICE_MODULE';
PRIMARY_ID       QUALIFIER_ID1                WAITS    BINDS
---------------  -------------------          -------- --------
SYS$USERS        sqlplus@jingyong (TNS V1-V3) TRUE     FALSE


启用跟踪后我们执行一个测试语句
SQL> select 'x' from dual;


'
-
x
检查生成的跟踪文件名
SQL> select * from v$diag_info where name='Default Trace File';


   INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
         1 Default Trace File
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_4411.trc
查看跟踪内容如下
trace file /u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_4411.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db
System name: Linux
Node name: jingyong
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine: i686
Instance name: jingyong
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 4411, image: oracle@jingyong (TNS V1-V3)




*** 2013-11-11 14:34:00.971
*** SESSION ID:(25.412) 2013-11-11 14:34:00.972
*** CLIENT ID:() 2013-11-11 14:34:00.972
*** SERVICE NAME:(SYS$USERS) 2013-11-11 14:34:00.972
*** MODULE NAME:(sqlplus@jingyong (TNS V1-V3)) 2013-11-11 14:34:00.972
*** ACTION NAME:() 2013-11-11 14:34:00.972
 
WAIT #1: nam='SQL*Net message from client' ela= 152965072 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1384151640937525
CLOSE #1:c=1000,e=521,dep=0,type=0,tim=1384151640973430
=====================
PARSING IN CURSOR #1 len=20 dep=0 uid=0 oct=3 lid=0 tim=1384151640977682 hv=2740543121 ad='275fa9e4' sqlid='04vfkrajpkrnj'
select 'x' from dual
我们执行的测试语句被记录了在跟踪文件中.
sys@JINGYONG> exec dbms_monitor.serv_mod_act_trace_disable('SYS$USERS','sqlplus@
jingyong (TNS V1-V3)');


PL/SQL 过程已成功完成。


sys@JINGYONG> select primary_id,qualifier_id1,waits,binds
  2  from dba_enabled_traces
  3  where trace_type='SERVICE_MODULE';


未选定行
使用trcsess来合并跟踪文件
从某些跟踪操作中会得到多个跟踪文件.在oracle10g之前的版本中你得手动将这些跟踪文件合并到一起.现在可以使用trcsess工具来帮你合并这些跟踪文件.
语句如下:
trcsess [output=] [session=] [clientid=] [service=] [action=] [module=] 
output= output destination default being standard output.
 session= session to be traced.
 Session id is a combination of session Index & session serial number e.g. 8.13.
clientid= clientid to be traced.
service= service to be traced.
action= action to be traced.
module= module to be traced.
Space separated list of trace files with wild card '*' suppor
ted.
[oracle@jingyong trace]$ trcsess output=jingyong_ora_88888888.trc service=jingyong jingyong_ora_2529.trc jingyong_ora_4411.trc
[oracle@jingyong trace]$ ls -lrt jingyong_ora_88888888.trc
-rw-r--r-- 1 oracle oinstall 16219 Nov 11 14:59 jingyong_ora_88888888.trc
dbms_application_info
可以在过程开始一个事务之前使用dbms_application_info.set*过程来注册一个事务名/客户端信息/模块名为以后检查性能来使用.你应该对以后可能消耗你最多系统资源的活动事务进行指定.
dbms_application_info包有以下过程
SET_CLIENT_INFO ( client_info IN VARCHAR2 );
SET_ACTION ( action_name IN VARCHAR2 );
SET_MODULE ( module_name IN VARCHAR2, action_name IN VARCHAR2 );
例如
sys@JINGYONG> create table emp as select * from scott.emp where 1=0;


表已创建。


sys@JINGYONG> exec dbms_application_info.set_module(module_name=>'add_emp',actio
n_name=>'insert into emp');


PL/SQL 过程已成功完成。


sys@JINGYONG> insert into emp select * from scott.emp;


已创建14行。


sys@JINGYONG> commit;


提交完成。


sys@JINGYONG> exec dbms_application_info.set_module(null,null);


PL/SQL 过程已成功完成。
下面查询v$sqlarea视图使用module和action列进行查询
sys@JINGYONG> select sql_text from v$sqlarea where module='add_emp';


insert into emp select * from scott.emp


sys@JINGYONG> select sql_text from v$sqlarea where action='insert into emp';


insert into emp select * from scott.emp


dbms_session包：只能跟踪当前会话，不能指定会话。
跟踪当前会话：
SQL> exec dbms_session.set_sql_trace(true);
SQL> 执行sql
SQL> exec dbms_session.set_sql_trace(false);
dbms_session.set_sql_trace相当于alter session set sql_trace，从生成的trace文件可以明确地看alter session set sql_trace语句。使用dbms_session.session_trace_enable过程，不仅可以看到等待事件信息还可以看到绑定变量信息，相当于alter session set events '10046 trace name context forever, level 12';语句，从生成的trace文件可以确认。
SQL> exec dbms_session.session_trace_enable(waits=>true,binds=>true);
SQL> 执行sql
SQL> exec dbms_session.session_trace_enable();  
dbms_support包：不应该使用这种方法，非官方支持。
系统默认没有安装这个包，可以手动执行$ORACLE_HOME/rdbms/admin/bmssupp.sql脚本来创建该包。
SQL> desc dbms_support
FUNCTION MYSID RETURNS NUMBER
FUNCTION PACKAGE_VERSION RETURNS VARCHAR2
PROCEDURE START_TRACE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 WAITS                          BOOLEAN                 IN     DEFAULT
 BINDS                          BOOLEAN                 IN     DEFAULT
PROCEDURE START_TRACE_IN_SESSION
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SID                            NUMBER                  IN
 SERIAL                         NUMBER                  IN
 WAITS                          BOOLEAN                 IN     DEFAULT
 BINDS                          BOOLEAN                 IN     DEFAULT
PROCEDURE STOP_TRACE
PROCEDURE STOP_TRACE_IN_SESSION
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SID                            NUMBER                  IN
 SERIAL                         NUMBER                  IN
SQL> select dbms_support.package_version from dual;
PACKAGE_VERSION
--------------------------------------------------------------------------------
DBMS_SUPPORT Version 1.0 (17-Aug-1998) - Requires Oracle 7.2 - 8.0.5
SQL> select dbms_support.mysid from dual;
     MYSID
----------
       292
SQL> select * from v$mystat where rownum=1;
       SID STATISTIC#      VALUE
---------- ---------- ----------
       292          0          1
跟踪当前会话：
SQL> exec dbms_support.start_trace
SQL> 执行sql
SQL> exec dbms_support.stop_trace
跟踪其他会话：等待事件+绑定变量，相当于level 12的10046事件。
SQL> select sid,serial#,username from v$session where ...;
SQL> exec dbms_support.start_trace_in_session(sid=>sid,serial=>serial#,waits=>true,binds=>true);
SQL> exec dbms_support.stop_trace_in_session(sid=>sid,serial=>serial#);
dbms_system包：9i时使用
跟踪其他会话：
SQL> select sid,serial#,username from v$session where ...;
SQL> exec dbms_system.set_sql_trace_in_session(sid,serial#,true);
SQL> exec dbms_system.set_sql_trace_in_session(sid,serial#,false);
注意:dbms_system这个包在10gR2官方文档上面没有找到这个包的说明，但数据库中有。
SQL> exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(sid, serial#, 'sql_trace', TRUE);
SQL> exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(sid, serial#, 'sql_trace', FALSE);
使用dbms_system.set_ev设置10046事件
SQL> select sid,serial#,username from v$session where ...;
SQL> exec dbms_system.set_ev(sid,serial#,10046,12,'');
SQL> exec dbms_system.set_ev(sid,serial#,10046,0,'');
最后一个参数只有为''时，才会生成trace文件，否则不报错，但没有trace文件生成。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26015009/viewspace-777381/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26015009/viewspace-777381/