ORACLE EXPLAIN PLAN总结
作为DBA,数据库的性能优化是主要的工作任务的之一,而对SQL的优化必须知道SQL的执行计划,从而根据计划做相应的调整。下面对ORACLE执行计划的几种方法的总结。
1. AUTOTRACE
set autotrace on |
设置autotrace on后,执行sql语句有会产生explain plan和统计信息。
优点:使用方便
缺点:查看执行时间较长的sql语句时,需要等待该语句执行成功后,才返回执行计划,使优化的周期大大增长。
set autotrace on –产生explain plan和统计信息
set autotrace traceonly 只列出执行计划,不会真正执行语句,也不产生统计信息。
set autotrace on explain 列出执行计划,并执行语句,不产生统计信息
set autotrace off 关闭autotrace 功能
2. EXPLAIN PLAN
(1) 安装
用sys身份登陆,执行脚本utlxplan.sql,该脚本位于
WINDWOS: %ORACLE_HOME%\rdbms\admin\utlxplan.sql
LINUX: $ ORACLE_HOME/rdbms/admin/ utlxplan.sql
C:\Documents and Settings\cn010294>sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 14 16:28:22 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. idle> conn sys/admin1@orcl as sysdba Connected. sys@ORCL> @%ORACLE_HOME%\rdbms\admin\utlxplan.sql 如果需要授权给其他user使用 sys@ORCL> create public synonym plan_table for plan_table;--建立同义词 sys@ORCL> grant all on plan_table to public ; --授权给所有人 Grant succeeded. |
(2) 使用
EXPLIAN PLAN [ SET STATEMENT_ID [=] < string literal > ] [ INTO < table_name > ] FOR < sql_statement >
其中:
STATEMENT_ID:是一个唯一的字符串,把当前执行计划与存储在同一PLAN中的其它执行计划区别开来。
TABLE_NAME:是PLAN表名,它结构如前所示,你可以任意设定这个名称。
SQL_STATEMENT: 是真正的SQL语句。
sys@ORCL> explain plan set statement_id='test' for 2 select * from t_parent; Explained. |
通过以下语句可以查询到执行计划:
set linesize 150
set pagesize 500
col PLANLINE for a120
SELECT EXECORD EXEC_ORDER, PLANLINE
FROM (SELECT PLANLINE, ROWNUM EXECORD, ID, RID
FROM (SELECT PLANLINE, ID, RID, LEV
FROM (SELECT lpad(' ',2*(LEVEL),rpad(' ',80,' '))||
OPERATION||' '|| -- Operation
DECODE(OPTIONS,NULL,'','('||OPTIONS || ') ')|| -- Options
DECODE(OBJECT_OWNER,null,'','OF '''|| OBJECT_OWNER||'.')|| -- Owner
DECODE(OBJECT_NAME,null,'',OBJECT_NAME|| ''' ')|| -- Object Name
DECODE(OBJECT_TYPE,null,'','('||OBJECT_TYPE|| ') ')|| -- Object Typ
DECODE(ID,0,'OPT_MODE:')|| -- Optimizer
DECODE(OPTIMIZER,null,'','ANALYZED','', OPTIMIZER)||
DECODE(NVL(COST,0)+NVL(CARDINALITY,0)+NVL(BYTES,0),
0,null,' (COST='||TO_CHAR(COST)||',CARD='||
TO_CHAR(CARDINALITY)||',BYTES='||TO_CHAR(BYTES)||')')
PLANLINE, ID, LEVEL LEV,
(SELECT MAX(ID) FROM PLAN_TABLE PL2
CONNECT BY PRIOR ID = PARENT_ID
AND PRIOR STATEMENT_ID = STATEMENT_ID
START WITH ID = PL1.ID
AND STATEMENT_ID = PL1.STATEMENT_ID) RID
FROM PLAN_TABLE PL1
CONNECT BY PRIOR ID = PARENT_ID
AND PRIOR STATEMENT_ID = STATEMENT_ID
START WITH ID = 0
AND STATEMENT_ID = 'test')
ORDER BY RID, -LEV))
ORDER BY ID;
用EXPLIAN PLAN方法时,并不执行sql语句,所以只会列出执行计划,不会列出统计信息,并且执行计划只存在plan_table中。所以该语句比起set autotrace traceonly可用性要差。而且查看结果还需要自己去格式化查询结果,相对比较麻烦。
3. 第三方工具
TOAD:在执行当前的SQL窗口中选择下方的Explain Plan页即可以查看要执行语句的执行计划信息。
其他工具:
4. dbms_system存储过程生成执行计划
5. 分析执行计划
待续