學習動態效能表(三)-(2)-V$SQL_PLAN

學習動態效能表(三)-(2)-V$SQL_PLAN

　　本檢視提供了一種方式檢查那些執行過的並且仍在快取中的cursor的執行計劃。
　　通常，本檢視提供的資訊與列印出的EXPLAIN PLAN非常相似，不過，EXPLAIN PLAN顯示的是理論上的計劃，並不一定在執行的時候就會被使用，但V$SQL_PLAN中包括的是實際被使用的計劃。獲自EXPLAIN PLAN語句的執行計劃跟具體執行的計劃可以不同，因為cursor可能被不同的session引數值編譯(如，HASH_AREA_SIZE)。

V$SQL_PLAN中資料可以：　　確認當前的執行計劃
　　鑑別建立表索引效果
　　尋找cursor包括的存取路徑(例如，全表查詢或範圍索引查詢)
　　鑑別索引的選擇是否最優
　　決定是否最最佳化選擇的詳細執行計劃(如，nested loops join)如開發者所願。

　　本檢視同時也可被用於當成一種關鍵機制在計劃對比中。計劃對比通常用於下列各項發生改變時：

　　刪除和新建索引
　　在資料庫物件上執行分析語句
　　修改初始引數值
　　從rule-based切換至cost-based最佳化方式
　　升級應用程式或資料庫到新版本之後

　　如果之前的計劃仍然在(例如，從V$SQL_PLAN選擇出記錄並儲存到oracle表中供參考)，那麼就有可能去鑑別一條SQL語句在執行計劃改變後效能方面有什麼變化。

注意：
Oracle公司強烈推薦你使用DBMS_STATS包而非ANALYZE收集最佳化統計。該包可以讓你平行地蒐集統計項，收集分割槽物件(partitioned objects)的全集統計，並且透過其它方式更好的調整你的統計收集方式。此處，cost-based最佳化器將最終使用被DBMS_STATS收集的統計項。瀏覽Oracle9i Supplied PL/SQL包和型別參考以獲得關於此包的更多資訊。

不過，你必須使用ANALYZE語句而非DBMS_STATS進行統計收集，不涉及cost-based最佳化器，就像：
·使用VALIDATE或LIST CHAINED ROWS子句
·在freelist blocks上收集資訊。

V$SQL_PLAN中的常用列：

除了一些新加列，本檢視幾乎包括所有的PLAN_TABLE列，那些同樣存在於PLAN_TABLE中的列擁有相同的值：
ADDRESS：當前cursor父控制程式碼位置
HASH_VALUE：在library cache中父語句的HASH值。
ADDRESS和HASH_VALUE這兩列可以被用於連線v$sqlarea查詢 cursor-specific 資訊。
CHILD_NUMBER：使用這個執行計劃的子cursor數
列ADDRESS,HASH_VALUE以及CHILD_NUMBER可被用於連線v$sql查詢子cursor資訊。
OPERATION: 在各步驟執行內部操作的名稱，例如：TABLE ACCESS
OPTIONS: 描述列OPERATION在操作上的變種，例如：FULL
OBJECT_NODE: 用於訪問物件的資料庫連結database link 的名稱對於使用並行執行的本地查詢該列能夠描述操作中輸出的次序。
OBJECT#: 表或索引物件數量
OBJECT_OWNER: 對於包含有表或索引的架構schema 給出其所有者的名稱
OBJECT_NAME: 表或索引名
OPTIMIZER: 執行計劃中首列的預設最佳化模式；例如，CHOOSE。比如業務是個儲存資料庫，它將告知是否物件是最最佳化的。
ID: 在執行計劃中分派到每一步的序號。
PARENT_ID: 對ID 步驟的輸出進行操作的下一個執行步驟的ID。
DEPTH: 業務樹深度(或級)。
POSITION: 對於具有相同PARENT_ID 的操作其相應的處理次序。
COST: cost-based方式最佳化的操作開銷的評估，如果語句使用rule-based方式，本列將為空。
CARDINALITY: 根據cost-based方式操作所訪問的行數的評估。
BYTES: 根據cost-based方式操作產生的位元組的評估，。
OTHER_TAG: 其它列的內容說明。
PARTITION_START: 範圍存取分割槽中的開始分割槽。
PARTITION_STOP: 範圍存取分割槽中的停止分割槽。
PARTITION_ID: 計算PARTITION_START和PARTITION_STOP這對列值的步數
OTHER: 其它資訊即執行步驟細節，供使用者參考。
DISTRIBUTION: 為了並行查詢，儲存用於從生產伺服器到消費伺服器分配列的方法
CPU_COST: 根據cost-based方式CPU操作開銷的評估。如果語句使用rule-based方式，本列為空。
IO_COST: 根據cost-based方式I/O操作開銷的評估。如果語句使用rule-based方式，本列為空。
TEMP_SPACE: cost-based方式操作(sort or hash-join)的臨時空間佔用評估。如果語句使用rule-based方式，本列為空。
ACCESS_PREDICATES: 指明以便在存取結構中定位列，例如，在範圍索引查詢中的開始或者結束位置。
FILTER_PREDICATES: 在生成資料之前即指明過濾列。

CONNECT BY操作產生DEPTH列替換LEVEL偽列，有時被用於在SQL指令碼中幫助indent PLAN_TABLE資料

V$SQL_PLAN中的連線列
　　列ADDRESS,HASH_VALUE和CHILD_NUMBER被用於連線V$SQL或V$SQLAREA來獲取cursor-specific資訊，例如，BUFFER_GET,或連線V$SQLTEXT獲取完整的SQL語句。
Column View Joined Column(s)
ADDRESS, HASH_VALUE V$SQLAREA ADDRESS, HASH_VALUE
ADDRESS,HASH_VALUE,CHILD_NUMBER V$SQL ADDRESS,HASH_VALUE,CHILD_NUMBER
ADDRESS, HASH_VALUE V$SQLTEXT ADDRESS, HASH_VALUE

確認SQL語句的最佳化計劃
　　下列語句顯示一條指定SQL語句的執行計劃。檢視一條SQL語句的執行計劃是調整最佳化SQL語句的第一步。這條被查詢到執行計劃的SQL語句是透過語句的HASH_VALUE和ADDRESS列識別。分兩步執行：

1.SELECT sql_text, address, hash_value FROM v$sql
WHERE sql_text like '%TAG%';

SQL_TEXT ADDRESS HASH_VALUE
-------- -------- ----------
82157784 1224822469

2.SELECT operation, options, object_name, cost FROM v$sql_plan
WHERE address = '82157784' AND hash_value = 1224822469;

OPERATION OPTIONS OBJECT_NAME COST
-------------------- ------------- ------------------ ----
SELECT STATEMENT 5
SORT
AGGREGATE
HASH JOIN 5
TABLE ACCESS FULL DEPARTMENTS 2
TABLE ACCESS FULL EMPLOYEES 2