oracle tkprof工具详解 - 数据库编程

---------- ---------- --------------------------------------------------- 1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 1 FAST DUAL ******************************************************************************** SQL ID: 25qnz9xr43bvy Plan Hash: 3383998547 select * from dept call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.05 6 6 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.05 6 6 0 4 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 83 (SCOTT) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 4 4 4 TABLE ACCESS FULL DEPT (cr=6 pr=6 pw=0 time=52995 us cost=3 size=80 card=4) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 4 TABLE ACCESS MODE: ANALYZED (FULL) OF 'DEPT' (TABLE) ******************************************************************************** SQL ID: 3fuwk9jhwd624 Plan Hash: 1973284518 select * from emp where rownum<5 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.01 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 6 6 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.01 6 6 0 4 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 83 (SCOTT) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 4 4 4 COUNT STOPKEY (cr=6 pr=6 pw=0 time=213 us) 4 4 4 TABLE ACCESS FULL EMP (cr=6 pr=6 pw=0 time=208 us cost=2 size=152 card=4) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 4 COUNT (STOPKEY) 4 TABLE ACCESS MODE: ANALYZED (FULL) OF 'EMP' (TABLE) ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 10 0.01 0.02 0 0 0 0 Execute 11 0.00 0.00 0 0 0 6 Fetch 5 0.00 0.05 12 12 0 11 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 26 0.01 0.07 12 12 0 17 Misses in library cache during parse: 3 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 10 0.00 0.00 0 0 0 0 Execute 81 0.02 0.03 0 0 0 0 Fetch 108 0.00 0.00 7 270 0 287 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 199 0.03 0.04 7 270 0 287 Misses in library cache during parse: 4 Misses in library cache during execute: 4 6 user SQL statements in session. 14 internal SQL statements in session. 20 SQL statements in session. 3 statements EXPLAINed in this session. ******************************************************************************** Trace file: RHYS_ora_3191.trc Trace file compatibility: 11.1.0.7 Sort options: default 1 session in tracefile. 6 user SQL statements in trace file. 14 internal SQL statements in trace file. 20 SQL statements in trace file. 18 unique SQL statements in trace file. 3 SQL statements EXPLAINed using schema: SCOTT.prof$plan_table Default table was used. Table was created. Table was dropped. 469 lines in trace file. 22 elapsed seconds in trace file. [oracle@oracle-one trace]$ 查看我执行这个sql语句的整个过程如下： [html] [oracle@oracle-one trace]$ more sql_path.txt select 'x' from dual ; begin :id := sys.dbms_transaction.local_transaction_id; end; / select 'x' from dual ; select * from dept ; select 'x' from dual ; select * from emp where rownum<5 ; [oracle@oracle-one trace]$ 案例二： [html] [oracle@oracle-one trace]$ tkprof RHYS_ora_3191.trc sort_sql_plan.txt sys=yes waits=yes sort=prscpu,execnt explain=scott/root table=scott.plan_table TKPROF: Release 11.2.0.4.0 - Development on Thu Sep 26 23:05:52 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. [oracle@oracle-one trace]$ [oracle@oracle-one trace]$ The following statement encountered a error during parse: select * from rhys.amy_dept PARSE #1399268 Error encountered: ORA-00942 ******************************************************************************** SQL ID: 3fuwk9jhwd624 Plan Hash: 1973284518 select * from emp where rownum<5 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.01 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 6 6 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.01 6 6 0 4 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 83 (SCOTT) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 4 4 4 COUNT STOPKEY (cr=6 pr=6 pw=0 time=213 us) 4 4 4 TABLE ACCESS FULL EMP (cr=6 pr=6 pw=0 time=208 us cost=2 size=152 card=4) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 4 COUNT (STOPKEY) 4 TABLE ACCESS MODE: ANALYZED (FULL) OF 'EMP' (TABLE) ******************************************************************************** SQL ID: 25qnz9xr43bvy Plan Hash: 3383998547 select * from dept call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.05 6 6 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.05 6 6 0 4 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 83 (SCOTT) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 4 4 4 TABLE ACCESS FULL DEPT (cr=6 pr=6 pw=0 time=52995 us cost=3 size=80 card=4) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 4 TABLE ACCESS MODE: ANALYZED (FULL) OF 'DEPT' (TABLE) ******************************************************************************** SQL ID: cf06fwacdmgfk Plan Hash: 1388734953 select 'x' from dual call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 0 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 0 0 3 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.00 0.00 0 0 0 3 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 83 (SCOTT) Number of plan statistics captured: 3 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 1 FAST DUAL ******************************************************************************** SQL ID: 7ng34ruy5awxq Plan Hash: 3992920156 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property, i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey, i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256), i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0), nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null, null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols, min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled >

1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 3 0.01 0.01 0 0 0 0 Fetch 6 0.00 0.00 2 21 0 3 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 10 0.01 0.02 2 21 0 3 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 SORT ORDER BY (cr=6 pr=0 pw=0 time=1055 us cost=6 size=372 card=2) 1 1 1 HASH JOIN OUTER (cr=6 pr=0 pw=0 time=510 us cost=5 size=372 card=2) 1 1 1 NESTED LOOPS OUTER (cr=4 pr=0 pw=0 time=54 us cost=2 size=286 card=2) 1 1 1 TABLE ACCESS CLUSTER IND$ (cr=3 pr=0 pw=0 time=38 us cost=2 size=182 card=2) 1 1 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=16 us cost=1 size=0 card=1)(object id 3) 0 0 0 TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=1 pr=0 pw=0 time=11 us cost=0 size=52 card=1) 0 0 0 INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=1 pr=0 pw=0 time=10 us cost=0 size=0 car d=1)(object id 456) 0 0 0 VIEW (cr=2 pr=0 pw=0 time=45 us cost=3 size=43 card=1) 0 0 0 SORT GROUP BY (cr=2 pr=0 pw=0 time=45 us cost=3 size=15 card=1) 0 0 0 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=2 pr=0 pw=0 time=25 us cost=2 size=15 card =1) 0 0 0 INDEX RANGE SCAN I_CDEF2 (cr=2 pr=0 pw=0 time=25 us cost=1 size=0 card=4)(objec t id 54) ******************************************************************************** SQL ID: 9tgj4g8y4rwy8 Plan Hash: 3755742892 select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts, NVL(lists,65535),NVL(groups,65535),cacheh

oracle tkprof工具详解(三)