oracle 11gr2中使用dbms_sqldiag.dump_trace來獲得10053跟蹤檔案

dbhelper發表於2014-11-27

從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: (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:( (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 name=SEL$1
FROM position=40
end of FROM position=49
START position=25
END position=49
SELECT (qbcsel)
FROM (qbcfro)
WHERE ) (qbcwhr)
GROUP BY ) (qbcgbh)
HAVING ) (qbchav)
ORDER BY ) (qbcord)
Outer Query ) (qbcoqb)
Inner Query ) (qbciqb)
Next Query ) (qbcnxt)
View Query ) (qbcvqb)
Set Query ) (qbcseb)
Set Q.B. ) (qbcsep)
qbcflg=0x40400
qbcxfl=0x0
qbcxxfl=0x0
qbcxxxfl=0x0
seldef name=SYSDATE
name=SYSDATE
flags=0x11
end position=40
select
next )
opndef type=base operand [3]
position=32
opnflg=0x30040
opnxfl=0x0
opnflg2=0x0
strtyp=SYSDATE
frodef alias=DUAL
alias=DUAL
table=DUAL
next ) (fronxt)
containing q.b. (froqbc)=@0x2b4afb907cb0
view q.b. (frovqb)=@(nil)
outer join ) (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:    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 (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 ======================


這種方法它是事件基礎框架的一部分,它有先天的優勢能捕獲包含在PL/SQL塊中的SQL語句。下面顯示了一個例子。
1.建立包的定義
SQL> create or replace package getcircarea as
  2  function getcircarea(radius number)
  3  return number;
  4  end getcircarea;
  5  /

Package created
2.建立包體
SQL> create or replace package body getcircarea as
  2  function getcircarea (radius number) return number
  3  is area number(8,2);
  4  begin
  5  select 3.142*radius*radius into area from dual;
  6  return area;
  7  end;
  8  end getcircarea;
  9  /

Package body created

3.呼叫過程
SQL> set serveroutput on size 100000;
SQL> declare
  2  area number(8,2);
  3  begin
  4  area:= getcircarea.getcircarea(10);
  5  dbms_output.put_line('Area is '||area);
  6  end;
  7  /

Area is 314.2

PL/SQL procedure successfully completed

4.查詢PL/SQL中特定語句的sql_id
SQL>  select sql_text, sql_id from v$sqlarea where sql_text like '%3.142%';

SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID
-------------
 select sql_text, sql_id from v$sqlarea where sql_text like '%3.142%'
0wu4zyqk0jkg2

SELECT 3.142*:B1 *:B1 FROM DUAL
9rjmrhbjuasav

5.清空shared_pool(備註:必須flush shared pool,否則trace不會生成)
SQL> alter system flush shared_pool;

System altered.

6.設定跟蹤會話識別符號
SQL> alter session set tracefile_identifier='PLSQL';

Session altered.
--也可以oradebug
--oradebug event trace[rdbms.SQL_Optimizer.*][sql:9rjmrhbjuasav]
7.開啟跟蹤
SQL> alter session set events 'trace[rdbms.SQL_Optimizer.*][sql:9rjmrhbjuasav]';

Session altered.

8.再次呼叫 getcircarea.getcircarea過程
SQL> set serveroutput on size 100000;
SQL> declare
  2  area number(8,2);
  3  begin
  4  area:= getcircarea.getcircarea(10);
  5  dbms_output.put_line('Area is '||area);
  6  end;
  7  /
Area is 314.2

PL/SQL procedure successfully completed.

9.關閉跟蹤
SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*]off';

Session altered.
10. 查詢生成跟蹤檔案的位置
SQL> select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/jycs/jycs/trace/jycs_ora_12089_PLSQL.trc


11.檢視跟蹤檔案
SQL> host cat /u01/app/oracle/diag/rdbms/jycs/jycs/trace/jycs_ora_12089_PLSQL.trc
Trace file /u01/app/oracle/diag/rdbms/jycs/jycs/trace/jycs_ora_12089_PLSQL.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: 21
Unix process pid: 12089, image: (TNS V1-V3)


*** 2014-08-15 10:47:46.809
*** SESSION ID:(146.49829) 2014-08-15 10:47:46.809
*** CLIENT ID:() 2014-08-15 10:47:46.809
*** SERVICE NAME:(SYS$USERS) 2014-08-15 10:47:46.809
*** MODULE NAME:( (TNS V1-V3)) 2014-08-15 10:47:46.809
*** ACTION NAME:() 2014-08-15 10:47:46.809

Registered qb: SEL$1 0xb0a521f0 (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

**************************
Automatic degree of parallelism (ADOP)
**************************
Automatic degree of parallelism is disabled: Parameter.

PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION

******************************************
----- Current SQL Statement for this session (sql_id=9rjmrhbjuasav) -----
SELECT 3.142*:B1 *:B1 FROM DUAL
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x7f525220         5  package body SYS.GETCIRCAREA
0x7f553b20         4  anonymous block
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
OST - old style star transformation
ST - new (cbqt) star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
JF - join factorization
SLP - select list pruning
DP - distinct placement
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUSPEED - CPU Speed
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
  1: no partitioning required
  2: value partitioned
  4: right is random (round-robin)
  128: left is random (round-robin)
  8: broadcast right and partition left
  16: broadcast left and partition right
  32: partition left using partitioning of right
  64: partition right using partitioning of left
  256: run the join in serial
  0: invalid distribution method
sel - selectivity
ptn - partition
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
Compilation Environment Dump
is_recur_flags                      = 128
Bug Fix Control Environment

..........省略
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 9rjmrhbjuasav.
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 9rjmrhbjuasav.
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
FPD: Considering simple filter push in query block SEL$1 (#0)
 ??
apadrv-start sqlid=11224790525316260187
  :
    call(in-use=1136, alloc=16344), compile(in-use=53272, alloc=58176), execution(in-use=3336, alloc=4032)

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
 Bind#0
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=2b8cb0aa3880  bln=22  avl=02  flg=05
  value=10
 Bind#1
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=2b8cb0aa3898  bln=22  avl=02  flg=01
  value=10

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

        :
    call(in-use=1136, alloc=16344), compile(in-use=54200, alloc=58176), execution(in-use=3512, alloc=4032)

kkoqbc-subheap (create addr=0x2b8cb0a5b698)
****************
QUERY BLOCK TEXT
****************
SELECT 3.142*:B1 *:B1 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
Access path analysis for DUAL
***************************************
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

***************************************


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

*********************************
Number of join permutations tried: 1
*********************************
Trying or-Expansion on query block SEL$1 (#0)
Transfer Optimizer annotations for query block SEL$1 (#0)
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=0x2b8cb0a5b698, in-use=11112, alloc=14296)
kkoqbc-end:
        :
    call(in-use=6400, alloc=32712), compile(in-use=54760, alloc=58176), execution(in-use=3512, alloc=4032)

kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
          :
    call(in-use=6400, alloc=32712), compile(in-use=55672, alloc=58176), execution(in-use=3512, alloc=4032)


Starting SQL statement dump

user_id=0 user_name=SYS (TNS V1-V3) action=
sql_id=9rjmrhbjuasav plan_hash_value=1388734953 problem_type=3
----- Current SQL Statement for this session (sql_id=9rjmrhbjuasav) -----
SELECT 3.142*:B1 *:B1 FROM DUAL
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x7f525220         5  package body SYS.GETCIRCAREA
0x7f553b20         4  anonymous block
sql_text_length=32
sql=SELECT 3.142*:B1 *:B1 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 0xb0a521f0 (PARSER) [FINAL]

:
    call(in-use=8672, alloc=32712), compile(in-use=80744, alloc=144008), execution(in-use=4712, alloc=8088)

End of Optimizer State Dump
Dumping Hints
=============
====================== END SQL Statement Dump ======================

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8494287/viewspace-1349435/,如需轉載,請註明出處,否則將追究法律責任。

相關文章