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

Source: Internet
Author: User
Tags sql using
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.