本文共 28688 字,大约阅读时间需要 95 分钟。
从Oracle 11gr2开始在不执行SQL语句的情况下可以使用dbms_sqldiag.dump_trace来生成10053跟踪文件。它的操作步骤如下:
1.先执行sql语句
SQL> column slq_text format a30 SQL> select sysdate from dual;SYSDATE
------------ 15-AUG-142.通过sql语句的文本来搜索v$sql找到该语句相应的sql_id.
SQL> select sql_id from v$sql where sql_text like 'select sysdate from dual%';SQL_ID
------------- 7h35uxf5uhmm13.执行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/trace4.找到生成的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.trc5.查看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: 0kkfdPaForcePrm 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 dumpuser_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%' 0wu4zyqk0jkg2SELECT 3.142*:B1 *:B1 FROM DUAL
9rjmrhbjuasav5.清空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.2PL/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.trc11.查看跟踪文件 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=10Final 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/26015009/viewspace-1252230/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26015009/viewspace-1252230/