ORACLE EXPLAIN PLAN的总结 (优化SQL语句）

太过爱你忘了你带给我的痛 2022-05-23 00:29 469阅读 0赞

### ORACLE EXPLAIN PLAN的总结 ###

文章分类:数据库

在ORACLE数据库中,需要对 SQL语句进行优化的话需要知道其执行计划,从而针对性的进行调整.ORACLE的执行计划的获得有几种方法,下面就来总结下  
  
1、EXPLAIN的使用  
  
Oracle RDBMS执行每一条 SQL语句，都必须经过Oracle优化器的评估。所以，了解优化器是如何选择(搜索)路径以及索引是如何被使用的，对优化 SQL语句有很大的帮助。Explain可以用来迅速方便地查出对于给定 SQL语句中的查询数据是如何得到的即搜索路径(我们通常称为Access Path)。从而使我们选择最优的查询方式达到最大的优化效果。  
1.1 、安 装   
  
要使用EXPLAIN首先要执行相应的脚本，创建出Explain\_plan表。  
  
具体脚本执行如下：  
  
$ORACLE\_HOME/rdbms/admin/utlxplan. sql (UNIX) 该脚本后会生成一个表这个程序会创建一个名为plan\_table的表。  
  
1.2 、使用  
常规使用语法：  
explain PLAN \[ SET STATEMENT\_ID \[=\] < string literal > \] \[ INTO < table\_name > \]  
FOR <  sql\_statement >  
  
其中：  
STATEMENT\_ID：是一个唯一的字符串，把当前执行计划与存储在同一PLAN中的其它执行计划区别开来。  
TABLE\_NAME：是plan表名，它结构如前所示，你可以任意设定这个名称。  
SQL\_STATEMENT：是真正的 SQL语句。   
  
比如：   
  
SQL>explain plan set statement\_id='T\_TEST' for select \* from t\_test;   
  
SQL>  
  
Explained  
  
执行下面语句可以查询到执行计划  
  
SQL>SELECT A.OPERATION,OPTIONS,OBJECT\_NAME,OBJECT\_TYPE,ID,PARENT\_ID  
2 FROM PLAN\_TABLE a  
3 WHERE STATEMENT\_ID='T\_TEST'  
4 ORDER BY Id;  
也可以用这句话 select \* from table(dbms\_xplan.display); 可以把所有PLAN\_TABLE里的数据罗列出来。  
  
2 、AUTOTRACE的使用方法  
  
2.1、安装  
  
用sys用户运行脚本ultxplan. sql  
建立这个表的脚本是：（UNIX:$ORACLE\_HOME/rdbms/admin, Windows:%ORACLE\_HOME%\\rdbms\\admin）ultxplan. sql。  
SQL> connect sys/sys@colm2 as sysdba;  
SQL> @C:\\oracle\\ora92\\rdbms\\admin\\utlxplan. sql;  
SQL> create public synonym plan\_table for plan\_table;--建立同义词  
SQL> grant all on plan\_table to public;--授权所有用户  
  
要在数据库中建立一个角色plustrace,用sys用户运行脚本plustrce. sql来创建这个角色，这个脚本在目录（UNIX:$ORACLE\_HOME/sqlplus/admin, Windows:%ORACLE\_HOME%\\sqlplus\\admin）中；  
SQL> @C:\\oracle\\ora92\\sqlplus\\admin\\plustrce. sql;  
然后将角色plustrace授予需要autotrace的用户；  
SQL>grant plustrace to public;  
经过以上步骤的设置，就可以在 sql\*plus中使用autotrace了  
2、2 使用  
  
使用起来非常方便，只要使用一条命令就可以了  
SQL>SET AUTOTRACE ON；  
  
\*autotrace功能只能在 SQL\*PLUS里使用  
  
其他一些使用方法：  
2.2.1、在SQLPLUS中得到语句总的执行时间  
SQL> set timing on;  
  
2.2.2、只显示执行计划--（会同时执行语句得到结果）  
SQL>set autotrace on explain  
比如：  
sql> select count(\*) from test;  
count(\*)  
\-------------  
4  
  
Execution plan  
\----------------------------  
0 select statement ptimitzer=choose (cost=3 card=1)  
1 0 sort(aggregate)  
2 1 partition range(all)  
3 2 table access (full) of 't\_test' (cost=3 card=900)  
  
  
  
2.2.3、只显示统计信息---（会同时执行语句得到结果）  
SQL>set autotrace on statistics;  
（备注：对于SYS用户，统计信息将会是0）  
  
2.2.4、显示执行计划，屏蔽执行结果--（但语句实质还执行的  
SQL> set autotrace on traceonly;   
（备注：同SET AUTOTRACE ON; 只不过不显示结果，显示计划和统计）  
  
2.2.5、仅仅显示执行计划，屏蔽其他一切结果--(语句还是执行了)  
SQL>set autotrace on traceonly explain;  
对于仅仅查看大表的Explain Plan非常管用。  
  
2.2.6、关闭  
SQL>set autotrace off;  
  
总结：SQLPLUS 下的自动显示功能，在看执行计划中其语句还是会被执行的。尤其在执行UPDATE/DELETE语句时请千万注意，ORACLE是先执行脚本同时显示执行计划的，即使使用set autotrace on traceonly explain;  
这个时候推荐使用EXPLAIN PLAN FOR来看或者PL/ SQL等第三方工具  
  
3、第三工具来看执行计划  
如果在PL/ SQL中使用选择要查询语句显示执行计划，则只需要 SQL WINDOWS 窗口里面输入要查询的 SQL语句，然后选择按键F5或者在菜单TOOLS?D?D>Explain Plan 菜单按键就可以在执行计划窗口查看该语句的执行计划。  
在TOAD语句中在执行当前的 SQL窗口中选择下方的Explain PlanTAB页即可以查看要执行语句的执行计划信息。  
  
4、限制  
explain真正的唯一的限制是用户不能去解释其它用户的表，视图，索引或其它类型，用户必须是所有被解释事物的所有者，如果不是所有者而只有select权限，explain会返回一个错误。