查看执行计划（二）
在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)
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博客 ” ，链接：https://blog.itpub.net/26084062/viewspace-1084360/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
