在11G之前跟踪某个具体的SQL很难办到，开启的SESSION跟踪将跟踪与这个会话
相关的所有操作，而11G加强了这方面的跟踪。
SQL> alter system flush shared_pool;
System altered.
SQL> select * from test.t;
ID NAME
---------- --------------------
1 a
2 b
3 c
124 d
SQL> select * from test.t where id=1;
ID NAME
---------- --------------------
1 a
SQL> col sql_text format a30
SQL> select sql_id,sql_text from v$sql where sql_text like 'select * from test.t%';
SQL_ID SQL_TEXT
------------- ------------------------------
gnhzyjcpg453x select * from test.t
05j387tgcrswb select * from test.t where id=
1
SQL> alter session set events 'sql_trace[sql:gnhzyjcpg453x | 05j387tgcrswb]';
Session altered.
SQL> select * from test.t;
ID NAME
---------- --------------------
1 a
2 b
3 c
124 d
SQL> select * from test.t where id=1;
ID NAME
---------- --------------------
1 a
SQL> select * from test.t where id=2;
ID NAME
---------- --------------------
2 b
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/huateng/huateng/trace/huateng_ora_15138.trc
SQL>
[oracle@db2server trace]$ more /u01/app/oracle/diag/rdbms/huateng/huateng/trace/huateng_ora_15138.trc
Trace file /u01/app/oracle/diag/rdbms/huateng/huateng/trace/huateng_ora_15138.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/db11gr2
System name: Linux
Node name: db2server
Release: 2.6.18-92.el5
Version: #1 SMP Tue Jun 10 18:49:47 EDT 2008
Machine: i686
Instance name: huateng
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 15138, image: oracle@db2server (TNS V1-V3)
*** 2013-01-18 12:34:19.745
*** SESSION ID:(36.7) 2013-01-18 12:34:19.745
*** CLIENT ID:() 2013-01-18 12:34:19.745
*** SERVICE NAME:(SYS$USERS) 2013-01-18 12:34:19.745
*** MODULE NAME:(sqlplus@db2server (TNS V1-V3)) 2013-01-18 12:34:19.745
*** ACTION NAME:() 2013-01-18 12:34:19.745
=====================
PARSING IN CURSOR #2 len=323 dep=1 uid=0 oct=3 lid=0 tim=1358483659698987 hv=2755668964 ad='2712bdd4' sqlid='38xaasqk40az4'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(
SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, 1 AS C2 FROM
"TEST"."T" "T") SAMPLESU
B <-- 动态 抽样SQL
END OF STMT
PARSE #2:c=3999,e=49181,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=3225603066,tim=1358483659693485
EXEC #2:c=0,e=90,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3225603066,tim=1358483659767126
FETCH #2:c=3000,e=21061,p=0,cr=14,cu=0,mis=0,r=1,dep=1,og=1,plh=3225603066,tim=1358483659788216
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=14 pr=0 pw=0 time=0 us)'
STAT #2 id=2 cnt=4 pid=1 pos=1 obj=0 op='PARTITION RANGE ALL PARTITION: 1 2 (cr=14 pr=0 pw=0 time=12 us cost=4 size=0 card=818)'
STAT #2 id=3 cnt=4 pid=2 pos=1 obj=77127 op='TABLE ACCESS FULL T PARTITION: 1 2 (cr=14 pr=0 pw=0 time=4 us cost=4 size=0 card=818)'
CLOSE #2:c=0,e=20,dep=1,type=0,tim=1358483659835664
=====================
PARSING IN CURSOR #3 len=20 dep=0 uid=0 oct=3 lid=0 tim=1358483659836505 hv=720508029 ad='26aab08c' sqlid='gnhzyjcpg453x'
select * from test.t
END OF STMT
EXEC #3:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3557914527,tim=1358483659836501
FETCH #3:c=0,e=90,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=3557914527,tim=1358483659837107
FETCH #3:c=1000,e=205,p=0,cr=11,cu=0,mis=0,r=3,dep=0,og=1,plh=3557914527,tim=1358483659839004
STAT #3 id=1 cnt=4 pid=0 pos=1 obj=0 op='PARTITION RANGE ALL PARTITION: 1 2 (cr=15 pr=0 pw=0 time=0 us cost=4 size=100 card=4)'
STAT #3 id=2 cnt=4 pid=1 pos=1 obj=77127 op='TABLE ACCESS FULL T PARTITION: 1 2 (cr=15 pr=0 pw=0 time=0 us cost=4 size=100 card=4)'
*** 2013-01-18 12:34:28.024
CLOSE #3:c=0,e=22,dep=0,type=0,tim=1358483668024107
=====================
PARSING IN CURSOR #1 len=397 dep=1 uid=0 oct=3 lid=0 tim=1358483668026199 hv=1327856853 ad='26f19118' sqlid='03k8ugj7kaz6p'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(
SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1
AS C1, CASE WHEN "T"."ID"=1 THEN 1 ELSE 0 END AS C2 FROM "TEST"."T" "T" WHERE "T"."ID"=1) SAMPLESUB
END OF STMT
PARSE #1:c=999,e=996,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=2993254470,tim=1358483668026196
EXEC #1:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=2993254470,tim=1358483668026319
FETCH #1:c=0,e=63,p=0,cr=7,cu=0,mis=0,r=1,dep=1,og=1,plh=2993254470,tim=1358483668026405
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=7 pr=0 pw=0 time=0 us)'
STAT #1 id=2 cnt=3 pid=1 pos=1 obj=0 op='PARTITION RANGE SINGLE PARTITION: 1 1 (cr=7 pr=0 pw=0 time=6 us cost=3 size=5317 card=409)'
STAT #1 id=3 cnt=3 pid=2 pos=1 obj=77127 op='TABLE ACCESS FULL T PARTITION: 1 1 (cr=7 pr=0 pw=0 time=2 us cost=3 size=5317 card=409)'
CLOSE #1:c=0,e=9,dep=1,type=0,tim=1358483668026541
=====================
PARSING IN CURSOR #5 len=31 dep=0 uid=0 oct=3 lid=0 tim=1358483668026880 hv=1590420363 ad='27357f8c' sqlid='05j387tgcrswb'
select * from test.t where id=1
END OF STMT
EXEC #5:c=0,e=19,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2931986080,tim=1358483668026877
FETCH #5:c=0,e=46,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=2931986080,tim=1358483668027246
FETCH #5:c=0,e=20,p=0,cr=4,cu=0,mis=0,r=0,dep=0,og=1,plh=2931986080,tim=1358483668028149
STAT #5 id=1 cnt=1 pid=0 pos=1 obj=0 op='PARTITION RANGE SINGLE PARTITION: 1 1 (cr=8 pr=0 pw=0 time=0 us cost=3 size=25 card=1)'
STAT #5 id=2 cnt=1 pid=1 pos=1 obj=77127 op='TABLE ACCESS FULL T PARTITION: 1 1 (cr=8 pr=0 pw=0 time=0 us cost=3 size=25 card=1)'
*** 2013-01-18 12:34:31.185
CLOSE #5:c=0,e=22,dep=0,type=0,tim=1358483671185149
*** 2013-01-18 12:34:33.701
Processing Oradebug command 'setmypid'
*** 2013-01-18 12:34:33.734
Oradebug command 'setmypid' console output:
*** 2013-01-18 12:34:37.724
Processing Oradebug command 'tracefile_name'
*** 2013-01-18 12:34:37.724
Oradebug command 'tracefile_name' console output:
/u01/app/oracle/diag/rdbms/huateng/huateng/trace/huateng_ora_15138.trc
