最后，点击"Trace"按钮。一旦有语句被上述函数调用，你就可以从监控窗口看到这些语句：
SQL代码
...
[2010-5-28 3:53:23.426]User call: _rpisplu (TID: 5276)
[Args(6)]:
6
0
0
"select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0"
0x84(=>NULL)
0
[2010-5-28 3:53:23.442]User call: _rpisplu (TID: 5276)
[Args(6)]:
6
0
0
"alter session set NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'"
0xd4(=>NULL)
0
[2010-5-28 3:53:23.473]User call: _opiprs (TID: 5276)
[Args(6)]:
0x70cce00(=>0x4000000)
"alter session set NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'"
0xd5(=>NULL)
0x4bfe214(=>0x18)
0
6
[2010-5-28 3:53:23.504]User call: _rpisplu (TID: 5276)
[Args(6)]:
3
0x20(=>NULL)
0x20(=>NULL)
"select sysdate + 1 / (24 * 60) from dual"
0x28(=>NULL)
1
[2010-5-28 3:53:23.520]User call: _rpisplu (TID: 5276)
[Args(6)]:
5
0x20(=>NULL)
0x20(=>NULL)
"DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; "
0xd5(=>NULL)
0
...
[2010-5-28 3:53:23.426]User call: _rpisplu (TID: 5276)
[Args(6)]:
6
0
0
"select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0"
0x84(=>NULL)
0
[2010-5-28 3:53:23.442]User call: _rpisplu (TID: 5276)
[Args(6)]:
6
0
0
"alter session set NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'"
0xd4(=>NULL)
0
[2010-5-28 3:53:23.473]User call: _opiprs (TID: 5276)
[Args(6)]:
0x70cce00(=>0x4000000)
"alter session set NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'"
0xd5(=>NULL)
0x4bfe214(=>0x18)
0
6
[2010-5-28 3:53:23.504]User call: _rpisplu (TID: 5276)
[Args(6)]:
3
0x20(=>NULL)
0x20(=>NULL)
"select sysdate + 1 / (24 * 60) from dual"
0x28(=>NULL)
1
[2010-5-28 3:53:23.520]User call: _rpisplu (TID: 5276)
[Args(6)]:
5
0x20(=>NULL)
0x20(=>NULL)
"DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; "
0xd5(=>NULL)
0
...
点击"Stop"按钮，停止追踪。
例子 2:
理解SQL是如何被执行计划驱动执行的。
我们知道，查询计划实际上就是驱动Oracle通过特定函数及顺序来获取数据。我们可以通过追踪这些函数来理解执行计划。
首先下载以下文件，解压，重命名为"TracePoints.txt"，放到OraTracer.exe所在目录：
http://www.HelloDBA.com/download/TracePoints_ALL_Query_Calls.zip
然后获取到你需要追踪的会话的SPID：
SQL代码
HELLODBA.COM>select distinct spid from v$mystat m, v$session s, v$process p where s.sid=m.sid and s.paddr=p.addr;
SPID
------------
11076
SPID
------------
11076
