*************************************************************
1.开启10046
*************************************************************
SQL> oradebug setmypid
Statement processed.
// 激活 10046 事件
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> select count(*) from scott.tblorders;
COUNT(*)
----------
167
// 在当前 session 关闭 10046 事件
SQL> oradebug event 10046 trace name context off;
Statement processed.
// 使用 oradebug tracefile_name 可以直接看到生成的 trace 文件的位置
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2551.trc
*************************************************************
2.直接读取文件
*************************************************************
----------------------trace文件内容
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2551.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: timesten.ocp.com
Release: 2.6.32-431.el6.x86_64
Version: #1 SMP Fri Nov 22 03:15:09 UTC 2013
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 2551, image: oracle@timesten.ocp.com (TNS V1-V3)
*** 2015-02-10 17:34:40.127
*** SESSION ID:(132.9911) 2015-02-10 17:34:40.127
*** CLIENT ID:() 2015-02-10 17:34:40.127
*** SERVICE NAME:(SYS$USERS) 2015-02-10 17:34:40.127
*** MODULE NAME:(sqlplus@timesten.ocp.com (TNS V1-V3)) 2015-02-10 17:34:40.127
*** ACTION NAME:() 2015-02-10 17:34:40.127
Processing Oradebug command 'setmypid'
*** 2015-02-10 17:34:40.127
Oradebug command 'setmypid' console output:
*** 2015-02-10 17:34:48.483
Processing Oradebug command 'event 10046 trace name context forever,level 12'
*** 2015-02-10 17:34:48.484
Oradebug command 'event 10046 trace name context forever,level 12' console output:
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1423560888484461
*** 2015-02-10 17:35:09.044
WAIT #0: nam='SQL*Net message from client' ela= 20559954 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1423560909044435
=====================
PARSING IN CURSOR #140199425457600 len=37 dep=0 uid=0 oct=3 lid=0 tim=1423560909045924 hv=2120411436 ad='28d609c60' sqlid='23uhtxpz65u9c'
select count(*) from scott.tblorders
END OF STMT
PARSE #140199425457600:c=1000,e=1393,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1853518045,tim=1423560909045922
EXEC #140199425457600:c=0,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1853518045,tim=1423560909046084
WAIT #140199425457600: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1423560909046130
FETCH #140199425457600:c=0,e=99,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=1853518045,tim=1423560909046258
STAT #140199425457600 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=1 pr=0 pw=0 time=110 us)'
STAT #140199425457600 id=2 cnt=167 pid=1 pos=1 obj=89045 op='INDEX FULL SCAN PK_ORDERID (cr=1 pr=0 pw=0 time=76 us cost=1 size=0 card=167)'
WAIT #140199425457600: nam='SQL*Net message from client' ela= 148 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1423560909046527
FETCH #1401