Oracle-SQL性能优化（1）-查看执行计划

1.执行计划

执行计划就是Oracle基于成本（Cost）、算法和统计信息，最终得到资源消耗最低的SQL执行步骤的组合。其中，成本的值是一个估算值，包括访问路径、关联方式、I/O、CPU和内存等。

2.获取执行计划

2.1.EXPLAIN PLAN

优化器基于当前数据库对象统计信息生成执行计划和相关信息，而不实际执行语句。

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也是不实际执行。

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查看的执行计划是真实的。

2.3.1.DISPLAY_CURSOR

用于获取内存中shared_pool游标缓存。

select * from table(dbms_xplan.DISPLAY_CURSOR('sql_id','null',ADVANCED ALLSTATS LAST'));
--sql_id
--child_number，null表示显示所有子游标
--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表示显示所有
--默认获取本地v$database中的值
-- ALLSTATS LAST 、 ADVANCED ALLSTATS LAST

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

2.4.获取真实消耗资源

执行计划是SQL语句执行前基于当前的统计信息生成的，日中rows、bytes、cost、time等为评估值，为了获取更为准确的实际值，即A_ROWS,A_TIME等，数据库在执行SQL语句时需要做额外的收集。

--会话
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.查看执行计划

3.1.右上原则

最右最上先执行。

3.2.树形图解法

例如上述执行计划：
先画出树形结构：

• 自顶向下
• 最接近的上方，并且前进一格为父子节点。
• 同一父亲、相同缩进，为兄弟节点
  1缩进一格，为0的儿子； 2和4缩进相同为兄弟节点，同为1的儿子，2在上，为兄； 3相对2缩进一格，为2的儿子；5为4的儿子；得到如下树形图
  
  执行顺序：
• 先遍历左子树
• 再遍历右子树
• 左节点先于右节点执行
• 子节点先于父节点执行
• 对于相同缩进、上下同父的兄弟节点，兄先执行
• 最后访问根节点

4.常用的执行计划查看语句

查看当前sql在内存中最后一次执行计划

--查看当前SQL在内存中的最后一次执行计划，命令如下：
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;

--查看当前SQL在内存中的所有执行计划，命令如下：
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的历史执行计划，包括记录在快照点中执行计划，命令如下：
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攻坚指南》