Oracle 11gR2中使用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.

PM: Considering predicate move-around in query block SEL$1 (#0)
 **************************
 Predicate Move-Around (PM)
 **************************
 Considering Query Transformations on query block SEL$1 (#0)
 **************************
 Query transformations (QT)
 **************************
 JF: Checking validity of join factorization for query block SEL$1 (#0)
 JF: Bypassed: not a UNION or UNION-ALL query block.
 ST: not valid since star transformation parameter is FALSE
 TE: Checking validity of table expansion for query block SEL$1 (#0)
 TE: Bypassed: No partitioned table in query block.
 CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
 CBQT: Validity checks failed for 84zghzsc8b7rj.
 CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
 *************************
 Common Subexpression elimination (CSE)
 *************************
 CSE:    CSE not performed on query block SEL$1 (#0).
 OBYE:  Considering Order-by Elimination from view SEL$1 (#0)
 ***************************
 Order-by elimination (OBYE)
 ***************************
 OBYE:    OBYE bypassed: no order by to eliminate.
 CVM: Considering view merge in query block SEL$1 (#0)
 query block SEL$1 (#0) unchanged
 Considering Query Transformations on query block SEL$1 (#0)
 **************************
 Query transformations (QT)
 **************************
 JF: Checking validity of join factorization for query block SEL$1 (#0)
 JF: Bypassed: not a UNION or UNION-ALL query block.
 ST: not valid since star transformation parameter is FALSE
 TE: Checking validity of table expansion for query block SEL$1 (#0)
 TE: Bypassed: No partitioned table in query block.
 CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
 CBQT: Validity checks failed for 84zghzsc8b7rj.
 CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
 *************************
 Common Subexpression elimination (CSE)
 *************************
 CSE:    CSE not performed on query block SEL$1 (#0).
 SU: Considering subquery unnesting in query block SEL$1 (#0)
 ********************
 Subquery Unnest (SU)
 ********************
 SJC: Considering set-join conversion in query block SEL$1 (#0)
 *************************
 Set-Join Conversion (SJC)
 *************************
 SJC: not performed
 PM: Considering predicate move-around in query block SEL$1 (#0)
 **************************
 Predicate Move-Around (PM)
 **************************
 PM:    PM bypassed: Outer query contains no views.
 PM:    PM bypassed: Outer query contains no views.
 query block SEL$1 (#0) unchanged
 End Semantic analysis of cur#=7 sqlid=84zghzsc8b7rj
 Typechecking cur#=7 sqlid=84zghzsc8b7rj
 FPD: Considering simple filter push in query block SEL$1 (#0)
  ??
 apadrv-start sqlid=9402936571143233265
  :
    call(in-use=1008, alloc=16344), compile(in-use=53512, alloc=54384), execution(in-use=2424, alloc=4032)
 
*******************************************
 Peeked values of the binds in SQL statement
 *******************************************

Final query after transformations:******* UNPARSED QUERY IS *******
 SELECT SYSDATE@! "SYSDATE" FROM "SYS"."DUAL" "DUAL"
 kkoqbc: optimizing query block SEL$1 (#0)

        :
    call(in-use=1008, alloc=16344), compile(in-use=54576, alloc=56336), execution(in-use=2424, alloc=4032)
 
kkoqbc-subheap (create addr=0x2b4afb8cfb08)
 ****************
 QUERY BLOCK TEXT
 ****************
 select sysdate from dual
 ---------------------
 QUERY BLOCK SIGNATURE
 ---------------------
 signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=0 objn=116 hint_alias="DUAL"@"SEL$1"

-----------------------------
 SYSTEM STATISTICS INFORMATION
 -----------------------------
  Using NOWORKLOAD Stats
  CPUSPEEDNW: 2657 millions instructions/sec (default is 100)
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
  MBRC: -1 blocks (default is 8)

***************************************
 BASE STATISTICAL INFORMATION
 ***********************
 Table Stats::
  Table: DUAL  Alias: DUAL
    #Rows: 1  #Blks:  1  AvgRowLen:  2.00
 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)
 Access path analysis for DUAL
        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)
 ***************************************
 SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for DUAL[DUAL]
  Table: DUAL  Alias: DUAL
    Card: Original: 1.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  Access Path: TableScan
    Cost:  2.00  Resp: 2.00  Degree: 0
      Cost_io: 2.00  Cost_cpu: 7271
      Resp_io: 2.00  Resp_cpu: 7271
  Best:: AccessPath: TableScan
          Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.00  Bytes: 0

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

OPTIMIZER STATISTICS AND COMPUTATIONS
 ***************************************
 GENERAL PLANS
 ***************************************
 Considering cardinality-based initial join order.
 Permutations for Starting Table :0
 Join order[1]:  DUAL[DUAL]#0
        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)
 ***********************
 Best so far:  Table#: 0  cost: 2.0002  card: 1.0000  bytes: 0
 ***********************
        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)
 (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000

*********************************
 Number of join permutations tried: 1
 *********************************
        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)
        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)
 Trying or-Expansion on query block SEL$1 (#0)
 Transfer Optimizer annotations for query block SEL$1 (#0)
        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)
        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)
 Final cost for query block SEL$1 (#0) - All Rows Plan:
  Best join order: 1
  Cost: 2.0002  Degree: 1  Card: 1.0000  Bytes: 0
  Resc: 2.0002  Resc_io: 2.0000  Resc_cpu: 7271
  Resp: 2.0002  Resp_io: 2.0000  Resc_cpu: 7271
 kkoqbc-subheap (delete addr=0x2b4afb8cfb08, in-use=11112, alloc=14424)
 kkoqbc-end:
        :
    call(in-use=6272, alloc=32712), compile(in-use=55136, alloc=56336), execution(in-use=2424, alloc=4032)
 
kkoqbc: finish optimizing query block SEL$1 (#0)
 apadrv-end
          :
    call(in-use=6272, alloc=32712), compile(in-use=56080, alloc=56336), execution(in-use=2424, alloc=4032)
 

Code generation for query block SEL$1 (#0)
 qksqbDumpQbcdef() dumping query block tree sqlid=84zghzsc8b7rj
 qbcdef qbcp=@0x2b4afb907cb0 name=SEL$1
 FROM position=40
 end of FROM position=49
 START position=25
 END position=49
 SELECT clause=@0x2b4afb903080 (qbcsel)
 FROM clause=@0x2b4afb902d88 (qbcfro)
 WHERE clause=@(nil) (qbcwhr)
 GROUP BY clause=@(nil) (qbcgbh)
 HAVING clause=@(nil) (qbchav)
 ORDER BY clause=@(nil) (qbcord)
 Outer Query Block=@(nil) (qbcoqb)
 Inner Query Block=@(nil) (qbciqb)
 Next Query Block=@(nil) (qbcnxt)
 View Query Block=@(nil) (qbcvqb)
 Set Query Block=@(nil) (qbcseb)
 Set Q.B. Parent=@(nil) (qbcsep)
 qbcflg=0x40400
 qbcxfl=0x0
 qbcxxfl=0x0
 qbcxxxfl=0x0
 seldef selp=@0x2b4afb903080 name=SYSDATE
 name=SYSDATE
 flags=0x11
 end position=40
 select operand=@0x2b4afb903010
 next element=@(nil)
 opndef opnp=@0x2b4afb903010 type=base operand [3]
 position=32
 opnflg=0x30040
 opnxfl=0x0
 opnflg2=0x0
 strtyp=SYSDATE
 frodef frop=@0x2b4afb902d88 alias=DUAL
 alias=DUAL
 table=DUAL
 next pointer=@(nil) (fronxt)
 containing q.b. (froqbc)=@0x2b4afb907cb0
 view q.b. (frovqb)=@(nil)
 outer join to=@(nil) (frooutj)
 flags, flags, and more flags
 froflg=0x43
 froxfl=0x100
 froxxfl=0x40000000
 froxxxfl=0x0
 fro4xfl=0x0
 Code generation for table DUAL[DUAL] using frokmode:23
 qknAllocate
        Allocate FAST_DUAL_QKNTYP(0x2b4afb91adf0 rwo:0x2b4afb91aea8)

        []
 qkatab: froqkn:0x2b4afb91adf0 fro:DUAL
 frorwo:
        []
 froqkn:
  FAST_DUAL (0x2b4afb91adf0)

Query block SEL$1 (#0) processed
 Traversing query block SEL$1 (#0) because of (14)
  FAST_DUAL (0x2b4afb91adf0)
 Query block SEL$1 (#0) traversed
 Traversing query block SEL$1 (#0) because of (3)
  FAST_DUAL (0x2b4afb91adf0)
 Query block SEL$1 (#0) traversed
 qkaMarkQkn: -> FAST_DUAL_QKNTYP(0x2b4afb91adf0)
            flags_in:
 qkaMarkQkn: <- FAST_DUAL_QKNTYP(0x2b4afb91adf0)
    out:
    out_left:
    out_right:
 Traversing query block SEL$1 (#0) because of (6)
  FAST_DUAL (0x2b4afb91adf0)
 Query block SEL$1 (#0) traversed
 Traversing query block SEL$1 (#0) because of (12)
  FAST_DUAL (0x2b4afb91adf0)
 Query block SEL$1 (#0) traversed
 Traversing query block SEL$1 (#0) because of (5)
  FAST_DUAL (0x2b4afb91adf0)
 Query block SEL$1 (#0) traversed
 qknAllocate
        Allocate STMT_MARKER_QKNTYP(0x2b4afb919fb8 rwo:(nil)) on top of FAST_DUA
        L_QKNTYP(0x2b4afb91adf0)
 qkenndfs: node 0x2b4afb91adf0(dnum_qkn  1) of type FAST_DUAL_QKNTYP              exprs_qkn 2b4afb91ae30
 qkenndfs: node 0x2b4afb919fb8(dnum_qkn  2) of type STMT_MARKER_QKNTYP            exprs_qkn 2b4afb919ff8
 **** qkeDumpExpressionScopes expression scopes ****
 Expression:
        [(0x2b4afb903010:8:SYSDATE@!)]
 Defined by  : Node STMT_MARKER_QKNTYP        (dnum_qkn  2) type QKE_REF  dob  1
 Referenced by: Node STMT_MARKER_QKNTYP        (dnum_qkn  2)
 ********** End of qkeDumpExpressionScopes *********
 Traversing query block SEL$1 (#0) because of (4)
  FAST_DUAL (0x2b4afb91adf0)
 Query block SEL$1 (#0) traversed
 Traversing query block SEL$1 (#0) because of (1)
  FAST_DUAL (0x2b4afb91adf0)
 Query block SEL$1 (#0) traversed
 Traversing query block SEL$1 (#0) because of (5)
 qkaPlanSignatureCB
        node : 0x2b4afb91adf0, node->type_qkn #: 66,node->exprs: (nil), node->dn
        _qkn: (nil), dn->kkfdntyp: 0
  FAST_DUAL (0x2b4afb91adf0)
 Query block SEL$1 (#0) traversed
 Starting SQL statement dump

user_id=0 user_name=SYS module=sqlplus@jyrac1 (TNS V1-V3) action=
 sql_id=84zghzsc8b7rj plan_hash_value=1388734953 problem_type=3
 ----- 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
 sql_text_length=50
 sql=/* SQL Analyze(146,0) */ select sysdate from dual
 ----- Explain Plan Dump -----
 ----- Plan Table -----

============
 Plan Table
 ============
 ------------------------------------+-----------------------------------+
 | Id  | Operation        | Name    | Rows  | Bytes | Cost  | Time      |
 ------------------------------------+-----------------------------------+
 | 0  | SELECT STATEMENT  |        |      |      |    2 |          |
 | 1  |  FAST DUAL        |        |    1 |      |    2 |  00:00:01 |
 ------------------------------------+-----------------------------------+
 Predicate Information:
 ----------------------

Content of other_xml column
 ===========================
  db_version    : 11.2.0.1
  parse_schema  : SYS
  plan_hash      : 1388734953
  plan_hash_2    : 308129442
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
    END_OUTLINE_DATA
  */

..........省略

Query Block Registry:
 SEL$1 0xfb907cb0 (PARSER) [FINAL]

:
    call(in-use=9216, alloc=32712), compile(in-use=79040, alloc=141816), execution(in-use=3600, alloc=4032)
 
End of Optimizer State Dump
 Dumping Hints
 =============
 ====================== END SQL Statement Dump ======================