Oracle診斷或調(diào)優(yōu)經(jīng)常需要做的就是查看SQL語句的執(zhí)行計(jì)劃，很多時(shí)候我們需要得到sql語句在不同場(chǎng)景、不同時(shí)間段的執(zhí)行計(jì)劃。

Oracle10g平臺(tái)以上獲取sql語句的執(zhí)行計(jì)劃的方法如下：

1. Explain Plan Command

通過explain plan命令獲得sql語句的執(zhí)行計(jì)劃。

explain plan的命令格式如下：
sql>Explain plan <set statement_id = ‘text’> <into your plan table> for sql statement;

注：
藍(lán)色部分可以省略；紅色部分為具體sql語句
"set statement_id = ‘text’” ,其中statement_id是plan_table.statement_id，標(biāo)記該條sql的id信息;
"into your plan table”,默認(rèn)的plan table是plan_table

運(yùn)行上述命令生成sql的執(zhí)行計(jì)劃，然后可以通過如下2種方式在當(dāng)前session內(nèi)獲取該sql語句的執(zhí)行計(jì)劃：

• 運(yùn)行Explain plan 腳本$ORACLE_HOME/rdbms/admin/utlxpls.sql
      eg: sql>@$ORACLE_HOME/rdbms/admin/utlxpls.sql
• 或是直接執(zhí)行select * from table(dbms_xplan.display());

通過explain plan command獲得sql語句的執(zhí)行計(jì)劃，最大的優(yōu)點(diǎn)是不用直接運(yùn)行sql語句，避免了由于返回結(jié)果時(shí)間過長過多帶來的等待。

2. V$SQL_PLAN

使用dbms_xplan.display_cursor包從v$sql_plan里查看sql語句的執(zhí)行計(jì)劃。

sql命令如下：
sql>select plan_table_output from table(dbms_xplan.display_cursor(‘sql_id’));

注意：sql_id可以通過v$sql 視圖獲得。另外，v$sql_plan_statistics_all記錄著sql語句的統(tǒng)計(jì)信息，也可以結(jié)合該視圖查看到歷史sql的執(zhí)行計(jì)劃。

3. SQL*Plus AUTOTRACE

使用autotrace功能獲得sql語句的執(zhí)行計(jì)劃。當(dāng)然這需要建立autotrace相關(guān)的plan_table、synonym等。

建立autotrace方法如下：

1、cd @oracle_homerdbms/rdbms/admin
2、#sqlplus as system
3、sql>@utlxplan
4、sql>create public synonym plan_table for plan_table
5、sql>Grant all on plan_table to public;

autotrace命令格式：
SQL>SET AUTOTRACE OFF/ON/TRACEONLY {EXPLAIN/STATISTICS}

注意：紅色部分是可選項(xiàng)。執(zhí)行”set autotrace option”后，運(yùn)行的sql在返回結(jié)果結(jié)束后根據(jù)參數(shù)設(shè)置顯示該條sql的執(zhí)行計(jì)劃或統(tǒng)計(jì)信息。

不同選項(xiàng)說明如下：

4. AWRRPT

通過AWRRPT查看sql語句的執(zhí)行計(jì)劃。
同其他方式一樣，要想獲得sql語句的執(zhí)行計(jì)劃，必須獲得該sql的sql_id。有了sql_id，并且確認(rèn)該sql已經(jīng)被記錄在dba_hist_sqltext里，你就可以使用oracle10g提供的dbms_xplan.display_awr包顯示指定sql_id的執(zhí)行計(jì)劃。

比如，執(zhí)行一條sql，通過awrrpt獲取其執(zhí)行計(jì)劃的步驟如下：

1）執(zhí)行sql語句

sql>select /*awrshow*/ id from test order by id;

2）確認(rèn)sql語句的sql_id

sql>select sql_id,sql_text from v$sql where sql_text like '%awrshow%';

3）確認(rèn)該sql是否被記錄在dba_hist_sqltext里

sql>select sql_id,sql_text from dba_hist_sqltext where sql_id = '****';

注意：如果沒有該sql的信息，則手工設(shè)置AWR的snapshot,將sql信息記錄在dba_hist_sqltext里。執(zhí)行如下sql命令：
sql>exec dbms_workload_repository.create_snapshot();

4）使用dbms_xplan.display_awr的包顯示指定sql_id的執(zhí)行計(jì)劃

sql>select plan_table_output from table(dbms_xplan.display_awr('sql_id'));

結(jié)合AWRRPT功能查看sql語句的執(zhí)行計(jì)劃最大的用處就是，當(dāng)業(yè)務(wù)出現(xiàn)瓶頸或是峰值時(shí)，你可以獲得異常時(shí)間段內(nèi)問題sql語句的執(zhí)行計(jì)劃與正常表現(xiàn)時(shí)的進(jìn)行對(duì)比。當(dāng)然前提是，數(shù)據(jù)庫是Oracle10g及以上版本，并使用了AWRRPT功能。