1、SQL_TRACE
当SQL语句出现性能问题时,我们可以用SQL_TRACE来跟踪SQL的执行情况,通过跟踪,我们可以了解一条SQL或者PL/SQL包的运行情况,SQL_TRACE命令会将SQL执行的整个过程输出到一个trace文件中,我们可以读这个trace 文件来了解在这个SQL执行过程中Oracle 都做了哪些操作。
可以通过sql命令启动SQL_TRACE,或者在初始化参数里面。
SQL>alter session set sql_trace=true;
或者
SQL> alter database set sql_trace=true;
这两条命令的区别:
在session级别设置,只对当前session进行跟踪;在实例级别database,会对实例上所有的SQL做跟踪,这种方式跟踪的SQL太多,代价是非常大的,所有很少用。如果是在初始化文件里面设置,只需要在参数文件里添加一个sql_trace 参数即可。注意: 通过在全局启用sql_trace,我们可以跟踪到所有后台进程的活动,很多在文档中的抽象说明,通过跟踪文件的实时变化,我们可以 清晰的看到各个进程之间的紧密协调。
示例:
1. 确定当前的trace文件。
1.1 通过设置trace 文件标识
SQL> alter session set tracefile_identifier='安庆怀宁';
会话已更改。
设置标识的目的就是方便我们查找生成的trace文件。我们只需要在trace目录查找文件名里带有标识的文件即可。 在Oracle 10g中,SQL_TRACE生成的trace文件默认路劲是$ORACLE_BASE/admin/SID/udump.
到了11g,trace 默认路径在:$ORACLE_BASE/diag/rdbms/orcl/orcl/trace目录下.
在当前的session中启用:大多数的情况下,我们使用sql_trace跟踪当前进程。通过跟踪当前进程可以发现当前操作的后台数据库递归活动(这在研究数据库新特性时尤其有效),研究SQL执行,发现后台错误等。在session级启用和停止sql_trace方式如下:启用当前session的跟踪:
SQL> alter session set sql_trace=true;
Session altered.
此时的SQL操作将被跟踪:
SQL> select count(*) from dba_users;
COUNT(*)
----------
34
结束跟踪:
SQL> alter session set sql_trace=false;
Session altered.
跟踪其他用户进程
在很多时候我们需要跟踪其他用户的进程,而不是当前用户,我们可以通过Oracle提供的系统包DBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION来完成。
SET_SQL_TRACE_IN_SESSION程序需要提供三个参数:
SQL> desc dbms_system
…
PROCEDURE SET_SQL_TRACE_IN_SESSION
Argument Name Type In/Out Default?
----------------- ------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
SQL_TRACE BOOLEAN IN
…
通过v$session我们可以获得sid、serial#等信息。
获得进程信息,选择需要跟踪的进程:
SQL> select sid,serial#,username from v$session where username is not null;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
8 2041 SYS
9 437 EYGLE
设置跟踪:
SQL> exec dbms_system.set_sql_trace_in_session(9,437,true)
PL/SQL procedure successfully completed.
….
可以等候片刻,跟踪session执行任务,捕获sql操作……
停止跟踪:
SQL> exec dbms_system.set_sql_trace_in_session(9,437,false)
PL/SQL procedure successfully completed.
1.2直接用如下SQL直接查出,当前的trace文件名。
/* Formatted on 2010/9/1 23:56:24 (QP5 v5.115.810.9015) */
SELECT d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc'
AS "trace_file_name"
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') d;
SQL> SELECT d.VALUE
2 || '/'
3 || LOWER (RTRIM (i.INSTANCE, CHR (0)))
4 || '_ora_'
5 || p.spid
6 || '.trc' as "trace_file_name"
7 FROM (SELECT p.spid
8 FROM v$mystat m, v$session s, v$process p
9 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
10 (SELECT t.INSTANCE
11 FROM v$thread t, v$parameter v
12 WHERE v.NAME = 'thread'
13 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
14 (SELECT VALUE
15 FROM v$parameter
16 WHERE NAME = 'user_dump_dest') d;
trace_file_name
--------------------------------------------------------------------------------
d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_3048.trc
2. 启动SQL_TRACE
SQL> alter session set sql_trace=true;
会话已更改。
3. 进行相关事务操作
SQL> select * from t;
4. 关闭SQL_TRACE
SQL> alter session set sql_trace=false;
会话已更改。
注意,这里是显示的关闭SQL_TRACE,在session级别,也可以直接退出SQLPLUS来终止SQL_TRACE。
如何查看该session的sql_trace参数呢?
SQL> ALTER session SET SQL_TRACE=TRUE; (implicitly sets event 10046 at level 1)
SQL> exec dbms_system.read_ev(10046,:lev);
SQL> print lev
得到的数据 lev 应该为 1
表示 sql_trace=true, lev为0 则sql_trace=false
instance 级别的sql_trace
alter system set sql_trace=true scope=spfile;
or
alter system set sql_trace=true scope=both;
show parameter sql_trace 就可以查看sql_trace 的置
2、10046 事件
10046 事件主要用来跟踪SQL语句,它并不是ORACLE 官方提供给用户的命令,在官方文档上也找不到事件的说明信息。 但是用的却比较多,因为10046事件获取SQL的信息比SQL_TRACE 更多。 更有利于我们对SQL的判断。
10046 事件按照收集信息内容,可以分成4个级别:
Level 1: 等同于SQL_TRACE 的功能
Level 4: 在Level 1的基础上增加收集绑定变量的信息
Level 8: 在Level 1 的基础上增加等待事件的信息
Level 12:等同于Level 4+Level 8, 即同时收集绑定变量信息和等待事件信息。
1、 对当前session 使用10046事件:
SQL>alter session set events ‘10046 trace name context forever, level 12’; --启动10046事件
执行相关事务
SQL>alter session set events ‘10046 trace name context off’; -- 关闭10046事件
该事件收集的信息也是放在trace文件中,查看trace文件的方法,参考:TKPROF 工具。
2、对其他的会话进行跟踪:
之前说的都是对当前session进行跟踪,在生产环境中,可能需要对其他session进行跟踪,有如下2种方法:
1、用SQL_TRACE跟踪
SQL> select sid,serial# from v$session where SID=267;
SID SERIAL#
---------- ----------
267 996
SQL> execute dbms_system.set_sql_trace_in_session(267,996,true); -- 启动SQL_TRACE
PL/SQL 过程已成功完成。
SQL> execute dbms_system.set_sql_trace_in_session(267,996,false); -- 关闭SQL_TRACE
PL/SQL 过程已成功完成。
2、 使用10046 事件跟踪
SQL> exec dbms_monitor.session_trace_enable(267,996,waits=>true,binds=>true); -- 启动trace
PL/SQL 过程已成功完成。
SQL> exec dbms_monitor.session_trace_disable(267,996); -- 关闭trace
PL/SQL 过程已成功完成。
注意:
如果一条SQL语句中包含了通过DBLINK进行的数据操作,我们想对这条SQL进行trace跟踪,在本地只能够trace到本地执行的SQL信息,而对于远程的SQL语句,由于它运行在远端的数据库上,我们要获得它的信息,需要到远端的数据库上,找到运行这条SQL语句的session,然后对它做Trace。 另外,这条SQL语句的执行计划也只能从远端数据库上捕获到。
总之,当SQL语句操作出现性能问题时,我们可以用SQL_TRACE 或者10046事件进行跟踪是最合适的。 如果是数据库整体性能下降,就需要使用statspack或者AWR对数据库进行分析。Oracle AWR 介绍http://blog.csdn.net/changyanmanman/article/details/7746584
3、使用oradebug 生成10046 事件
SYS@anqing1(rac1)> oradebug setmypid
SYS@anqing1(rac1)> oradebug event 10046trace name context forever,level 8;
SYS@anqing1(rac1)> oradebug event 10046trace name context off;
SYS@anqing1(rac1)> oradebugtracefile_name
/u01/app/oracle/admin/anqing/udump/anqing1_ora_17800.trc
Oracle oradebug 命令 使用说明:http://blog.csdn.net/changyanmanman/article/details/7005169