第三篇 - (1) - V$SQL
V$SQL 中存儲具體的SQL語句。
一條語句可以映射 多 個cursor,因為對象所指的cursor可以有不同用戶 ( 如例1) 。如果有多個cursor(子游標)存在， 在 V$SQLAREA 為所有cursor提供集合信息。
例1：
這里介紹以下child cursor
user A: select * from tbl
user B: select * from tbl
大家認為這兩條語句是不是一樣的啊，可能會有很多人會說是一樣的，但我告訴你不一定，那為什么呢？
這個tblA看起來是一樣的，但是不一定哦，一個是A用戶的, 一個是B用戶的，這時他們的執行計劃分析代碼差別可能就大了哦，改下寫法大家就明白了:
select * from A.tbl
select * from B.tbl
在個別cursor上，v$sql可被使用。該視圖包含cursor級別資料。當試圖定位session或用戶以分析cursor時被使用。
PLAN_HASH_VALUE列存儲的是數值表示的cursor執行計劃。可被用來對比執行計劃。PLAN_HASH_VALUE讓你不必一行一行對比即可輕松鑒別兩條執行計劃是否相同 。
V$SQL 中的列說明：
- SQL_TEXT ：SQL文本的前1000個字符
- SHARABLE_MEM ：占用的共享內存大小(單位：byte)
- PERSISTENT_MEM ：生命期內的固定內存大小(單位：byte)
- RUNTIME_MEM ：執行期內的固定內存大小
- SORTS ：完成的排序數
- LOADED_VERSIONS ：顯示上下文堆是否載入，1是0否
- OPEN_VERSIONS ：顯示子游標是否被鎖，1是0否
- USERS_OPENING ：執行語句的用戶數
- FETCHES ：SQL語句的fetch數。
- EXECUTIONS ：自它被載入緩存庫后的執行次數
- USERS_EXECUTING ：執行語句的用戶數
- LOADS ：對象被載入過的次數
- FIRST_LOAD_TIME ：初次載入時間
- INVALIDATIONS ：無效的次數
- PARSE_CALLS ：解析調用次數
- DISK_READS ：讀磁盤次數
- BUFFER_GETS ：讀緩存區次數
- ROWS_PROCESSED ：解析SQL語句返回的總列數
- COMMAND_TYPE ：命令類型代號
- OPTIMIZER_MODE ：SQL語句的優化器模型
- OPTIMIZER_COST ：優化器給出的本次查詢成本
- PARSING_USER_ID ：第一個解析的用戶ID
- PARSING_SCHEMA_ID ：第一個解析的計劃ID
- KEPT_VERSIONS ：指出是否當前子游標被使用DBMS_SHARED_POOL包標記為常駐內存
- ADDRESS ：當前游標父句柄地址
- TYPE_CHK_HEAP ：當前堆類型檢查說明
- HASH_VALUE ：緩存庫中父語句的Hash值
- PLAN_HASH_VALUE ：數值表示的執行計劃。
- CHILD_NUMBER ：子游標數量
- MODULE ：在第一次解析這條語句是通過調用DBMS_APPLICATION_INFO.SET_MODULE設置的模塊名稱。
- ACTION ：在第一次解析這條語句是通過調用DBMS_APPLICATION_INFO.SET_ACTION設置的動作名稱。
- SERIALIZABLE_ABORTS ：事務未能序列化次數
- OUTLINE_CATEGORY ：如果outline在解釋cursor期間被應用，那么本列將顯示出outline各類，否則本列為空
- CPU_TIME ：解析/執行/取得等CPU使用時間(單位，毫秒)
- ELAPSED_TIME ：解析/執行/取得等消耗時間(單位，毫秒)
- OUTLINE_SID ：outline session標識
- CHILD_ADDRESS ：子游標地址
- SQLTYPE ：指出當前語句使用的SQL語言版本
- REMOTE ：指出是否游標是一個遠程映象(Y/N)
- OBJECT_STATUS ：對象狀態(VALID or INVALID)
- IS_OBSOLETE ：當子游標的數量太多的時候，指出游標是否被廢棄(Y/N)
第三篇 - (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)如開發者所愿。
本視圖同時也可被用于當成一種關鍵機制在計劃對比中。計劃對比通常用于下列各項發生改變時：
- l 刪除和新建索引
- l 在數據庫對象上執行分析語句
- l 修改初始參數值
- l 從rule-based切換至cost-based優化方式
- l 升級應用程序或數據庫到新版本之后
如果之前的計劃仍然在(例如，從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
|