Oracle10g里sql語句的執行計劃

Oracle診斷或調優經常需要做的就是查看SQL語句的執行計劃，很多時候我們需要得到sql語句在不同場景、不同時間段的執行計劃。

Oracle10g平臺以上獲取sql語句的執行計劃的方法如下：

1. Explain Plan Command

通過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的執行計劃，然后可以通過如下2種方式在當前session內獲取該sql語句的執行計劃：

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

通過explain plan command獲得sql語句的執行計劃，最大的優點是不用直接運行sql語句，避免了由于返回結果時間過長過多帶來的等待。

2. V$SQL_PLAN

使用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的執行計劃。

3. SQL*Plus AUTOTRACE

使用autotrace功能獲得sql語句的執行計劃。當然這需要建立autotrace相關的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}

注意：紅色部分是可選項。執行”set autotrace option”后，運行的sql在返回結果結束后根據參數設置顯示該條sql的執行計劃或統計信息。

不同選項說明如下：

4. AWRRPT

通過AWRRPT查看sql語句的執行計劃。
同其他方式一樣，要想獲得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功能。