Use dbms_sqldiag.dump_trace in Oracle11gR2 to obtain the 10053 trace file.

When Oracle11gR2 does not execute SQL statements, you can use dbms_sqldiag.dump_trace to generate the 10053 trace file.

When Oracle 11gR2 does not execute SQL statements, you can use dbms_sqldiag.dump_trace to generate the 10053 trace file.

When Oracle 11gR2 does not execute SQL statements, you can use dbms_sqldiag.dump_trace to generate the 10053 trace file. The procedure is as follows:

1. Execute the SQL statement first
SQL> column slq_text format a30
SQL> select sysdate from dual;

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

2. Search for v $ SQL using the SQL statement text to find the corresponding SQL _id of the statement.
SQL> select SQL _id from v $ SQL where SQL _text like 'select sysdate from dual % ';

SQL _ID
-------------
7h35uxf5uhmm1

3. Run the dbms_sqldiag.dump_trace process to generate the 10053 trace file.
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. Find the generated 10053 trace file
SQL> host ls-lrt/u01/app/oracle/diag/rdbms/jycs/trace/* DIAG *. trc
-Rw-r ----- 1 oracle oinstall 66194 Aug 15/u01/app/oracle/diag/rdbms/jycs/trace/jycs_ora_8474_DIAG.trc

5. view the content of the 10053 tracking File
SQL> host cat/u01/app/oracle/diag/rdbms/jycs/trace/jycs_ora_8474_DIAG.trc
Trace file/u01/app/oracle/diag/rdbms/jycs/trace/jycs_ora_8474_DIAG.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit 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-164. el5
Version: #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine: x86_64
Instance name: jycs
Redo thread mounted by this instance: 1
Oracle process number: 33
Unix process pid: 8474, image: oracle @ jyrac1 (TNS V1-V3)

* ** 09:49:11. 244
* ** Session id: (146.49619) 09:49:11. 244
* ** Client id: () 09:49:11. 244
* ** Service name :( SYS $ USERS) 09:49:11. 244
* ** Module name :( sqlplus @ jyrac1 (TNS V1-V3) 09:49:11. 244
* ** Action name :() 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
**************************************** ***
...... Omitted
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.