SQL调优是很多Oracle DBA和开发人员的重要工作。一个高效的SQL改写调优，可以大幅度优化执行计划，提高执行效率，进而增强关键用例模块的可用性和满意度。
进行SQL调优中不可缺少的操作就是获取指定SQL的执行计划。在目前的Oracle版本中，有很多可以使用的执行计划获取方法。本篇就加以总结，供需要的朋友不时之需。
1、方便易用的explain plan
Explain plan命令在Oracle中，可以对后面的SQL语句进行直接的解析，将执行计划保存在一个plan_table的中间表中。之后通过dbms_xplan包的方法进行获取。
ü 确定plan_table的安装
使用explain plan命令的一个前提就是系统中存在plan_table数据表。如果没有的话，需要进行脚本调用安装。
--如果不存在，就生成
SQL> @?/rdbms/admin/catplan.sql
程序包体已创建。
没有错误。
这里注意两个细节：
首先，调用脚本中的?表示ORACLE_HOME目录。如果是使用sqlplus工具，可以直接使用？代指该目录。其他如PL/SQL Developer第三方工具不支持；
其次，如果是Oracle 10g以上的版本，使用脚本名称为catplan.sql。如果是如9i的版本，使用脚本名称为utlxplan.sql。如果在高版本Oracle上使用低版本的plan_table结构，可能在生成执行计划中报错“Plan Table version too old”错误。
ü 使用explain plan for命令生成执行计划并显示
SQL> set linesize 10000;
SQL> set wrap off;
SQL> set pagesize 10000;
SQL> explain plan for select * from scott.emp where empno=7839;
已解释。
之后使用dbms_xplan工具包将生成的执行计划展示出。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 35 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7839)
已选择14行。
该语句显示出索引执行计划。
ü 显示详细执行计划信息
上面直接调用，是显示出分析的SQL最简单的执行计划。可以通过设置format参数，显示出关于计划的更详细信息。
SQL> select * from table(dbms_xplan.display(null,null,'advanced'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 35 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
2 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7839)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
"EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7],
"EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
已选择41行。
添加了format参数，Oracle将更加详细的执行计划信息返回，包括Outline信息、结果集合映射等内容。
ü Explain plan for细节
Explain plan for使用比较方便，特别是可以支持在pl/sql developer等第三方开发工具中使用的特性，比较吸引人。不过，explain plan在使用的时候，要注意一些潜在问题：
首先，explain plan for是单纯对SQL语句进行优化器分析，获取产生到的执行计划。这个过程中，并没有真正执行。所以，生成的执行计划有时候会有bug，而且进行统计的信息情况没有autotrace高；
其次，explain plan for由于只是对执行计划进行估计。所以在有绑定变量的SQL时，生成的执行计划并不准确；
2、 获取“刚刚”的执行计划display_cursor
使用dbms_xplan包，还可以获取刚刚执行过的SQL执行计划信息。
SQL> select * from scott.emp where empno=7900;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30
SQL> select * from table(dbms_xplan.display_cursor); //获取刚刚的执行计划；
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 66nkfdw21rc9j, child number 0
-------------------------------------
select * from scott.emp where empno=7900
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 35 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
已选择19行。
直接调用display_cursor，不指定sql_id，就可以将刚刚当前会话执行的SQL命令执行计划从library cache中抽取出来。
注意：display_cursor也支持format参数，可以进行详细执行计划信息的抽取。
此外还有一点，就是这种方法获取刚刚执行过的SQL执行计划，只能在sqlplus或者sqlplusw上使用。如果是pl/sql developer等第三方工具，可能不适用。
(注意：在pl/sql developer下使用存在问题)
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9m7787camwh4m, child number 0
begin :id := sys.dbms_transaction.local_transaction_id; end;
NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
8 rows selected
3、autotrace工具使用
本人以为autotrace工具是获取执行计划信息较为完整的工具。优势在于使用该工具可以获取到执行SQL过程中的读写、调用递归和排序分组消耗。
在之前的Blog中，笔者已经撰写过一篇关于autotrace较为详细的文章，有兴趣的读者可以参考：《Autotrace工具使用——小工具，大用场》（http://space.itpub.net/17203031/viewspace-686535）。
在下篇中，我们会介绍直接从shared_pool中抽取执行计划，和从AWR报告库中抽取。最后介绍使用10046事件跟踪执行计划。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-704626/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-704626/
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
