Oracle10g获取sql话语执行计划

　Oracle10g获取sql语句的执行计划详解
　　---
　　Oracle诊断或调优经常需要做的就是查看SQL语句的执行计划，很多时候我们需要得到sql语句在不同场景、不同时间段的执行计划。　　

?

　　一，通过explain plan命令获得sql语句的执行计划。
　　explain plan的命令格式如下：?
　　sql>Explain plan <set statement_id = ‘text’> <into your plan table> for sql statement;
　　蓝色部分(<>中)可以省略；红色部分为具体sql语句?
　　"set statement_id = ‘text’” ,其中statement_id是plan_table.statement_id，标记该条sql的id信息;?
　　"into your plan table”,默认的plan table是plan_table
　　例如：
　 　SQL> Explain plan set statement_id = 'dd' into plan_table for select * from cmdba.cmcdms t where t.code_item_no='031' and t.code='1B';
　　已解释。
　　通过explain plan command获得sql语句的执行计划，最大的优点是不用直接运行sql语句，避免了由于返回结果时间过长过多带来的等待。

?

　　二，Oracle10g平台以上获取sql语句的执行计划的方法有很多种，大致列举如下：

　　0，通过PL/SQL查看

　　打开SQLWINDOW，输入sql语句，按F5可以看到它的执行计划,但是这里没有物理读等统计信息。

?

　　1，通过plan_table查看
　　1) 需要先运行@$ORACLE_HOME/rdbms/admin/utlxpls.sql语句，生成plan_table.
　　2) 执行explain plan命令获得sql语句的执行计划。
　 　SQL> Explain plan set statement_id = 'dd' into plan_table for select * from cmdba.cmcdms t where t.code_item_no='031' and t.code='1B';
　　注意，执行完成后要提交，才能在plan_table中查看到。
　　提交后，在PLAN_Table中可以查看到相关的记录。
　　3)查看sql执行计划：select * from plan_table where statement_id='dd'

?

　　2，使用dbms_xplan.display_cursor包从v$sql_plan里查看sql语句的执行计划。
　　sql命令如下：?
　　sql>select plan_table_output from table(dbms_xplan.display_cursor(‘sql_id’));
　　注意：sql_id可以通过v$sql 视图获得。另外，v$sql_plan_statistics_all记录着sql语句的统计信息，也可以结合该视图查看到历史sql的执行计划。
　　如查询上述sql语句的sql_ID：select sql_id from v$Sql where sql_text like 'select * from cmdba.cmcdms t where t.code_item_no=%'
　　得到sql_id=4skqqxzg5qkz9，查询执行计划：
　　SQL> select plan_table_output from table(dbms_xplan.display_cursor('4skqqxzg5qkz9'));
　　PLAN_TABLE_OUTPUT
　　--------------------------------------------
　　SQL_ID? 4skqqxzg5qkz9, child number 0
　　-------------------------------------
　　select * from cmdba.cmcdms t where t.code_item_no='031' and t.code='1B'
　　Plan hash value: 3600018637
　　--------------------------------------------
　　| Id? | Operation?????????????????? | Name????? | Rows? | Bytes | Cost (%CPU)| T
　　--------------------------------------------
　　|?? 0 | SELECT STATEMENT??????????? |?????????? |?????? |?????? |???? 2 (100)|
　　|?? 1 |? TABLE ACCESS BY INDEX ROWID| CMCDMS??? |???? 1 |??? 71 |???? 2?? (0)| 0
　　|*? 2 |?? INDEX UNIQUE SCAN???????? | PK_CMCDMS |???? 1 |?????? |???? 1?? (0)| 0
　　--------------------------------------------
　　Predicate Information (identified by operation id):
　　---------------------------------------------------
　　2 - access("T"."CODE_ITEM_NO"='031' AND "T"."CODE"='1B')
　　19 rows selected

?

　　3，直接在V$Sql_plan中查看执行计划：
　　同样也需要在v$sql 视图中查询出sql_id。
　　然后通过sql_id查询执行计划：
　　select * from V$SQL_PLAN where sql_id='4skqqxzg5qkz9'

?

　　4，使用dbms_xplan包,这里跟F5差不多,也是没有一些统计信息
　　Oracle9i新引入的包dbms_xplan
　　Oracle9i新引入的包dbms_xplan。函数display有三个参数：Table_name——执行计划所存放的表，默认为PLAN_TABLE；STATEMENT_ID——PLAN_TABLE表中的字段；FORMAT——显示格式。

?

　　SQL> explain plan for select * from cmdba.cmcdms t where t.code_item_no='031' and t.code='1B';

?

　　Explained

?

　　SQL> select * from table(dbms_xplan.display);

?

　　PLAN_TABLE_OUTPUT
　　--------------------------------------------
　　Plan hash value: 3600018637
　　--------------------------------------------
　　| Id? | Operation?????????????????? | Name????? | Rows? | Bytes | Cost (%CPU)| T
　　--------------------------------------------
　　|?? 0 | SELECT STATEMENT??????????? |?????????? |???? 1 |??? 71 |???? 2?? (0)| 0
　　|?? 1 |? TABLE ACCESS BY INDEX ROWID| CMCDMS??? |???? 1 |??? 71 |???? 2?? (0)| 0
　　|*? 2 |?? INDEX UNIQUE SCAN???????? | PK_CMCDMS |???? 1 |?????? |???? 1?? (0)| 0
　　--------------------------------------------
　　Predicate Information (identified by operation id):
　　---------------------------------------------------
　　2 - access("T"."CODE_ITEM_NO"='031' AND "T"."CODE"='1B')

?

　　14 rows selected

?

　　优点：适合于bind var的SQL。可以直接执行，能够根据相关信息排序，显示界面友好。

?

　　5.使用autotrace查看,可以查看一些统计信息
　　首先执行脚本
　　SQL>@D:oracleora92rdbmsadminutlxplan.sql 建立plan_table表
　　SQL> set autotrace on
　　SQL> set autotrace traceonly
　　SQL> set timing on
　　SQL> select * from cmdba.cmcdms;

?

　　已选择3327行。

?

　　已用时间:? 00: 00: 00.13

?

　　Execution Plan
　　----------------------
　　0????? SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=3327 Bytes=23
　　6217)

?

　　1??? 0?? TABLE ACCESS (FULL) OF 'CMCDMS' (TABLE) (Cost=9 Card=3327
　　Bytes=236217)

?

　　Statistics
　　----------------------
　　0? recursive calls
　　0? db block gets
　　256? consistent gets
　　0? physical reads
　　0? redo size
　　351787? bytes sent via SQL*Net to client
　　24956? bytes received via SQL*Net from client
　　446? SQL*Net roundtrips to/from client
　　0? sorts (memory)
　　0? sorts (disk)
　　3327? rows processed

?

　　或者：

?

　　SQL> set autotrace trace explain
　　SQL> select * from v$parameter;
　　Execution Plan
　　----------------------
　　Plan hash value: 1128103955
　　------------------------------------------
　　| Id? | Operation???????? | Name???? | Rows? | Bytes | Cost (%CPU)| Time???? |
　　------------------------------------------
　　|?? 0 | SELECT STATEMENT? |????????? |???? 1 |?? 926 |???? 1 (100)| 00:00:01 |
　　|*? 1 |? HASH JOIN??????? |????????? |???? 1 |?? 926 |???? 1 (100)| 00:00:01 |
　　|*? 2 |?? FIXED TABLE FULL| X$KSPPI? |???? 1 |?? 249 |???? 0?? (0)| 00:00:01 |
　　|?? 3 |?? FIXED TABLE FULL| X$KSPPCV |?? 100 | 67700 |???? 0?? (0)| 00:00:01 |
　　------------------------------------------
　　Predicate Information (identified by operation id):
　　---------------------------------------------------
　　1 - access("X"."INDX"="Y"."INDX")
　　??? filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
　　??? "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
　　2 - filter("X"."INST_ID"=USERENV('INSTANCE') AND
　　??? TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%')

?

　　关于Autotrace几个常用选项的说明:
　　SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
　　SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
　　SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
　　SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
　　SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出
　　这种方法显示内容较多……

?

　　6.使用sql_trace查看,这个跟3差不多
　　在pl/sql或者sqlplus中，打开一个sql_window。
　　（1）先运行：alter session set sql_trace=true;
　　（2）再运行你那个返回结果不正确的SQL
　　（3）再运行：alter session set sql_trace=false;
　　（4）马上登陆到机器上，到$ORACLE_BASE/admin/sid/udump目录下。
　　（5）找到刚生成的.trc文件(假设文件名是 xxx.trc)，
　　执行命令转储跟踪文件：tkprof xxx.trc aa.txt。
　　查看aa.txt文件。这个文件里面有执行计划。看看执行计划每一步返回的结果集记录数是不是正确。
　　(TTT：在udump目录下未找到新生成的*.trc，但在$ORACLE_BASE/admin/sid/bdump下找到相关文件，转储成功。oracle8i在$ORACLE_BASE\rdbms\trace\目录下。)

?

　　7.通过10053事件来查看,这里面可以查到一些关于SQL成本的详细信息(TTT：未测试成功，第一条语句不明)
　　在pl/sql或者sqlplus中，打开一个sql_window。
　　（1）先运行：Alter session set events’10053 trace name context forever[,level {1/2}]’;
　　（2）再运行你那个返回结果不正确的SQL
　　（3）再运行：Alter session set events’10053 trace name context off’;
　　（4）马上登陆到10.1.4.10机器上，到$ORACLE_BASE/admin/sid/udump目录下。找到刚生成的.trc文件)。

?

　　8.?通过AWRRPT查看(TTT：未测试)
　 　同其他方式一样，要想获得sql语句的执行计划，必须获得该sql的sql_id。有了sql_id，并且确认该sql已经被记录在 dba_hist_sqltext里，你就可以使用oracle10g提供的dbms_xplan.display_awr包显示指定sql_id的执行 计划。
　　比如，执行一条sql，通过awrrpt获取其执行计划的步骤如下：
　　1）执行sql语句
　　sql>select /*awrshow*/ id from test order by id;
　　2）确认sql语句的sql_id
　　sql>select sql_id,sql_text from v$sql where sql_text like '%awrshow%';
　　3）确认该sql是否被记录在dba_hist_sqltext里
　　sql>select sql_id,sql_text from dba_hist_sqltext where sql_id = '****';
　　注意：如果没有该sql的信息，则手工设置AWR的snapshot,将sql信息记录在dba_hist_sqltext里。执行如下sql命令：?
　　sql>exec dbms_workload_repository.create_snapshot();
　　4）使用dbms_xplan.display_awr的包显示指定sql_id的执行计划
　　sql>select plan_table_output from table(dbms_xplan.display_awr('sql_id'));
　　结合AWRRPT功能查看sql语句的执行计划最大的用处就是，当业务出现瓶颈或是峰值时，你可以获得异常时间段内问题sql语句的执行计划与正常表现时的进行对比。当然前提是，数据库是Oracle10g及以上版本，并使用了AWRRPT功能。