Oracle技术之玩转oracle跟踪
如今，一般DBA使用会话跟踪、SQL跟踪并进行分析已不是稀奇的事情，我估计常用的方法有SQL_TRACE、10046事件等。另外，如果我们需要跟踪其他会话，那需要如何做呢？大家估计最熟悉的是使用ORADEBUG，至少我喜欢这样做。其实，Oracle提供的跟踪方法非常丰富，本文就将这些方法一一介绍，大家可以选择自己喜欢的方式并熟练使用它们。TRACE目录
首先，我可能会关心咱跟踪的trace文件放在哪里，Oracle 10g和11g存放trace目录是不一样的，Oracle 10g中一般是$ORACLE_BASE/admin/$ORACLE_SID/udump和$ORACLE_BASE/admin/$ORACLE_SID/bdump(往往关心udump)，而11g则引入了ADR(Automatic Diagnostic Repository)新特性，trace文件就放于{adr_base}/diag/rdbms/{database_name}/$ORACLE_SID/trace下。
Oracle 11g Trace目录中{adr_base}如下查看：
luocs@MAA> show parameter diag
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
diagnostic_dest string /u01/app/oracle
跟踪文件的前台进程会被定向到USER_DUMP_DEST参数所指定的目录，而跟踪文件的后台进程则使用BACKGROUND_DUMP_DEST参数所指向的目录。无论在哪种情况下，TRACE的后缀都是.trc。
Oracle 10g中，我们可以如下查看trace目录
sys@LTB> show parameter user_dump_dest
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
user_dump_dest string /u01/app/oracle/admin/ltb/udump
sys@LTB> show parameter background_dump_dest
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
background_dump_dest string /u01/app/oracle/admin/ltb/bdump
或者通过查v$parameter获得
sys@LTB> select name, value from v$parameter where name in ('user_dump_dest','background_dump_dest');
NAME VALUE
----------------------------------- -----------------------------------------------------------------
background_dump_dest /u01/app/oracle/admin/ltb/bdump
user_dump_dest /u01/app/oracle/admin/ltb/udump
而Oracle 11g中，前台进程和后台进程所指定的trace目录是相同的
sys@MAA> select name, value from v$parameter where name in ('user_dump_dest','background_dump_dest');
NAME VALUE
-------------------- -----------------------------------------------------------------
background_dump_dest /u01/app/oracle/diag/rdbms/maa/maa/trace
user_dump_dest /u01/app/oracle/diag/rdbms/maa/maa/trace
另外，如果查找当前会话的trace文件，也可以使用如下脚本：
sys@LTB> column trace new_val T
sys@LTB> select c.value || '/' || d.instance_name || '_ora_' ||
2 a.spid || '.trc' ||
3 case when e.value is not null then '_'||e.value end trace
4 from v$process a, v$session b, v$parameter c, v$instance d, v$parameter e
5 where a.addr = b.paddr
6 and b.audsid = userenv('sessionid')
7 and c.name = 'user_dump_dest'
8 and e.name = 'tracefile_identifier'
9/
TRACE
------------------------------------------------------------------
/u01/app/oracle/admin/ltb/udump/ltb_ora_3471.trc
但如果你是普通用户，无法show parameter查看，那么可以通过如下方式获得trace目录：
luocs@LTB> set serveroutput on> luocs@LTB> declare
2 paramname varchar2(256);
3 integerval binary_integer;
4 stringval varchar2(256);
5 paramtype binary_integer;
6begin
7 paramtype:=dbms_utility.get_parameter_value('user_dump_dest',integerval,stringval);
8 if paramtype=1 then
9 dbms_output.put_line(stringval);
10 else
11 dbms_output.put_line(integerval);
12 end if;
13end;
14/
/u01/app/oracle/admin/ltb/udump
PL/SQL procedure successfully completed.
这方法在11g中也适用，但11g的ADR特性让你更方便的获得trace目录，查询v$diag_info即可，如下：
luocs@MAA> select value from v$diag_info where name = 'Default Trace File';
VALUE
----------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_15852.trc
Oracle为安全考虑，trace文件只能被oracle用户或者oinstall用户组成员才能读取，其他用户试图读取该文件会收到错误报告，如下：
# ls -l /u01/app/oracle/admin/ltb/udump/
total 40
-rw-r----- 1 oracle oinstall638 Jan 21 20:12 ltb_ora_27185.trc
-rw-r----- 1 oracle oinstall954 Jan 21 20:13 ltb_ora_27217.trc
$> uid=501(luocs) gid=502(luocs) groups=502(luocs)
$ cat /u01/app/oracle/admin/ltb/udump/ltb_ora_27185.trc
cat: /u01/app/oracle/admin/ltb/udump/ltb_ora_27185.trc: Permission denied
其实我们也可以让普通用户阅读trace内容，Oracle是通过一个隐含参数来控制这些限制的，即_trace_files_public，此隐含参数默认值为FALSE，即不允许普通用户阅读trace文件：
sys@MAA> set pagesize 9999
sys@MAA> set line 130
sys@MAA> col NAME for a20
sys@MAA> col VALUE for a20
sys@MAA> col DESCRIB for a80
sys@MAA> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIB
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3WHERE x.inst_id = USERENV ('Instance')
4 AND y.inst_id = USERENV ('Instance')
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE '%&par%'
7/
Enter value for par: _trace_files_public
old 6: AND x.ksppinm LIKE '%&par%'
new 6: AND x.ksppinm LIKE '%_trace_files_public%'
NAME VALUE DESCRIB
-------------------- -------------------- --------------------------------------------------------------------------------
_trace_files_publicFALSE Create publicly accessible trace files
我们可以修改为TRUE来破坏这限制，如下：
sys@LTB>>
System> sys@LTB> startup force
ORACLE instance started.
Total System Global Area520093696 bytes
Fixed>
Variable> Database Buffers 360710144 bytes
Redo Buffers 6365184 bytes
Database mounted.
Database opened.
sys@LTB> oradebug setmypid
Statement processed.
sys@LTB> oradebug tracefile_name
/u01/app/oracle/admin/ltb/udump/ltb_ora_3608.trc
sys@LTB> ! ls -l /u01/app/oracle/admin/ltb/udump/ltb_ora_3608.trc
-rw-r--r-- 1 oracle oinstall 1752 Jan 21 20:39 /u01/app/oracle/admin/ltb/udump/ltb_ora_3608.trc
– 可见普通用户已经有读取权限了(但，你真正去阅读的时候还会受目录的限制，也需要相应设置，不建议这么做)
跟踪级别(TRACE LEVEL)
玩转跟踪不了解跟踪级别是不行的，下面介绍下Oracle可采用的跟踪接口：
Level 0 = No statistics generated
Level 1 = standard trace output including parsing, executes and fetches plus more.
Level 2 = Same as level 1.
Level 4 = Same as level 1 but includes bind information
Level 8 = Same as level 1 but includes wait's information
Level 12 = Same as level 1 but includes binds and waits
不光是10046 事件，其他事件相关的level信息我们可阅读$ORACLE_HOME/rdbms/mesg/oraus.msg文件。
跟踪当前会话
1、sql_trace
luocs@MAA>> luocs@MAA> — execute our code
luocs@MAA>> 2、10046 events
luocs@MAA>> luocs@MAA> — execute our code
luocs@MAA>> 另外，我们还可以使用dbms_system、dbms_support、dbms_monitor、oradebug方式进行跟踪，这些都在下面介绍。
跟踪其他会话
有的时候，我们需要用DBA权限用户去跟踪某个用户的SESSION，这时候，我们先要获取SID、SERIAL#或者进程号，方法如下：
-- 获取当前会话的SID,SERIAL#
luocs@MAA> select sid, serial# from v$session where sid = (select sid from v$mystat where rownum=1);
SID SERIAL#
---------- ----------
41 1147
-- 获取当前会话的PID和SPID
luocs@MAA> select pid, spid from v$process p, v$session s where p.addr = s.paddr and s.sid=(select sid from v$mystat where rownum=1);
PID SPID
---------- ------------------------------------------------
24 16434
-- 获取系统级别的SID与SERIAL#等
sys@MAA> select s.sid,s.serial#,s.username,s.osuser
2from v$session s,v$process p
3where s.paddr=p.addr;
SID SERIAL# USERNAME OSUSER
---------- ---------- ---------- ----------
2 1 oracle
3 1 oracle
4 1 oracle
5 1 oracle
6 1 oracle
7 1 oracle
8 1 oracle
9 1 oracle
10 1 oracle
11 1 oracle
12 1 oracle
13 1 oracle
14 1 oracle
15 1 oracle
16 1 oracle
17 1 oracle
18 1 oracle
20 1 oracle
23 23 oracle
28 271 oracle
41 1149 LUOCS oracle
53 21805 LUOCS oracle
25 7 oracle
27 1 oracle
40 1127 SYS oracle
29 5 oracle
30 1 oracle
34 1 oracle
22 11 oracle
44 21617 SYS oracle
47 833 XLZHGJ oracle
38 3 oracle
37 13 oracle
21 79 oracle
48 1303 oracle
35 rows selected.
下面介绍几个跟踪会话方法
1、DBMS_SYSTEM
DBMS_SYSTEM是Oracle 10g之前常用的跟踪作用的包，虽然我们在Oracle 10g之后的版本无法看到这个包的相关说明，但功能还是能用的。
– 设置时间相关统计收集
luocs@MAA> exec dbms_system.set_bool_param_in_session(41,1149,'timed_statistics',true);
– 设置max_dump_file_size以trace文件大小足够容纳信息
luocs@MAA> exec dbms_system.set_int_param_in_session(41,1149,'max_dump_file_size',20000000);
– 设置10046事件并且指定LEVEL
luocs@MAA> exec dbms_system.set_ev(41,1149,10046,12,'');
– 激活trace跟踪
luocs@MAA> exec dbms_system.set_sql_trace_in_session(41,1149,true);
– 停止trace跟踪
luocs@MAA> exec dbms_system.set_sql_trace_in_session(41,1149,false);
luocs@MAA> exec dbms_system.set_ev(41,1149,10046,0,'');
下面是我的演示内容：
luocs@MAA> select sid, serial# from v$session where sid = (select sid from v$mystat where rownum=1);
SID SERIAL#
---------- ----------
53 21805
luocs@MAA> exec dbms_system.set_bool_param_in_session(53,21805,'timed_statistics',true);
PL/SQL procedure successfully completed.
luocs@MAA> exec dbms_system.set_int_param_in_session(53,21805,'max_dump_file_size',20000000);
PL/SQL procedure successfully completed.
luocs@MAA> exec dbms_system.set_ev(53,21805,10046,12,'');
PL/SQL procedure successfully completed.
luocs@MAA> exec dbms_system.set_sql_trace_in_session(53,21805,true);
PL/SQL procedure successfully completed.
luocs@MAA> variable x number
luocs@MAA> exec :l := 1
PL/SQL procedure successfully completed.
luocs@MAA> select count(*) from test where> COUNT(*)
----------
1
luocs@MAA> exec :l := 100
PL/SQL procedure successfully completed.
luocs@MAA> select count(*) from test where> COUNT(*)
----------
9999
luocs@MAA> exec dbms_system.set_sql_trace_in_session(53,21805,false);
PL/SQL procedure successfully completed.
luocs@MAA>exec dbms_system.set_ev(53,21805,10046,0,'');
PL/SQL procedure successfully completed.
luocs@MAA> select value from v$diag_info where name = 'Default Trace File';
VALUE
----------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_15826.trc
trace部分内容：
=====================
PARSING IN CURSOR #47603153651720 len=21 dep=0 uid=51 oct=47 lid=51 tim=1359462720172941 hv=3459344829 ad='9ed28e20' sqlid='0haapcz732udx'
BEGIN :l := 1; END;
END OF STMT
PARSE #47603153651720:c=0,e=670,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1359462720172939
EXEC #47603153651720:c=2000,e=1491,p=0,cr=0,cu=0,mis=1,r=1,dep=0,og=1,plh=0,tim=1359462720174513
PARSE #47603153650544:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1359462720174801
EXEC #47603153650544:c=0,e=186,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1359462720175049
*** 2013-01-29 20:32:11.351
CLOSE #47603153651720:c=0,e=31,dep=0,type=0,tim=1359462731351173
CLOSE #47603153650544:c=0,e=38,dep=0,type=3,tim=1359462731351294
=====================
PARSING IN CURSOR #47603153651720 len=37 dep=0 uid=51 oct=3 lid=51 tim=1359462731351922 hv=874746037 ad='9ed283d0' sqlid='9pukpvhu2745p'
select count(*) from test where> END OF STMT
PARSE #47603153651720:c=0,e=546,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1359462731351921
EXEC #47603153651720:c=2000,e=2221,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=4002023942,tim=1359462731354222
FETCH #47603153651720:c=0,e=170,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=4002023942,tim=1359462731354557
STAT #47603153651720>
STAT #47603153651720> FETCH #47603153651720:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=4002023942,tim=1359462731354893
PARSE #47603153650544:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1359462731355442
EXEC #47603153650544:c=0,e=206,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1359462731355711
*** 2013-01-29 20:32:17.365
CLOSE #47603153651720:c=0,e=13,dep=0,type=0,tim=1359462737365691
CLOSE #47603153650544:c=0,e=25,dep=0,type=3,tim=1359462737365809
2、DBMS_SUPPORT
早期版本提供的跟踪相关包还有DBMS_SUPPORT，它可以跟踪用户的SESSION信息。DBMS_SUPPORT默认没有安装，我们需要手动执行脚本来安装
luocs@MAA> desc dbms_support
ERROR:
ORA-04043: object dbms_support does not exist
– INSTALL THE PACKAGE
sys@MAA> @?/rdbms/admin/dbmssupp
Package created.
Package body created.
sys@MAA> @?/rdbms/admin/prvtsupp.plb
Package body created.
– 查看版本
sys@MAA> select dbms_support.PACKAGE_VERSION from dual;
PACKAGE_VERSION
———————————————————————————————————————————-
DBMS_SUPPORT Version 1.0 (17-Aug-1998) – Requires Oracle 7.2 – 8.0.5
– 开启某session的跟踪，可以选择等待信息和绑定变量信息
sys@MAA> exec dbms_support.start_trace_in_session(53,21805,waits=>false,binds=>true);
– 停止某session的跟踪
sys@MAA> exec dbms_support.stop_trace_in_session(53,21805)
– 启动跟踪当前session
sys@MAA> exec dbms_support.start_trace(waits=>false,binds=>true);
– 停止跟踪当前session
sys@MAA> exec dbms_support.stop_trace;
下面是我的演示：
luocs@MAA> select sid, serial# from v$session where sid = (select sid from v$mystat where rownum=1);
SID SERIAL#
---------- ----------
53 21807
sys@MAA> exec dbms_support.start_trace_in_session(53,21807,waits=>false,binds=>true);
PL/SQL procedure successfully completed.
luocs@MAA> variable x number
luocs@MAA> exec :l := 100
PL/SQL procedure successfully completed.
luocs@MAA> select count(*) from test where> 2;
COUNT(*)
----------
9999
sys@MAA> exec dbms_support.stop_trace_in_session(53,21807);
PL/SQL procedure successfully completed.
luocs@MAA> select value from v$diag_info where name = 'Default Trace File';
VALUE
----------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_16974.trc
-- 部分TRACE内容
=====================
PARSING IN CURSOR #47953679205472 len=38 dep=0 uid=51 oct=3 lid=51 tim=1359464472023679 hv=3115683868 ad='9e731000' sqlid='fsa2yt2wvb40w'
select count(*) from test where> END OF STMT
PARSE #47953679205472:c=999,e=984,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1359464472023678
BINDS #47953679205472:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b9d16045340bln=22avl=02flg=05
value=100
EXEC #47953679205472:c=2999,e=2765,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3727996439,tim=1359464472026526
FETCH #47953679205472:c=3000,e=2883,p=0,cr=62,cu=0,mis=0,r=1,dep=0,og=1,plh=3727996439,tim=1359464472029485
STAT #47953679205472>
STAT #47953679205472> FETCH #47953679205472:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3727996439,tim=1359464472029832
PARSE #47953679348848:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1359464472030399
BINDS #47953679348848:
Bind#0
oacdty=123 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000000 frm=00 csi=00 siz=4000 off=0
toid ptr value=A1E4A4A0 length=16
C7D9741553643AE0E0430100007F2FB7
kxsbbbfp=2b9d1605df70bln=4000avl=00flg=15
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b9d1605f310bln=22avl=22flg=05
value=###
An invalid number has been seen.Memory contents are :
Dump of memory from 0x00002B9D1605F310 to 0x00002B9D1605F326
2B9D1605F310 000010C1 00000000 00000000 00000000[................]
2B9D1605F320 00000000 00000000 [........]
EXEC #47953679348848:c=0,e=393,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1359464472030861
-- 跟踪当前会话
sys@MAA> grant execute on dbms_support to luocs;
Grant succeeded.
luocs@MAA> SELECT DBMS_SUPPORT.MYSID from DUAL;
SELECT DBMS_SUPPORT.MYSID from DUAL
*
ERROR at line 1:
ORA-00904: "DBMS_SUPPORT"."MYSID": invalid> -- 这时候发现普通用户无法执行，我们需要如下授权
sys@MAA> grant execute on dbms_support to luocs;
Grant succeeded.
sys@MAA> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;
Synonym created.
luocs@MAA> SELECT DBMS_SUPPORT.MYSID from DUAL;
MYSID
----------
53
luocs@MAA> exec dbms_support.start_trace(waits=>true,binds=>false);
PL/SQL procedure successfully completed.
luocs@MAA> select count(*) from test;
COUNT(*)
----------
10000
luocs@MAA> exec dbms_support.stop_trace;
PL/SQL procedure successfully completed.
-- trace部分内容
=====================
PARSING IN CURSOR #47179837486632 len=25 dep=0 uid=51 oct=3 lid=51 tim=1359465350267384 hv=297253644 ad='9ed2c088' sqlid='7b2twsn8vgfsc'
select count(*) from test
END OF STMT
PARSE #47179837486632:c=0,e=144,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1950795681,tim=1359465350267383
EXEC #47179837486632:c=0,e=57,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1950795681,tim=1359465350267512
WAIT #47179837486632: nam='SQL*Net message to client' ela= 2 driver> FETCH #47179837486632:c=2000,e=1851,p=0,cr=37,cu=0,mis=0,r=1,dep=0,og=1,plh=1950795681,tim=1359465350269457
STAT #47179837486632>
STAT #47179837486632>
WAIT #47179837486632: nam='SQL*Net message from client' ela= 164 driver> FETCH #47179837486632:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1950795681,tim=1359465350269824
WAIT #47179837486632: nam='SQL*Net message to client' ela= 1 driver>
WAIT #47179837486632: nam='SQL*Net message from client' ela= 458 driver> PARSE #47179837532376:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1359465350270400
WAIT #47179837532376: nam='SQL*Net message to client' ela= 1 driver> EXEC #47179837532376:c=0,e=239,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1359465350270709
3、DBMS_MONITOR
DBMS_MONITOR是Oracle 从10g开始引入的程序包，它即可以实现传统的跟踪功能，也可以在拥有连接池或共享服务器等的多层平台中使用，请阅读下段文：
Oracle 10g offers a new package to allow sessions to be traced end to end in multi-tier architectures that share sessions using connection pooling or multi-threading. This package allows applications written using for instance JDBC / Java or something like Forte to be traced where it would normally be very difficult to>
The new functionality works in three levels. You can use the old SID / SERIAL# pairings to> 下面就看一下我的演示：
-- 使用传统方式，通过SID和SERIAL#来跟踪会话
luocs@MAA> select sid, serial# from v$session where sid = (select sid from v$mystat where rownum=1);
SID SERIAL#
---------- ----------
53 21813
-- 启动跟踪
sys@MAA> exec DBMS_MONITOR.SESSION_TRACE_ENABLE(53,21813,true,true);
PL/SQL procedure successfully completed.
luocs@MAA> variable x number
luocs@MAA> exec :l := 1;
PL/SQL procedure successfully completed.
luocs@MAA> select count(*) from test where> COUNT(*)
----------
1
-- 停止跟踪
sys@MAA> exec DBMS_MONITOR.SESSION_TRACE_DISABLE(53,21813);
PL/SQL procedure successfully completed.
TRACE内容略。
– 当前会话跟踪
luocs@MAA> EXEC DBMS_MONITOR.session_trace_enable;
luocs@MAA> EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>FALSE);
luocs@MAA> EXEC DBMS_MONITOR.session_trace_disable;
– 另外，我们也可以使用客户端标识符来进行跟踪
下面是我一个使用例子
luocs@WWW> BEGIN
2DBMS_SESSION.SET_IDENTIFIER('www.luocs.com');
3DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE
4 (CLIENT_ID => 'www.luocs.com',
5 WAITS => TRUE
6 );
7END;
8/
PL/SQL procedure successfully completed.
luocs@WWW> select /*+ parallel(3) */ count(*) from test1;
COUNT(*)
----------
456128
luocs@WWW> exec DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE('www.luocs.com');
PL/SQL procedure successfully completed.
sys@WWW> select client_identifier from v$session where sid=(select sid from v$mystat where rownum=1);
CLIENT_IDENTIFIER
----------------------------------------------------------------------------------------------------
www.luocs.com
sys@WWW> col PRIMARY_ID for a30
sys@WWW> select trace_type, primary_id, waits, binds from dba_enabled_traces;
TRACE_TYPE PRIMARY_ID WAITS BINDS
------------------------------------------ ------------------------------ ---------- ----------
CLIENT_ID www.luocs.com TRUE FALSE
$ cd /u01/app/oracle/diag/rdbms/www/ltb1/trace/
$ trcsess clientid=www.luocs.com output=luocs_test2.trc
$ ls luocs_test2.trc
-rw-r--r-- 1 oracle oinstall 103514 Jan 27 07:06 luocs_test2.trc
-- trace内容略
关于DBMS_MONITOR更多更详尽的内容，请阅读官方NOTE PL/SQL Packages and Types Reference部分。
4、 DBMS_SESSION
DBMS_SESSION也有几个PL/SQL是和跟踪相关的，它们就是SESSION_TRACE_ENABLE/DISABLE、SET_SQL_TRACE，注意DBMS_SESSION只能跟踪当前会话，无法跟踪其他SESSION。
好，理论介绍还是减少点，请阅读官方NOTE。
下面是我的演示：
-- SESSION_TRACE_ENABLE/DISABLE过程可以设置等待和变量跟踪
luocs@MAA> exec DBMS_SESSION.SESSION_TRACE_ENABLE(TRUE,TRUE);
luocs@MAA> exec DBMS_SESSION.SESSION_TRACE_ENABLE(WAITS=>TRUE, BINDS=>TRUE);
luocs@MAA> variable x number
luocs@MAA> exec :l := 100
PL/SQL procedure successfully completed.
luocs@MAA> select count(*) from test where> COUNT(*)
----------
9999
-- 停止跟踪
luocs@MAA> exec DBMS_SESSION.SESSION_TRACE_DISABLE();
-- trace内容略
-- SET_SQL_TRACE过程好比alter session set sql_trace=true|false
luocs@MAA> exec DBMS_SESSION.SET_SQL_TRACE(TRUE);
PL/SQL procedure successfully completed.
luocs@MAA> variable x number
luocs@MAA> exec :l := 100
PL/SQL procedure successfully completed.
luocs@MAA> select count(*) from test where> COUNT(*)
----------
9999
luocs@MAA> exec DBMS_SESSION.SET_SQL_TRACE(FALSE);
PL/SQL procedure successfully completed.
-- trace内容里看不到绑定变量和等待的信息
5、ORADEBUG
ORADEBUG功能非常强大，我们执行oradebug help将会看到非常多的功能可使用，我也非常喜欢使用它，甚至有的时候10046、10053等events都是通过ORADEBUG来设置。ORADEBUG是SYS的工具，即使权限较大的SYSTEM用户都无法使用它：
system@MAA> show user
USER is "SYSTEM"
system@MAA> oradebug setmypid
ORA-01031: insufficient privileges
因此，oradebug只能跟踪SYS用户的当前SESSION，如下方式：
sys@MAA> oradebug setmypid
sys@MAA> oradebug unlimit
sys@MAA> oradebug event 10046 trace name context forever, level 12
sys@MAA> exec our code
sys@MAA> oradebug event 10046 trace name context off
sys@MAA> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_17641.trc
当然，我们通过oradebug非常方便地跟踪其他会话，如下方式：
luocs@MAA> select pid, spid from v$process p, v$session s where p.addr = s.paddr and s.sid=(select sid from v$mystat where rownum=1);
PID SPID
---------- ------------------------------------------------
25 17678
-- 指定跟踪SESSION的SPID(OS process)
sys@MAA> oradebug setospid 17678
Oracle pid: 25, Unix process pid: 17678, image: oracle@maa3.luocs.com (TNS V1-V3)
-- 或者指定跟踪SESSION的PID(Oracle process> sys@MAA> oradebug setorapid 25
Oracle pid: 25, Unix process pid: 17678, image: oracle@maa3.luocs.com (TNS V1-V3)
sys@MAA> oradebug unlimit
sys@MAA> oradebug event 10053 trace name context forever, level 1
sys@MAA> exec our code
sys@MAA> oradebug event 10053 trace name context off
sys@MAA> oradebug tracefile_name
以上五种方法，不要求DBA全会，在工作中选择自己喜欢的一两种方式，能够熟练使用即可。
另外，查看SQL TRACE还有DBA常用的AUTOTRACE(SQL*Plus)和10053 EVENTS(CBO trace)。
-- AUTOTRACE
luocs@MAA> set autotrace on
luocs@MAA> set autotrace on explain
luocs@MAA> set autotrace on statistics
luocs@MAA> set autotrace traceonly
luocs@MAA> set autotrace traceonly explain
luocs@MAA> set autotrace traceonly explain statistics
luocs@MAA> set autotrace off
我喜欢使用缩略方式，比如
luocs@MAA> set autot trace exp stat
-- 10053 EVENTS
luocs@MAA>>
luocs@MAA>> sys@MAA> oradebug event 10053 trace name context forever, level 1
sys@MAA> oradebug event 10053 trace name context off
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
页:
[1]