收集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
跟踪产生的跟踪文件名称类似于
实例级别的跟踪
注意:在实例级别启用跟踪因为每一个会话都会被跟踪这样对性能是有影响的在设置这个跟踪参数后产生的每一个会话都会被跟踪断开的会话将不会被跟踪设置系统级别的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=