Explain For理論執行計劃相關

eric_zhyd發表於2013-12-04
AI
 
DBMS_XPLAN完整結構
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
ZHONG@ zhongpdb SQL>desc dbms_xplan
FUNCTION BUILD_PLAN_XML RETURNS XMLTYPE
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 TABLE_NAME                     VARCHAR2                IN     DEFAULT
 STATEMENT_ID                   VARCHAR2                IN     DEFAULT
 PLAN_ID                        NUMBER                  IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 FILTER_PREDS                   VARCHAR2                IN     DEFAULT
 PLAN_TAG                       VARCHAR2                IN     DEFAULT
 REPORT_REF                     VARCHAR2                IN     DEFAULT
FUNCTION DIFF_PLAN RETURNS VARCHAR2
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN
 OUTLINE                        CLOB                    IN
 USER_NAME                      VARCHAR2                IN     DEFAULT
FUNCTION DIFF_PLAN_AWR RETURNS VARCHAR2
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 PLAN_HASH_VALUE1               NUMBER                  IN
 PLAN_HASH_VALUE2               NUMBER                  IN
FUNCTION DIFF_PLAN_CURSOR RETURNS VARCHAR2
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 CURSOR_CHILD_NUM1              NUMBER                  IN
 CURSOR_CHILD_NUM2              NUMBER                  IN
FUNCTION DIFF_PLAN_OUTLINE RETURNS VARCHAR2
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN
 OUTLINE1                       CLOB                    IN
 OUTLINE2                       CLOB                    IN
 USER_NAME                      VARCHAR2                IN     DEFAULT
FUNCTION DIFF_PLAN_SQL_BASELINE RETURNS VARCHAR2
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 BASELINE_PLAN_NAME1            VARCHAR2                IN
 BASELINE_PLAN_NAME2            VARCHAR2                IN
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 TABLE_NAME                     VARCHAR2                IN     DEFAULT
 STATEMENT_ID                   VARCHAR2                IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 FILTER_PREDS                   VARCHAR2                IN     DEFAULT
FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
DISPLAY_AWR 函式顯示儲存在AWR 歷史資料的執行計劃。
提示：要正常呼叫DISPLAY_AWR 引數，必須對以下檢視有許可權：DBA_HIST_SQL_PLAN 和DBA_HIST_SQLTEXT 的SELECT。
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 提示：該ID 可以從DBA_HIST_SQL_PLAN.
 PLAN_HASH_VALUE                NUMBER(38)              IN     DEFAULT
 提示：通過該值，可以顯示SQL 語句的特定執行計劃。如果該引數未指定或為NULL，則會顯示語句的所有執行計劃；
 DB_ID                          NUMBER(38)              IN     DEFAULT
 提示：我們可以將其他資料庫的AWR 資料匯入本地資料庫進行分析。
 FORMAT                         VARCHAR2                IN     DEFAULT
1.根據模糊搜尋方式獲得歷史SQL_TEXT對應的SQL_ID
select sql_id, to_char(substr(sql_text,0,4000)) from dba_hist_sqltext where upper(sql_text) like 'SELECT COUNT(*) FROM %';

SQL_ID
- - - - - - - - - - - - -
TO_CHAR(SUBSTR(SQL_TEXT, 0 , 4000 ))
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - - -  
007grn40hdw0v
select COUNT ( * ) from t1_skew where id = :num


2.通過DISPLAY_AWR獲得歷史執行計劃
select * from table(dbms_xplan.display_awr('007grn40hdw0v'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------SQL_ID 007grn40hdw0v
--------------------
select COUNT(*) from t1_skew where id = :num
 
Plan hash value: 2900991624
 
-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |       |       |    29 (100)|          |
|   1 |  SORT AGGREGATE       |           |     1 |     3 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_T1_ID | 25158 | 75474 |    29   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
 
 
14 rows selected.

FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
函式可以顯示記憶體中一個或者多個遊標的執行計劃。
使用者必須對檢視V$SQLV、$SQL_PLAN 和V$SQL_PLAN_STATISTICS_ALL 的SELECT有許可權，才能正常呼叫DISPLAY_CURSOR 函式。

引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN     DEFAULT
 CURSOR_CHILD_NO                NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
FORMAT ：格式化控制字串。DISPLAY 函式的格式化控制字串的所有選項都適用於DISPLAY_CURSOR 函式。由於執行語句還可以通過提示GATHER_PLAN_STATISTICS 或設定系統引數STATISTICS_LEVEL 為“ALL”收集語句執行的效能統計資料，因此在細化選項中還有額外的選項，以選擇是否輸出這些資料。
? IOSTATS：是否輸出計劃的輸入輸出（IO）統計資料；
? MEMSTATS ：在啟用了PGA 自動管理（引數pga_aggregate_target 的值大於0）的情況下，是否輸出計劃的輸入記憶體統計資料（操作的記憶體使用量、記憶體讀次數等）；
? ALLSTATS：包含了IOSTATS 和MEMSTATS 的全部內容；
? LAST ：以上三個選項輸出的統計資料都是實際產生的資料，而非估算資料，它們是該遊標所有執行所產生的資料的總和。你可以增加LAST 選項以限定僅顯示最後一次執行的統計資料。

此外，還有一些未公佈的選項可用於該函式的輸出控制。首先是預定義格式：
? 'ADVANCED' ：高階格式。高階格式除了會輸出完全格式中的所有內容外，還會視情況輸出繫結變數窺視資訊和計劃概要（Outline）資訊；
? OUTLINE：是否以提示（HINT）的方式顯示計劃概要；
? PEEKED_BINDS：是否顯示繫結變數窺視資訊；
? BUFFSTATS ：是否顯示記憶體讀次數（包括一致性讀和當前讀次數），該資訊為IOSTATS 的一部分；
? PLAN_HASH：是否顯示計劃的雜湊值，該選項同樣適用於DISPLAY 函式。
 
直接查詢
SELECT plan_table_output FROM
table(dbms_xplan.display_cursor('fnrtqw9c233tt',null,'basic'));
間接查詢
SELECT plan_table_output
FROM v$sql s, TABLE(dbms_xplan.display_cursor(s.sql_id,s.child_number,
'ADVANCED')) t
WHERE s.sql_text like 'select * from%';

FUNCTION DISPLAY_PLAN RETURNS CLOB
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 TABLE_NAME                     VARCHAR2                IN     DEFAULT
 STATEMENT_ID                   VARCHAR2                IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 FILTER_PREDS                   VARCHAR2                IN     DEFAULT
 TYPE                           VARCHAR2                IN     DEFAULT
FUNCTION DISPLAY_SQLSET RETURNS DBMS_XPLAN_TYPE_TABLE
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 SQLSET_NAME                    VARCHAR2                IN
 SQL_ID                         VARCHAR2                IN
 PLAN_HASH_VALUE                NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 SQLSET_OWNER                   VARCHAR2                IN     DEFAULT
FUNCTION DISPLAY_SQL_PLAN_BASELINE RETURNS DBMS_XPLAN_TYPE_TABLE
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 SQL_HANDLE                     VARCHAR2                IN     DEFAULT
 PLAN_NAME                      VARCHAR2                IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
FUNCTION FORMAT_NUMBER RETURNS VARCHAR2
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 NUM                            NUMBER                  IN
FUNCTION FORMAT_NUMBER2 RETURNS VARCHAR2
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 NUM                            NUMBER                  IN
FUNCTION FORMAT_SIZE RETURNS VARCHAR2
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 NUM                            NUMBER                  IN
FUNCTION FORMAT_SIZE2 RETURNS VARCHAR2
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 NUM                            NUMBER                  IN
FUNCTION FORMAT_TIME_S RETURNS VARCHAR2
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 NUM                            NUMBER                  IN
FUNCTION GET_PLANDIFF_REPORT_XML RETURNS XMLTYPE
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 REPORT_REF                     VARCHAR2                IN     DEFAULT
 TID                            NUMBER                  IN
 METHOD                         VARCHAR2                IN
FUNCTION PREPARE_PLAN_XML_QUERY RETURNS VARCHAR2
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 PLAN_QUERY                     VARCHAR2                IN
FUNCTION PREPARE_RECORDS RETURNS DBMS_XPLAN_TYPE_TABLE
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 PLAN_CUR                       REF CURSOR              IN
 I_FORMAT_FLAGS                 BINARY_INTEGER          IN
FUNCTION VALIDATE_FORMAT RETURNS BOOLEAN
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 HASPLANSTATS                   BOOLEAN                 IN
 FORMAT                         VARCHAR2                IN
 FORMAT_FLAGS                   BINARY_INTEGER          OUT
 
DBMS_XPLAN額外資訊
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
- 查詢塊和物件別名
在使用DBMS_XPLAN 顯示執行計劃時，選擇'ADVANCED' 預定義格式作為引數或者加入'ALIAS' 控制字串，可以在輸出中看到以下內容:
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
3 - SEL$1 / O@SEL$1

其中，數字為對應的操作ID，SEL$1 為查詢塊（Query Block）的名字，T@SEL$1 和O@SEL$1 為查詢塊中物件的別名（Alias）。

語句在被提交到Oracle 後，解析器（Parser）會對SQL 語句的語法、語義進行分析，並將查詢中的檢視展開、劃分為小的查詢塊（Query Block）。這些查詢塊被傳輸給優化器後，其查詢轉換器（Query Transformer）會對它們進行進一步地查詢轉換，使優化器能生成效率更高的執行計劃。
 
- 計劃概要資料
在使用DBMS_XPLAN 顯示執行計劃時，選擇'ADVANCED' 預定義格式作為引數或者加入'OUTLINE' 控制字串，可以在輸出中看到以下內容：
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "T_USERS"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OPT_PARAM('optimizer_index_cost_adj' 60)
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
這是由一組提示（HINT）組成的資料，即執行計劃的概要（Outline）資料。計劃概要可以確保語句解析出一個特定的執行計劃，換句話說，它能確保語句執行計劃的穩定性。在9i當中，引入了一個特性：儲存概要（Stored Outline），作為一個保持語句執行計劃穩定的手段，它可以在不中止相關程式、不修改原始碼的情況下，影響優化器解析執行計劃時的行為。
 
要注意的是，執行計劃中的概要資料是在優化器選擇了最終的執行計劃後，根據該計劃產生的，用於重現該執行計劃的必要的概要資料。

提示：SQL“提示”（HINT）是內嵌在SQL 語句中，由/*+ Hint_Words */ 構成的一段註釋。它不會改變語句的邏輯結果，但可以強制優化器在選擇執行計劃的過程中，使用特定值作為優化引數，或者選擇某些特定的操作作為執行計劃的一部分。
 
在11g 中，執行計劃管理器（SQL Plan Management）的引入，能使SQL 語句獲得更加穩定的效能，官方不再推薦使用儲存概要。
DBMS_XPLAN 中顯示的概要資料對於我們來說具有相當重要的作用：
1）它可以在不對語句做OPTIMIZER_TRACE 的情況下，讓我們瞭解優化器生成該執行計劃的基本環境；
2）利用概要資料，我們可以在其他環境中重現一條語句的執行計劃，以幫助我們做問題分析（Troubleshooting）和語句調優。
 
提示：概要資料是由一組SQL 提示構成，在11g 中，Oracle 提供了一個檢視V$SQL_HINT，可用於查詢各個版本可用的SQL 提示。其中，欄位VERSION_OUTLINE 表示SQL提示是否可用於計劃概要，並且是從哪個版本開始可以被用於計劃概要。
 
- 繫結變數資訊
對於使用繫結變數， 並且在解析計劃時啟用了繫結變數窺視特性的語句， 在使用DBMS_XPLAN 顯示執行計劃時， 選擇'ADVANCED' 預定義格式作為引數或者加入'PEEKED_BINDS' 控制字串，可以在輸出中看到以下內容：
Peeked Binds (identified by position):
--------------------------------------
1 - :A (VARCHAR2(30), CSID=871): 'S'

其中，數字1 為關聯的操作ID，:A 為繫結變數名（括號中為變數資料型別，對於字元型別，還有其字符集的ID 號），最後為解析計劃時，該變數所窺視到的數值。

注意：9i和10g中，繫結變數如果存在嚴重的分佈不均，系統將會出現執行計劃錯誤的情況。11g中，可以通過adaptive Cursor Sharing自動適應共享遊標方式以解決這一問題：它會比較繫結變數不同數值的執行計劃的效率，相應的選擇最優的執行計劃。

- 分散式查詢語句資訊
分散式查詢中，會涉及對遠端資料庫上物件的查詢。該部分資訊則是將執行計劃中涉及
遠端物件查詢的語句顯示出來，語句是與執行計劃中的操作相關聯的。示例如下：
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT "USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE
","DEFAULT_TABLESPACE","TEMPORARY_TABLESPACE","CREATED","PROFILE","INITIAL_RSRC_CONSUME
R_GROUP","EXTERNAL_NAME" FROM "T_USERS" "T_USERS" (accessing 'ORA10201' )

- 註釋
註釋（Note）部分顯示了在輸出執行計劃時所探測到的問題以及相關建議。例如，以下
註釋內容告訴我們，該執行計劃使用了RBO 作為優化器，建議我們使用CBO：
Note
-----
- rule based optimizer used (consider using cbo)



Explain Plan事前調查
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--建立執行計劃使用的表
@?\rdbms\admin\utlxplan
 
Explain Plan重要的指令碼
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
A、utlxplan.sql(Utilility explain PLAN table) 建立表plan_table，用於儲存執行計劃 
B、utlxplp.sql(Utilility explain PLAN Parallel) 顯示計劃表內容，包括並行查詢計劃的資訊細節 
C、utlxpls.sql(Utilitity explain PLAN Serial) 顯示普通、序列計劃的計劃表內容

執行Explain Plan語句
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
explain PLAN [ SET STATEMENT_ID [=] < string literal > ] [ INTO < table_name > ] FOR < sql_statement >
其中：
STATEMENT_ID：是一個唯一的字串，把當前執行計劃與儲存在同一PLAN中的其它執行計劃區別開來。
TABLE_NAME：是plan表名，它結構如前所示，你可以任意設定這個名稱。
SQL_STATEMENT：是真正的SQL語句。
- 常見的簡便模式
explain plan for select name from v$datafile;
- 高階模式
explain plan set statement_id='ZHONG' for select name from v$datafile;
 
注1：每次儲存計劃時，需要將計劃表中原有的內容刪除
delete from plan_table
 
DBMS_XPLAN模擬執行計劃計算
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
SELECT A.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID
FROM PLAN_TABLE  a
WHERE STATEMENT_ID='ERIC'
ORDER BY Id;
- 常用無參模式
select * from table(dbms_xplan.display);
- 高階模式
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'ZHONG', 'ALL') );
注1：高階模式可以檢視Column Projection Information可以方便地看到每一步的操作與變數型別
ZHONG@ zhongpdb SQL>SELECT plan_table_output FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'ZHONG', 'ALL') );

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  2015 | 19382   (1)| 00:03:53 |
|   1 |  SORT AGGREGATE    |      |     1 |  2015 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 77093 |   148M| 19382   (1)| 00:03:53 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T1@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"=1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT("DATA")[22]
   2 - "DATA"[VARCHAR2,4000]

Note
-----
   - dynamic sampling used for this statement (level=2)

-- 查詢SQL HASH的執行計劃
select * from table(dbms_xplan.display_cursor('6h2j39hfk0tx1',0,'advanced'));
 
顯示執行結果：
 
DBMS_XPLAN檢視實際執行計劃
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
9i 中 DBMS_XPLAN只是提供了“理論”上的的SQL執行計劃(通過Explain Plan). 不過，9i提供了幾個動態檢視來供查詢實際的SQL(cursor)執行計劃資訊，比如v$SQL_PLAN, v$SQL_PLAN_STATISTICS).  雖然如此，DBMS_XPLAN卻沒有能夠提供返回容易閱讀的格式化好的實際執行計劃資訊。
在10g中這個問題得到了解決， DBMS_XPLAN中增加的Function, DISPLAY_CURSOR, 用來返回格式化好(就像DISPLAY一樣）的SQL實際執行的資訊。DISPLAY_CURSOR主要是依賴於Oracle的幾個動態檢視， 包括v$SQL, v$SQL_PLAN, v$SQL_PLAN_STATISTICS_ALL等。
注1：注意呼叫DISPLAY_CURSOR的時候，我們沒有傳入任何引數，這樣的話這個函式會返回當前Session中最後一個執行的SQL(cursor) 的執行計劃資訊。由於sqlplus預設會呼叫DBMS_OUTPUT.GET_LINES，影響到DISPLAY_CURSOR返回的結果，因此我們首先 set serveroutput off。
 
# 查詢上一條SQL語句的實際執行計劃
ZHONG@ zhongpdb SQL>select count(data) from t t1 where x = 1;

COUNT(DATA)
-----------
      71005

已用時間:  00: 00: 10.32
ZHONG@ zhongpdb SQL>SELECT plan_table_output FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------
SQL_ID  1vfjpvrcfbw8t, child number 1
-------------------------------------
select count(data) from t t1 where x = 1

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 19382 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |  2015 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 77093 |   148M| 19382   (1)| 00:03:53 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)
 
# 動態檢視v$sql_plan用於檢視過去的執行計
- 授權給相應使用者
grant select on v_$sql_plan to zhong;
grant select on v_$sql to zhong;
- 建立動態PLAN_TABLE儲存執行計劃
CREATE OR REPLACE VIEW dynamic_plan_table
AS 
SELECT 
    RAWTOHEX(address) || '_' || child_number  AS statement_id
    ,SYSDATE timestamp
    ,operation
    ,options
    ,object_node
    ,object_owner
    ,object_name
    ,0 object_instance
    ,optimizer
    ,search_columns
    ,id
    ,parent_id
    ,position
    ,cost
    ,cardinality
    ,bytes
    ,other_tag
    ,partition_start
    ,partition_stop
    ,partition_id
    ,other
    ,distribution
    ,cpu_cost
    ,io_cost
    ,temp_space
    ,access_predicates
    ,filter_predicates
FROM v$sql_plan;
- 執行查詢指令碼 
select plan_table_output 
from table(dbms_xplan.display('dynamic_plan_table', 
              (select rawtohex(address)||'_'||child_number x 
                 from v$sql 
                where sql_text='xxx'), 
                'serial')))
 
# 查詢已經在Share Pool的SQL語句的實際執行計劃
DISPLAY_CURSOR可以用來查詢某個具體的SQL CURSOR的實際執行資訊， 不過它需要傳入一個引數： SQL_ID. (有時候還需要CHILD_CURSOR_NO引數）
 
SQL_ID是Oracle10g新增加的，在很多動態檢視裡面都存在，用來標示一條SQL語句。
 
需要注意的是，SQL_ID只是跟在shared pool裡面的SQL語句的字串(TEXT)是一一對應的，但並不是於SQL語句對應的Cursor是一一對應的。因為一條SQL語句(TEXT)可能會對應於多個child cursors(不同的執行計劃). 正是因為這個原因，DISPLAY_CURSOR還接受另外一個引數—child cursor no (預設值為0， 因為大多數情況下每條SQL語句對應一個cursor)
 
另外， 一個Cusor(SQL_ID,  CHILD_CURSOR)的執行計劃也可能隨著時間的遷移而變化，這個會在動態檢視v$SQL_PLAN中以新的一條PLAN_HASH_VALUE來體現。
 
為了通過DISPLAY_CURSOR得到具體某個SQL（cursor）實際的執行計劃，顯然我們需要得到該SQL的SQL_ID和CHILD_NUMBER). 可以通過查詢動態檢視v$SQL來得到SQL_ID。
 
col sql_text format a80
SELECT sql_id, child_number , sql_text FROM v$sql WHERE  LOWER(sql_text) LIKE 'select count(data)%';
SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------------------------------------
c602jxkbz5r0r            0 select count(data) from t t1 where x = 2
1vfjpvrcfbw8t            1 select count(data) from t t1 where x = 1
c602jxkbz5r0r            1 select count(data) from t t1 where x = 2
1vfjpvrcfbw8t            0 select count(data) from t t1 where x = 1
 
SELECT plan_table_output FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1vfjpvrcfbw8t', 0, 'ALL'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1vfjpvrcfbw8t, child number 0
-------------------------------------
select count(data) from t t1 where x = 1

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 19382 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |  2015 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 77093 |   148M| 19382   (1)| 00:03:53 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T1@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"=1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT("DATA")[22]
   2 - "DATA"[VARCHAR2,4000]

Note
-----
   - dynamic sampling used for this statement (level=2)


已選擇35行。
 
DBMS_XPLAN查詢執行時runtime statistics
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
CREATE TABLE t1 AS SELECT * FROM all_objects;

CREATE TABLE t2 AS SELECT * FROM all_objects;

CREATE TABLE t3 AS SELECT * FROM all_objects WHERE ROWNUM <= 100;

CREATE INDEX it3 ON t3 (object_id);

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(user,'T1');
     DBMS_STATS.GATHER_TABLE_STATS(user,'T2');
     DBMS_STATS.GATHER_TABLE_STATS(user,'T3',cascade=>TRUE);
END;
/

ALTER SESSION SET STATISTICS_LEVEL = ALL;

SELECT /*+ ORDERED */
         t1.object_type
  ,      COUNT(*) AS object_count
  FROM   t1
  ,      t2
  ,      t3
  WHERE  t1.object_id = t2.object_id
  AND    t2.object_id = t3.object_id
  GROUP  BY t1.object_type;

OBJECT_TYPE         OBJECT_COUNT
------------------- ------------
EDITION                        1
SEQUENCE                       1
TABLE                         37
INDEX                         56
CLUSTER                        5

SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'RUNSTATS_LAST'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  7b49gb1jczckb, child number 0
-------------------------------------
SELECT /*+ ORDERED */          t1.object_type   ,      COUNT(*) AS
object_count   FROM   t1   ,      t2   ,      t3   WHERE  t1.object_id
= t2.object_id   AND    t2.object_id = t3.object_id   GROUP  BY
t1.object_type

Plan hash value: 2827993557

---------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      5 |00:00:00.21 |    2035 |
|   1 |  HASH GROUP BY       |      |      1 |     39 |      5 |00:00:00.21 |    2035 |
|*  2 |   HASH JOIN          |      |      1 |    100 |    100 |00:00:00.21 |    2035 |
|   3 |    INDEX FULL SCAN   | IT3  |      1 |    100 |    100 |00:00:00.01 |       1 |
|*  4 |    HASH JOIN         |      |      1 |  71005 |  71005 |00:00:00.18 |    2034 |
|   5 |     TABLE ACCESS FULL| T1   |      1 |  71005 |  71005 |00:00:00.03 |    1017 |
|   6 |     TABLE ACCESS FULL| T2   |      1 |  71006 |  71006 |00:00:00.02 |    1017 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

已選擇27行。
Oracle provides six high-level statistics as follows:
    - E-Rows: estimated rowcounts flowing through each plan step;
    - A-Rows: the actual rowcounts flowing through each plan step;
    - Buffers: reads from the buffer cache (LIO);
    - Reads: physical reads (PIO);
    - Writes: physical writes (e.g. to temp); and
    - A-Time: elapsed time of each plan step.
 

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

相關文章