1.執(zhí)行計(jì)劃

執(zhí)行計(jì)劃就是Oracle基于成本（Cost）、算法和統(tǒng)計(jì)信息，最終得到資源消耗最低的SQL執(zhí)行步驟的組合。其中，成本的值是一個(gè)估算值，包括訪問路徑、關(guān)聯(lián)方式、I/O、CPU和內(nèi)存等。

2.獲取執(zhí)行計(jì)劃

2.1.EXPLAIN PLAN

優(yōu)化器基于當(dāng)前數(shù)據(jù)庫對(duì)象統(tǒng)計(jì)信息生成執(zhí)行計(jì)劃和相關(guān)信息，而不實(shí)際執(zhí)行語句。

SQL> explain plan for select e.empno,e.job,d.dname from scott.emp e,scott.dept d
  2  where e.deptno=d.deptno
  3  and d.dname = 'ACCOUNTING';
Explained.
SQL> select * from table(dbms_xplan.display(NULL,NULL,'ADVANCED'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	4 |   216 |	6   (0)| 00:00:01 |
|*  1 |  HASH JOIN	   |	  |	4 |   216 |	6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPT |	1 |    22 |	3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    12 |   384 |	3   (0)| 00:00:01 |
---------------------------------------------------------------------------

2.2.SET AUTOTRACE

跟explain for一樣，set autotrac也是不實(shí)際執(zhí)行。

SQL> set autot
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autotrace traceonly
SQL> select e.empno,e.job,d.dname from scott.emp e,scott.dept d
  2  where e.deptno=d.deptno;
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |    12 |   648 |	6   (0)| 00:00:01 |
|*  1 |  HASH JOIN	   |	  |    12 |   648 |	6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |	4 |    88 |	3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    12 |   384 |	3   (0)| 00:00:01 |
---------------------------------------------------------------------------

2.3.DBMS_XPLAN

不同于前兩種方法，DBMS_XPLAN查看的執(zhí)行計(jì)劃是真實(shí)的。

2.3.1.DISPLAY_CURSOR

用于獲取內(nèi)存中shared_pool游標(biāo)緩存。

select * from table(dbms_xplan.DISPLAY_CURSOR('sql_id','null',ADVANCED ALLSTATS LAST'));
--sql_id
--child_number，null表示顯示所有子游標(biāo)
--format： ALLSTATS LAST 、 ADVANCED ALLSTATS LAST

SQL> select * from table(dbms_xplan.DISPLAY_CURSOR('2wnhpatm9a24s',null,'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID	2wnhpatm9a24s, child number 0
-------------------------------------
select e.empno,e.job,d.dname from scott.emp e,scott.dept d where
e.deptno=d.deptno
Plan hash value: 615168685
--------------------------------------------------------------------------
| Id  | Operation	   | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	   |	   |	 6 (100)|	   |	   |	   |	      |
|*  1 |  HASH JOIN	   |	  |	12 |   648 |	 6   (0)| 00:00:01 |  1695K|  1695K| 1021K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |	 4 |	88 |	 3   (0)| 00:00:01 |	   |	   |	      |
|   3 |   TABLE ACCESS FULL| EMP  |	12 |   384 |	 3   (0)| 00:00:01 |	   |	   |	      |
---------------------------------------------------------------------------

2.3.2.DISPLAY_AWR

用于獲取AWR基表WRH$_SQL_PLAN。

select * from table(dbms_xplan.display_awr('sql_id',plan_hash_value,db_id,'format'))
--sql_id
--NULL表示顯示所有
--默認(rèn)獲取本地v$database中的值
-- ALLSTATS LAST 、 ADVANCED ALLSTATS LAST

select * from table(dbms_xplan.display_awr('as3uq6ggb3gx6',null,null,'ADVANCED'));

2.4.獲取真實(shí)消耗資源

執(zhí)行計(jì)劃是SQL語句執(zhí)行前基于當(dāng)前的統(tǒng)計(jì)信息生成的，日中rows、bytes、cost、time等為評(píng)估值，為了獲取更為準(zhǔn)確的實(shí)際值，即A_ROWS,A_TIME等，數(shù)據(jù)庫在執(zhí)行SQL語句時(shí)需要做額外的收集。

--會(huì)話
SQL> alter session set STATISTICS_LEVEL=ALL;
Session altered.
SQL> select * from scott.dept d,scott.emp e where d.deptno=e.deptno and d.dname='SALES';
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null,null,'ADVANCED ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID	bcym3bsvz65x7, child number 0
-------------------------------------
select * from scott.dept d,scott.emp e where d.deptno=e.deptno and
d.dname='SALES'
Plan hash value: 615168685
---------------------------------------------------------------------------

| Id  | Operation| Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time  | A-Rows |A-Time   | Buffers |  OMem |  1Mem | Used-Mem |

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT   |	  |	 1 |	    |	    |	  6 (100)|
    |	   6 |00:00:00.02 |	 15 |	    |	    |	       |

|*  1 |  HASH JOIN	   |	  |	 1 |	  4 |	468 |	  6   (0)| 00:00
:01 |	   6 |00:00:00.02 |	 15 |  1476K|  1476K|  471K (0)|
|*  2 |   TABLE ACCESS FULL| DEPT |	 1 |	  1 |	 30 |	  3   (0)| 00:00
:01 |	   1 |00:00:00.01 |	  7 |	    |	    |	       |
|   3 |   TABLE ACCESS FULL| EMP  |	 1 |	 12 |  1044 |	  3   (0)| 00:00
:01 |	  12 |00:00:00.01 |	  8 |	    |	    |	       |
---------------------------------------------------------------------------
--語句
select /*+ gather_plan_statistics */ .......

3.查看執(zhí)行計(jì)劃

3.1.右上原則

最右最上先執(zhí)行。

3.2.樹形圖解法

例如上述執(zhí)行計(jì)劃：
先畫出樹形結(jié)構(gòu)：

• 自頂向下
• 最接近的上方，并且前進(jìn)一格為父子節(jié)點(diǎn)。
• 同一父親、相同縮進(jìn)，為兄弟節(jié)點(diǎn)
  1縮進(jìn)一格，為0的兒子； 2和4縮進(jìn)相同為兄弟節(jié)點(diǎn)，同為1的兒子，2在上，為兄； 3相對(duì)2縮進(jìn)一格，為2的兒子；5為4的兒子；得到如下樹形圖
  
  執(zhí)行順序：
• 先遍歷左子樹
• 再遍歷右子樹
• 左節(jié)點(diǎn)先于右節(jié)點(diǎn)執(zhí)行
• 子節(jié)點(diǎn)先于父節(jié)點(diǎn)執(zhí)行
• 對(duì)于相同縮進(jìn)、上下同父的兄弟節(jié)點(diǎn)，兄先執(zhí)行
• 最后訪問根節(jié)點(diǎn)

4.常用的執(zhí)行計(jì)劃查看語句

查看當(dāng)前sql在內(nèi)存中最后一次執(zhí)行計(jì)劃

--查看當(dāng)前SQL在內(nèi)存中的最后一次執(zhí)行計(jì)劃，命令如下：
SELECT RPAD('Inst: ' || v.inst_id, 9) || ' ' ||
       RPAD('Child: ' || v.child_number, 11) inst_child,
       t.plan_table_output
  FROM gv$sql v,
       TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all',
                                NULL,
                                'ADVANCED ALLSTATS LAST -Projection -Outline -Note',
                                'inst_id = ' || v.inst_id ||
                                ' AND sql_id = ''' || v.sql_id ||
                                ''' AND child_number = ' || v.child_number)) t
 WHERE v.sql_id = '&SQL_ID'
   AND v.loaded_versions > 0;

--查看當(dāng)前SQL在內(nèi)存中的所有執(zhí)行計(jì)劃，命令如下：
SELECT RPAD('Inst: ' || v.inst_id, 9) || ' ' ||
       RPAD('Child: ' || v.child_number, 11) inst_child,
       t.plan_table_output
  FROM gv$sql v,
       TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all',
                                NULL,
                                'ADVANCED ALLSTATS -Projection -Outline -Note',
                                'inst_id = ' || v.inst_id ||
                                ' AND sql_id = ''' || v.sql_id ||
                                ''' AND child_number = ' || v.child_number)) t
 WHERE v.sql_id = '&SQL'
   AND v.loaded_versions > 0
   AND v.executions > 1;

--查看指定SQL的歷史執(zhí)行計(jì)劃，包括記錄在快照點(diǎn)中執(zhí)行計(jì)劃，命令如下：
SELECT t.plan_table_output
  FROM (SELECT DISTINCT sql_id, plan_hash_value, dbid
          FROM dba_hist_sql_plan
         WHERE sql_id = '&SQL') v,
       TABLE(DBMS_XPLAN.DISPLAY_AWR(v.sql_id,
                                    v.plan_hash_value,
                                    null,
                                    'ADVANCED ALLSTATS')) t;

注：本文參考于：《DBA攻堅(jiān)指南》