Oracle 11gR2中使用dbms_sqldiag.dump_trace来获得10053跟踪文件

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 - 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)

*** 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.