ITPub博客

首页 > 数据库 > Oracle > 利用10046事件收集SQL的trace文件

利用10046事件收集SQL的trace文件

Oracle 作者:desert_xu 时间:2017-01-12 15:49:53 0 删除 编辑
Event 10046是为Oracle session收集扩展的sql_trace信息的标准方法,通常为了诊断SQL调优类问题,我们需要记录下这些语句在执行过程中产生的等待以及bind variables(绑定变量)的信。 这些信息可以通过级别为12的10046 trace获得。

一、trace文件的位置:
在11g之前:
SQL> show parameter user_dump_dest
在11g之后:
SQL> show parameter diagnostic_dest
注:下面的某些例子中会设定tracefile_identifier,通过这个设置可以帮助我们更容易的找到生成的trace文件

二、在Session级打开trace
适用于SQL语句可以在新的session创建后再运行
alter session set tracefile_identifier='10046';
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever,level 12';
-- 执行需要被trace的SQL --
select * from dual;
exit;
如果不退出当前session, 可以用以下命令关闭trace:
alter session set events '10046 trace name context off';
注意,如果session没有被彻底地关闭并且跟踪被停止了,某些重要的trace信息的可能会丢失。

三、跟踪一个已经开始的进程
如果需要跟踪一个已经存在session,可以用 oradebug连接到session上,并发起10046 trace。
1、首先,用某种方法找到需要被跟踪的session
例如,在SQL*Plus里,找出目标session的OS的进程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 是操作系统的进程标识符(os pid)
PID 是Oracle的进程标识符(ora pid)
2、一旦找到OS PID,就可以用以下命令初始化跟踪:
使用SPID跟踪:
假设需要被跟踪的OSPID是9834. 以sysdba的身份登录到SQL*Plus并执行下面的命令:
connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
Note: 也可以通过oradebug使用 'setorapid'命令连接到一个session.
使用ORAPID跟踪:
使用PID(Oracle进程标识符)(而不是SPID), oradebug命令将被改为:
connect / as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
跟踪过程完成以后,关闭oradebug跟踪:
oradebug event 10046 trace name context off

四、实例层的跟踪
注意: 在实例层设置跟踪需要非常小心,这是因为整体性能会由于所有session都被跟踪而受到影响。
这个设置将会跟踪在这个参数设置“以后”创建的每个session。已经存在的session不会被跟踪。 系统层的10046跟踪适用于当我们知道问题session会出现,但是不能预先识别它的时候。
在这种情况下,可以打开系统层跟踪一小段时间,当问题被重现以后立即将其关闭,然后从已经生成的trace中查找需要的信息。
用以下命令打开系统层的跟踪:
alter system set events '10046 trace name context forever,level 12';
用以下命令关闭在所有session中的10046跟踪:
alter system set events '10046 trace name context off';

五、初始化参数设置
设置以下参数并重新启动实例后,实例上所有的session都会打开跟踪。
event="10046 trace name context forever,level 12"
移除这个参数并且重启实例, 或者使用下面的alter system命令可以关闭跟踪。
alter system set events '10046 trace name context off';

六、通过logon trigger设置跟踪
有的时候当需要跟踪某个特定用户的操作时,可以使用logon trigger来打开跟踪,下面是一个例子:
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 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;
/
为了能打开跟踪session, 执行trigger的用户需要被显式地授予'alter session' 权限. 例如,
grant alter session to <USERNAME> ;

七、用SQLT收集trace
SQLTXPLAIN的Xecute方法生成的诊断文件中会包含10046 trace。正如XECUTE这个名字所示,SQLT会执行被分析的SQL语句, 然后生成一个诊断文件集(包括10046 trace)。详见:
Document:215187.1 - SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly
用这种方法生成的trace文件会被包含在SQLT输出包中, 格式为:
sqlt_s12345_10046_execute.trc
12345是SQLT报告ID。

八、用DBMS_MONITOR进行跟踪
DBMS_MONITOR包提供几个打开跟踪的方法。 详细请见:
Document:293661.1 - Tracing Enhancements In 10g Using DBMS_MONITOR
Oracle Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)
Part Number B14258-02
Chapter 60 DBMS_MONITOR

九、其它特定场景下打开跟踪的方法
Document:21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Document:1274511.1 General SQL_TRACE / 10046 trace Gathering Examples
Document:160124.1 How to Set SQL Trace on with 10046 Event Trace which Provides the Bind Variables
Document:371678.1 Capture 10046 Traces Upon User Login (without using a trigger)
Document:1102801.1 How to Get 10046 Trace for Parallel Query
Document:242374.1 Tracing PX session with a 10046 event or sql_trace
Document:258418.1 Getting 10046 Trace for Export and Import
如果您运行的是PL/SQL存储过程或包,那么可以通过使用PL/SQL profiler判断PL/SQL运行期间的时间消耗的具体信息。
下面的文档介绍了PL/SQL profiler的使用方法:
Document:243755.1 Implementing and Using the PL/SQL Profiler

十、Trace文件解析
Document:199081.1 SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference
Document:39817.1 Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output
Document:224270.1 Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046
Document:41634.1 - TKProf Basic Overview
Document:32951.1 - TKProf Interpretation (9i and below)
Document:760786.1 - TKProf Interpretation (9i and above)
Document:214106.1 - Using TKProf to compare actual and predicted row counts
Document:199083.1 * Master Note: SQL Query Performance Overview
Document:398838.1 * FAQ: SQL Query Performance - Frequently Asked Questions

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

请登录后发表评论 登录
全部评论

注册时间:2013-10-23

  • 博文量
    79
  • 访问量
    245969