查看执行计划（二）

在SQLPLUS中使用AUTOTRACE，这也是很多人最喜欢用的
SQL> set autotrace on--显示SQL的执行结果，显示执行计划和统计信息
SQL> select empno,ename,dname from scott.emp t11,scott.dept t12 where t11.deptno=t12.deptno;


     EMPNO ENAME      DNAME
---------- ---------- --------------
      7782 CLARK      ACCOUNTING
      7839 KING       ACCOUNTING
      7934 MILLER     ACCOUNTING
      7566 JONES      RESEARCH
      7902 FORD       RESEARCH
      7876 ADAMS      RESEARCH
      7369 SMITH      RESEARCH
      7788 SCOTT      RESEARCH
      7521 WARD       SALES
      7844 TURNER     SALES
      7499 ALLEN      SALES


     EMPNO ENAME      DNAME
---------- ---------- --------------
      7900 JAMES      SALES
      7698 BLAKE      SALES
      7654 MARTIN     SALES


已选择14行。




执行计划
----------------------------------------------------------
Plan hash value: 844388907


----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   364 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   364 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   182 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   4 - access("T11"."DEPTNO"="T12"."DEPTNO")
       filter("T11"."DEPTNO"="T12"."DEPTNO")




统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        710  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed






SQL> set autotrace traceonly--不显示SQL的执行结果，显示执行计划和统计信息
SQL> select empno,ename,dname from scott.emp t11,scott.dept t12 where t11.deptno=t12.deptno;


已选择14行。




执行计划
----------------------------------------------------------
Plan hash value: 844388907


----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   364 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   364 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   182 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   4 - access("T11"."DEPTNO"="T12"."DEPTNO")
       filter("T11"."DEPTNO"="T12"."DEPTNO")




统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        710  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed






SQL> set autotrace traceonly explain--只显示执行计划
SQL> select empno,ename,dname from scott.emp t11,scott.dept t12 where t11.deptno=t12.deptno;


执行计划
----------------------------------------------------------
Plan hash value: 844388907


----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   364 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   364 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   182 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   4 - access("T11"."DEPTNO"="T12"."DEPTNO")
       filter("T11"."DEPTNO"="T12"."DEPTNO")






SQL> set autotrace traceonly statistics--只显示统计信息
SQL> select empno,ename,dname from scott.emp t11,scott.dept t12 where t11.deptno=t12.deptno;


已选择14行。




统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        710  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed






10046 event与tkprof
SQL> oradebug setmypid
已处理的语句
SQL> oradebug event 10046 trace name context forever,level 12--在当前session激活10046事件
已处理的语句
SQL> select empno,ename,dname from scott.emp t11,scott.dept t12 where t11.deptno=t12.deptno;


     EMPNO ENAME      DNAME
---------- ---------- --------------
      7782 CLARK      ACCOUNTING
      7839 KING       ACCOUNTING
      7934 MILLER     ACCOUNTING
      7566 JONES      RESEARCH
      7902 FORD       RESEARCH
      7876 ADAMS      RESEARCH
      7369 SMITH      RESEARCH
      7788 SCOTT      RESEARCH
      7521 WARD       SALES
      7844 TURNER     SALES
      7499 ALLEN      SALES


     EMPNO ENAME      DNAME
---------- ---------- --------------
      7900 JAMES      SALES
      7698 BLAKE      SALES
      7654 MARTIN     SALES


已选择14行。


SQL> oradebug tracefile_name
e:\app\lenovo\diag\rdbms\orcl\orcl\trace\orcl_ora_3876.trc--这个文件中显示SQL的执行计划和资源消耗
SQL> oradebug event 10046 trace name context off--在当前session关闭10046事件
已处理的语句


--看看这个trace文件，orcl_ora_3876.trc
*** 2014-02-20 09:25:34.631
WAIT #0: nam='SQL*Net message from client' ela= 1376298 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2936765232
=====================
PARSING IN CURSOR #1 len=86 dep=0 uid=0 oct=3 lid=0 tim=2936765402 hv=438155893 ad='7ffb2edaa730' sqlid='fvp57hhd1vfmp'
select empno,ename,dname from scott.emp t11,scott.dept t12 where t11.deptno=t12.deptno
END OF STMT
PARSE #1:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=844388907,tim=2936765402
EXEC #1:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=844388907,tim=2936765509
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2936765536
WAIT #1: nam='Disk file operations I/O' ela= 314 FileOperation=2 fileno=4 filetype=2 obj#=-1 tim=2936765920
FETCH #1:c=0,e=451,p=0,cr=9,cu=0,mis=0,r=1,dep=0,og=1,plh=844388907,tim=2936766012
WAIT #1: nam='SQL*Net message from client' ela= 246 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2936766311
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2936766349
FETCH #1:c=0,e=40,p=0,cr=2,cu=0,mis=0,r=13,dep=0,og=1,plh=844388907,tim=2936766384
STAT #1 id=1 cnt=14 pid=0 pos=1 obj=0 op='MERGE JOIN  (cr=11 pr=0 pw=0 time=0 us cost=6 size=364 card=14)'
STAT #1 id=2 cnt=4 pid=1 pos=1 obj=73194 op='TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=0 pw=0 time=12 us cost=2 size=52 card=4)'
--cr 逻辑读(consistent read) 4
--pr 物力读(physical read) 0
--time 12us
--card (cardinality)返回的结果集 4
STAT #1 id=3 cnt=4 pid=2 pos=1 obj=73195 op='INDEX FULL SCAN PK_DEPT (cr=2 pr=0 pw=0 time=9 us cost=1 size=0 card=4)'
STAT #1 id=4 cnt=14 pid=1 pos=2 obj=0 op='SORT JOIN (cr=7 pr=0 pw=0 time=0 us cost=4 size=182 card=14)'
STAT #1 id=5 cnt=14 pid=4 pos=1 obj=73196 op='TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=0 us cost=3 size=182 card=14)'


--使用tkprof看一下，效果会更直观
C:\Users\lenovo>tkprof e:\app\lenovo\diag\rdbms\orcl\orcl\trace\orcl_ora_3876.trc e:\orcl_ora_3876.trc


TKPROF: Release 11.2.0.1.0 - Development on 星期四 2月 20 09:33:39 2014


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.






SQL ID: fvp57hhd1vfmp
Plan Hash: 844388907
select empno,ename,dname 
from
 scott.emp t11,scott.dept t12 where t11.deptno=t12.deptno




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        2      0.00       0.00          0         11          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         11          0          14


Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS


Rows     Row Source Operation
-------  ---------------------------------------------------
     14  MERGE JOIN  (cr=11 pr=0 pw=0 time=0 us cost=6 size=364 card=14)
      4   TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=0 pw=0 time=12 us cost=2 size=52 card=4)
      4    INDEX FULL SCAN PK_DEPT (cr=2 pr=0 pw=0 time=9 us cost=1 size=0 card=4)(object id 73195)
     14   SORT JOIN (cr=7 pr=0 pw=0 time=0 us cost=4 size=182 card=14)
     14    TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=0 us cost=3 size=182 card=14)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26084062/viewspace-1084360/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26084062/viewspace-1084360/