Oracle10g如何獲取執行計劃

tolywang發表於2010-10-26
Oracle


　　Oracle診斷或調優經常需要做的就是檢視SQL語句的執行計劃，很多時候我們需要得

到sql語句在不同場景、不同時間段的執行計劃。　　
 
　　一，透過explain plan命令獲得sql語句的執行計劃。
explain plan的命令格式如下：
sql>Explain plan 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 ptimizer=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功能。

本文來源於 ：

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-676762/，如需轉載，請註明出處，否則將追究法律責任。

相關文章