关于oracle执行计划的概念，参考之前的博客：/cymm_liu/article/details/7996599
如果要分析某条SQL的性能问题，通常我们要先看SQL的执行计划，看看SQL的每一步执行是否存在问题。如果一条SQL平时执行的好好的，却有一天突然性能很差，如果排除了系统资源和阻塞的原因，那么基本可以断定是执行计划出了问题。
看懂执行计划也就成了SQL优化的先决条件。这里的SQL优化指的是SQL性能问题的定位，定位后就可以解决问题。
一．查看执行计划的5种方法
1.1设置autotrace
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 calls0 db block gets10 consistent gets0 physical reads0 redo size941 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client1 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.3oradebug
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
Copyright (c) 1982, , 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.gztest_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
Copyright (c) 1982, , 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.010000
Execute 1 0.00 0.000000
Fetch 2 0.00 0.00010014
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01010014
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
---------- ---------- ---------- ---------------------------------------------------
141414 MERGE JOIN (cr=10 pr=0 pw=0 time=520 us cost=6 size=364 card=14)
444 TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=0 pw=0 time=329 us cost=2 size=52 card=4)
444 INDEX FULL SCAN PK_DEPT (cr=2 pr=0 pw=0 time=173 us cost=1 size=0 card=4)(object id 75334)
141414 SORT JOIN (cr=6 pr=0 pw=0 time=242 us cost=4 size=182 card=14)
141414 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 onTimes Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.000.00
SQL*Net message from client 2 11.5011.50
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.010000
Execute 1 0.00 0.000000
Fetch 2 0.00 0.00010014
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01010014
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited onTimes Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.000.00
SQL*Net message from client 4 12.7933.45
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.000000
Execute 0 0.00 0.000000
Fetch 0 0.00 0.000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.000000
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行缩进一样，那么就先执行上面的。
