ORACLE EXPLAIN PLAN总结

 

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．  分析执行计划

 待续