关于oracle执行计划的概念，参考之前的博客：http://blog.csdn.net/cymm_liu/article/details/7996599
如果要分析某条SQL的性能问题，通常我们要先看SQL的执行计划，看看SQL的每一步执行是否存在问题。 如果一条SQL平时执行的好好的，却有一天突然性能很差，如果排除了系统资源和阻塞的原因，那么基本可以断定是执行计划出了问题。
看懂执行计划也就成了SQL优化的先决条件。 这里的SQL优化指的是SQL性能问题的定位，定位后就可以解决问题。
一． 查看执行计划的5种方法
1.1 设置autotrace
| 序号 |
命令 |
解释 |
| 1 |
SET AUTOTRACE OFF |
此为默认值，即关闭Autotrace |
| 2 |
SET AUTOTRACE ON EXPLAIN |
只显示执行计划 |
| 3 |
SET AUTOTRACE ON STATISTICS |
只显示执行的统计信息 |
| 4 |
SET AUTOTRACE ON |
包含2,3两项内容 |
| 5 |
SET AUTOTRACE TRACEONLY |
与ON相似，但不显示语句的执行结果 |
SQL> set autotrace on
SQL> SET AUTOTRACE TRACEONLY;SQL> select empno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno;
14 rows selected. Execution Plan ---------------------------------------------------------- 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("EMP"."DEPTNO"="DEPT"."DEPTNO") filter("EMP"."DEPTNO"="DEPT"."DEPTNO") Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 10 consistent gets 0 physical reads 0 redo size 941 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 14 rows processed
1.2 使用SQL
SQL>EXPLAIN PLAN FOR sql语句;
SQL>SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
示例：
SQL> EXPLAIN PLAN FOR SELECT * FROM dual;
已解释。
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
或者：
SQL> select * from table(dbms_xplan.display);
1.3 oradebug
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12; ——打开10046
Statement processed.
SQL> select empno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.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 rows selected.
SQL> oradebug tracefile_name; ——查看跟踪文件名字
/u01/app/oracle/diag/rdbms/test/test/trace/test_ora_17425.trc
SQL> oradebug event 10046 trace name context off; ——关闭跟踪事件
Statement processed.
现在，根据查询出的跟踪文件名字，去查看执行计划：
需要对trace文件进行格式化，方便我们查看，使用tkprof：
[oracle@lyg ~]$ tkprof /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_17425.trc output= ./exptest/test_ora_17425.trc
TKPROF: Release 11.2.0.3.0 - Development on Wed Mar 26 11:27:52 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[oracle@lyg ~]$ cd exptest/
[oracle@lyg exptest]$ ls
compressfile expfull.log exptest.sh pxe.sh test.dmp.gz test_ora_17425.trc
[oracle@lyg exptest]$ vi test_ora_17425.trc （附上全部内容）
TKPROF: Release 11.2.0.3.0 - Development on Wed Mar 26 11:27:52 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_17425.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: 3yfu3wh150aqt Plan Hash: 844388907
select empno,ename,dname
from
scott.emp,scott.dept where emp.deptno=dept.deptno
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 2 0.00 0.00 0 10 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 10 0 14
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
14 14 14 MERGE JOIN (cr=10 pr=0 pw=0 time=520 us cost=6 size=364 card=14)
4 4 4 TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=0 pw=0 time=329 us cost=2 size=52 card=4)
4 4 4 INDEX FULL SCAN PK_DEPT (cr=2 pr=0 pw=0 time=173 us cost=1 size=0 card=4)(object id 75334)
14 14 14 SORT JOIN (cr=6 pr=0 pw=0 time=242 us cost=4 size=182 card=14)
14 14 14 TABLE ACCESS FULL EMP (cr=6 pr=0 pw=0 time=170 us cost=3 size=182 card=14)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 11.50 11.50
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
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 2 0.00 0.00 0 10 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 10 0 14
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 12.79 33.45
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
1 user SQL statements in session.
0 internal SQL statements in session.
1 SQL statements in session.
********************************************************************************
Trace file: /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_17425.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
1 user SQL statements in trace file.
0 internal SQL statements in trace file.
1 SQL statements in trace file.
1 unique SQL statements in trace file.
86 lines in trace file.
0 elapsed seconds in trace file.
1.4 通过hash_value,child_number查看执行过的sql语句的执行计划
SQL> col sql_text format a30;
SQL> select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like '%select empno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno%';
SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER
------------------------------ ------------- ---------- ------------
select empno,ename,dname from 088zzpvnu02r4 3919579876 0
scott.emp,scott.dept where emp
.deptno=dept.deptno
select empno,ename,dname from 3yfu3wh150aqt 38808281 0
scott.emp,scott.dept where emp
.deptno=dept.deptno
select empno,ename,dname from 3yfu3wh150aqt 38808281 1
scott.emp,scott.dept where emp
.deptno=dept.deptno
3 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(3919579876,0,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
HASH_VALUE 3919579876, child number 0
--------------------------------------
select empno,ename,dname from scott.emp,scott.dept where
emp.deptno=dept.deptno
Plan hash value: 844388907
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | MERGE JOIN | | 14 | 364 | 6 (17)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 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 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
3 - SEL$1 / DEPT@SEL$1
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
5 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
FULL(@"SEL$1" "EMP"@"SEL$1")
LEADING(@"SEL$1" "DEPT"@"SEL$1" "EMP"@"SEL$1")
USE_MERGE(@"SEL$1" "EMP"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "DNAME"[VARCHAR2,14], "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
2 - "DEPT"."DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14]
3 - "DEPT".ROWID[ROWID,10], "DEPT"."DEPTNO"[NUMBER,22]
4 - (#keys=1) "EMP"."DEPTNO"[NUMBER,22], "EMPNO"[NUMBER,22],
"ENAME"[VARCHAR2,10]
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
5 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "EMP"."DEPTNO"[NUMBER,22]
59 rows selected.
SQL>
1.5 使用Toad,PL/SQL Developer工具
图片是Toad工具查看的执行计划。 在Toad 里面，很清楚的显示了执行的顺序。 但是如果在SQLPLUS里面就不是那么直接。 但我们也可以判断：一般按缩进长度来判断，缩进最大的最先执行，如果有2行缩进一样，那么就先执行上面的。
