學習動态性能表 第三篇-(1)-v$sql

學習動态性能表

第三篇-(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中的列說明：

l         SQL_TEXT：SQL文本的前1000個字元

l         SHARABLE_MEM：占用的共享記憶體大小(機關：byte)

l         PERSISTENT_MEM：生命期内的固定記憶體大小(機關：byte)

l         RUNTIME_MEM：執行期内的固定記憶體大小

l         SORTS：完成的排序數

l         LOADED_VERSIONS：顯示上下文堆是否載入，1是0否

l         OPEN_VERSIONS：顯示子遊标是否被鎖，1是0否

l         USERS_OPENING：執行語句的使用者數

l         FETCHES：SQL語句的fetch數。

l         EXECUTIONS：自它被載入緩存庫後的執行次數

l         USERS_EXECUTING：執行語句的使用者數

l         LOADS：對象被載入過的次數

l         FIRST_LOAD_TIME：初次載入時間

l         INVALIDATIONS：無效的次數

l         PARSE_CALLS：解析調用次數

l         DISK_READS：讀磁盤次數

l         BUFFER_GETS：讀緩存區次數

l         ROWS_PROCESSED：解析SQL語句傳回的總列數

l         COMMAND_TYPE：指令類型代号

l         OPTIMIZER_MODE：SQL語句的優化器模型

l         OPTIMIZER_COST：優化器給出的本次查詢成本

l         PARSING_USER_ID：第一個解析的使用者ID

l         PARSING_SCHEMA_ID：第一個解析的計劃ID

l         KEPT_VERSIONS：指出是否目前子遊标被使用DBMS_SHARED_POOL包标記為常駐記憶體

l         ADDRESS：目前遊标父句柄位址

l         TYPE_CHK_HEAP：目前堆類型檢查說明

l         HASH_VALUE：緩存庫中父語句的Hash值

l         PLAN_HASH_VALUE：數值表示的執行計劃。

l         CHILD_NUMBER：子遊标數量

l         MODULE：在第一次解析這條語句是通過調用DBMS_APPLICATION_INFO.SET_MODULE設定的子產品名稱。

l         ACTION：在第一次解析這條語句是通過調用DBMS_APPLICATION_INFO.SET_ACTION設定的動作名稱。

l         SERIALIZABLE_ABORTS：事務未能序列化次數

l         OUTLINE_CATEGORY：如果outline在解釋cursor期間被應用，那麼本列将顯示出outline各類，否則本列為空

l         CPU_TIME：解析/執行/取得等CPU使用時間(機關，毫秒)

l         ELAPSED_TIME：解析/執行/取得等消耗時間(機關，毫秒)

l         OUTLINE_SID：outline session辨別

l         CHILD_ADDRESS：子遊标位址

l         SQLTYPE：指出目前語句使用的SQL語言版本

l         REMOTE：指出是否遊标是一個遠端映象(Y/N)

l         OBJECT_STATUS：對象狀态(VALID or INVALID)

l         IS_OBSOLETE：當子遊标的數量太多的時候，指出遊标是否被廢棄(Y/N)

第三篇-(2)-V$SQL_PLAN 2007.5.28

　　本視圖提供了一種方式檢查那些執行過的并且仍在緩存中的cursor的執行計劃。

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

V$SQL_PLAN中資料可以：

l         确認目前的執行計劃

l         鑒别建立表索引效果

l         尋找cursor包括的存取路徑(例如，全表查詢或範圍索引查詢)

l         鑒别索引的選擇是否最優

l         決定是否最優化選擇的詳細執行計劃(如，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中的列擁有相同的值：

l         ADDRESS：目前cursor父句柄位置

l         HASH_VALUE：在library cache中父語句的HASH值。

ADDRESS和HASH_VALUE這兩列可以被用于連接配接v$sqlarea查詢 cursor-specific 資訊。

l             CHILD_NUMBER：使用這個執行計劃的子cursor數

列ADDRESS,HASH_VALUE以及CHILD_NUMBER可被用于連接配接v$sql查詢子cursor資訊。

l         OPERATION: 在各步驟執行内部操作的名稱，例如：TABLE ACCESS

l         OPTIONS: 描述列OPERATION在操作上的變種，例如：FULL

l         OBJECT_NODE: 用于通路對象的資料庫連結database link 的名稱對于使用并行執行的本地查詢該列能夠描述操作中輸出的次序。

l         OBJECT#: 表或索引對象數量

l         OBJECT_OWNER: 對于包含有表或索引的架構schema 給出其所有者的名稱

l         OBJECT_NAME: 表或索引名

l         OPTIMIZER: 執行計劃中首列的預設優化模式；例如，CHOOSE。比如業務是個存儲資料庫，它将告知是否對象是最優化的。

l         ID: 在執行計劃中分派到每一步的序号。

l         PARENT_ID: 對ID 步驟的輸出進行操作的下一個執行步驟的ID。

l         DEPTH: 業務樹深度(或級)。

l         POSITION: 對于具有相同PARENT_ID 的操作其相應的處理次序。

l         COST: cost-based方式優化的操作開銷的評估，如果語句使用rule-based方式，本列将為空。

l         CARDINALITY: 根據cost-based方式操作所通路的行數的評估。

l         BYTES: 根據cost-based方式操作産生的位元組的評估，。

l         OTHER_TAG: 其它列的内容說明。

l         PARTITION_START: 範圍存取分區中的開始分區。

l         PARTITION_STOP: 範圍存取分區中的停止分區。

l         PARTITION_ID: 計算PARTITION_START和PARTITION_STOP這對列值的步數

l         OTHER: 其它資訊即執行步驟細節，供使用者參考。

l         DISTRIBUTION: 為了并行查詢，存儲用于從生産伺服器到消費伺服器配置設定列的方法

l         CPU_COST: 根據cost-based方式CPU操作開銷的評估。如果語句使用rule-based方式，本列為空。

l         IO_COST: 根據cost-based方式I/O操作開銷的評估。如果語句使用rule-based方式，本列為空。

l         TEMP_SPACE: cost-based方式操作(sort or hash-join)的臨時空間占用評估。如果語句使用rule-based方式，本列為空。

l         ACCESS_PREDICATES: 指明以便在存取結構中定位列，例如，在範圍索引查詢中的開始或者結束位置。

l         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