Oracle 11gR2开始在不执行SQL语句的情况下可以使用dbms_sqldiag.dump_trace来生成10053跟踪文件。

Oracle 11gR2开始在不执行SQL语句的情况下可以使用dbms_sqldiag.dump_trace来生成10053跟踪文件。它的操作步骤如下:

1.先执行sql语句
SQL> column slq_text format a30
SQL> select sysdate from dual;

SYSDATE
------------
15-AUG-14

2.通过sql语句的文本来搜索v$sql找到该语句相应的sql_id.
SQL> select sql_id from v$sql where sql_text like 'select sysdate from dual%';

SQL_ID
-------------
7h35uxf5uhmm1

3.执行dbms_sqldiag.dump_trace过程来生成10053跟踪文件
SQL> execute dbms_sqldiag.dump_trace(p_sql_id=>'7h35uxf5uhmm1',p_child_number=>0,p_component=>'Compiler',p_file_id=>'DIAG');

PL/SQL procedure successfully completed.

SQL> show parameter user_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/diag/rdbms/jyc
s/jycs/trace

4.找到生成的10053跟踪文件
SQL> host ls -lrt /u01/app/oracle/diag/rdbms/jycs/jycs/trace/*DIAG*.trc
-rw-r----- 1 oracle oinstall 66194 Aug 15 09:49 /u01/app/oracle/diag/rdbms/jycs/jycs/trace/jycs_ora_8474_DIAG.trc

5.查看10053跟踪文件的内容
SQL> host cat /u01/app/oracle/diag/rdbms/jycs/jycs/trace/jycs_ora_8474_DIAG.trc
Trace file /u01/app/oracle/diag/rdbms/jycs/jycs/trace/jycs_ora_8474_DIAG.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/11.2.0/db
System name: Linux
Node name: jyrac1
Release: 2.6.18-1.el5
Version: #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine: x86_
Instance name: jycs
Redo thread mounted by this instance: 1
Oracle process number: 33
Unix process pid: 8474, image: oracle@jyrac1 (TNS V1-V3)

*** 2014-08-15 09:49:11.244
*** SESSION ID:(146.49619) 2014-08-15 09:49:11.244
*** CLIENT ID:() 2014-08-15 09:49:11.244
*** SERVICE NAME:(SYS$USERS) 2014-08-15 09:49:11.244
*** MODULE NAME:(sqlplus@jyrac1 (TNS V1-V3)) 2014-08-15 09:49:11.244
*** ACTION NAME:() 2014-08-15 09:49:11.244

Enabling tracing for cur#=7 sqlid=84zghzsc8b7rj recursive
Parsing cur#=7 sqlid=84zghzsc8b7rj len=50
sql=/* SQL Analyze(146,0) */ select sysdate from dual
End parsing of cur#=7 sqlid=84zghzsc8b7rj
Semantic Analysis cur#=7 sqlid=84zghzsc8b7rj
OPTIMIZER INFORMATION

******************************************
----- Current SQL Statement for this session (sql_id=84zghzsc8b7rj) -----
/* SQL Analyze(146,0) */ select sysdate from dual
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x7f6236e8 145 package body SYS.DBMS_SQLTUNE_INTERNAL
0x7f6236e8 12043 package body SYS.DBMS_SQLTUNE_INTERNAL
0x854a3268 1276 package body SYS.DBMS_SQLDIAG
0x758e9c58 1 anonymous block
*******************************************
................省略
kkfdapdml
oct:3 pgadep:1 pdml mode:0 PX allowed DML allowed RowLock is not Intent
=> not allowed
/* SQL Analyze(146,0) */ select sysdate from dual
Registered qb: SEL$1 0xfb907cb0 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 objn=116 hint_alias="DUAL"@"SEL$1"

SPM: statement not found in SMB
SPM: statement not a candidate for auto-capture
kkfdPaForcePrm return FALSE
kkfdPaPrm: use dictionary DOP(1) on table
kkfdPaPrm:- The table : 116
kkfdPaPrm:DOP = 1 (computed from hint/dictionary/autodop)
kkfdPaPrm:- returns FALSE, i.e (serial)
qksbgCreateSessionEnv: inherit from system bgc:0x2b4afb8f0888
qksbgCreateCursorEnv: create a new one and copy from the session bgc:0x2b4afb90aef0

**************************
Automatic degree of parallelism (ADOP)
**************************
kkfdIsAutoDopSupported: Yes, ctxoct is 3
Automatic degree of parallelism is disabled: Parameter.