(1) v$sql
一條語(yǔ)句可以映射多個(gè)cursor,因?yàn)閷?duì)象所指的cursor可以有不同用戶(如例1)。如果有多個(gè)cursor(子游標(biāo))存在，在V$SQLAREA為所有cursor提供集合信息。
例1：
這里介紹以下child cursor
user A: select * from tbl
user B: select * from tbl
大家認(rèn)為這兩條語(yǔ)句是不是一樣的啊，可能會(huì)有很多人會(huì)說(shuō)是一樣的，但我告訴你不一定，那為什么呢？
這個(gè)tblA看起來(lái)是一樣的，但是不一定哦，一個(gè)是A用戶的, 一個(gè)是B用戶的，這時(shí)他們的執(zhí)行計(jì)劃分析代碼差別可能就大了哦，改下寫(xiě)法大家就明白了:
select * from A.tbl
select * from B.tbl
在個(gè)別cursor上，v$sql可被使用。該視圖包含cursor級(jí)別資料。當(dāng)試圖定位session或用戶以分析cursor時(shí)被使用。
PLAN_HASH_VALUE列存儲(chǔ)的是數(shù)值表示的cursor執(zhí)行計(jì)劃。可被用來(lái)對(duì)比執(zhí)行計(jì)劃。PLAN_HASH_VALUE讓你不必一行一行對(duì)比即可輕松鑒別兩條執(zhí)行計(jì)劃是否相同。
V$SQL中的列說(shuō)明：
l SQL_TEXT：SQL文本的前1000個(gè)字符
l SHARABLE_MEM：占用的共享內(nèi)存大小(單位：byte)
l PERSISTENT_MEM：生命期內(nèi)的固定內(nèi)存大小(單位：byte)
l RUNTIME_MEM：執(zhí)行期內(nèi)的固定內(nèi)存大小
l SORTS：完成的排序數(shù)
l LOADED_VERSIONS：顯示上下文堆是否載入，1是0否
l OPEN_VERSIONS：顯示子游標(biāo)是否被鎖，1是0否
l USERS_OPENING：執(zhí)行語(yǔ)句的用戶數(shù)
l FETCHES：SQL語(yǔ)句的fetch數(shù)。
l EXECUTIONS：自它被載入緩存庫(kù)后的執(zhí)行次數(shù)
l USERS_EXECUTING：執(zhí)行語(yǔ)句的用戶數(shù)
l LOADS：對(duì)象被載入過(guò)的次數(shù)
l FIRST_LOAD_TIME：初次載入時(shí)間
l INVALIDATIONS：無(wú)效的次數(shù)
l PARSE_CALLS：解析調(diào)用次數(shù)
l DISK_READS：讀磁盤次數(shù)
l BUFFER_GETS：讀緩存區(qū)次數(shù)
l ROWS_PROCESSED：解析SQL語(yǔ)句返回的總列數(shù)
l COMMAND_TYPE：命令類型代號(hào)
l OPTIMIZER_MODE：SQL語(yǔ)句的優(yōu)化器模型
l OPTIMIZER_COST：優(yōu)化器給出的本次查詢成本
l PARSING_USER_ID：第一個(gè)解析的用戶ID
l PARSING_SCHEMA_ID：第一個(gè)解析的計(jì)劃ID
l KEPT_VERSIONS：指出是否當(dāng)前子游標(biāo)被使用DBMS_SHARED_POOL包標(biāo)記為常駐內(nèi)存
l ADDRESS：當(dāng)前游標(biāo)父句柄地址
l TYPE_CHK_HEAP：當(dāng)前堆類型檢查說(shuō)明
l HASH_VALUE：緩存庫(kù)中父語(yǔ)句的Hash值
l PLAN_HASH_VALUE：數(shù)值表示的執(zhí)行計(jì)劃。
l CHILD_NUMBER：子游標(biāo)數(shù)量
l MODULE：在第一次解析這條語(yǔ)句是通過(guò)調(diào)用DBMS_APPLICATION_INFO.SET_MODULE設(shè)置的模塊名稱。
l ACTION：在第一次解析這條語(yǔ)句是通過(guò)調(diào)用DBMS_APPLICATION_INFO.SET_ACTION設(shè)置的動(dòng)作名稱。
l SERIALIZABLE_ABORTS：事務(wù)未能序列化次數(shù)
l OUTLINE_CATEGORY：如果outline在解釋cursor期間被應(yīng)用，那么本列將顯示出outline各類，否則本列為空
l CPU_TIME：解析/執(zhí)行/取得等CPU使用時(shí)間(單位，毫秒)
l ELAPSED_TIME：解析/執(zhí)行/取得等消耗時(shí)間(單位，毫秒)
l OUTLINE_SID：outline session標(biāo)識(shí)
l CHILD_ADDRESS：子游標(biāo)地址
l SQLTYPE：指出當(dāng)前語(yǔ)句使用的SQL語(yǔ)言版本
l REMOTE：指出是否游標(biāo)是一個(gè)遠(yuǎn)程映象(Y/N)
l OBJECT_STATUS：對(duì)象狀態(tài)(VALID or INVALID)
l IS_OBSOLETE：當(dāng)子游標(biāo)的數(shù)量太多的時(shí)候，指出游標(biāo)是否被廢棄(Y/N)
(2)-V$SQL_PLAN
本視圖提供了一種方式檢查那些執(zhí)行過(guò)的并且仍在緩存中的cursor的執(zhí)行計(jì)劃。
通常，本視圖提供的信息與打印出的EXPLAIN PLAN非常相似，不過(guò)，EXPLAIN PLAN顯示的是理論上的計(jì)劃，并不一定在執(zhí)行的時(shí)候就會(huì)被使用，但V$SQL_PLAN中包括的是實(shí)際被使用的計(jì)劃。獲自EXPLAIN PLAN語(yǔ)句的執(zhí)行計(jì)劃跟具體執(zhí)行的計(jì)劃可以不同，因?yàn)?/span>cursor可能被不同的session參數(shù)值編譯(如，HASH_AREA_SIZE)。
V$SQL_PLAN中數(shù)據(jù)可以：
l 確認(rèn)當(dāng)前的執(zhí)行計(jì)劃
l 鑒別創(chuàng)建表索引效果
l 尋找cursor包括的存取路徑(例如，全表查詢或范圍索引查詢)
l 鑒別索引的選擇是否最優(yōu)
l 決定是否最優(yōu)化選擇的詳細(xì)執(zhí)行計(jì)劃(如，nested loops join)如開(kāi)發(fā)者所愿。
本視圖同時(shí)也可被用于當(dāng)成一種關(guān)鍵機(jī)制在計(jì)劃對(duì)比中。計(jì)劃對(duì)比通常用于下列各項(xiàng)發(fā)生改變時(shí)：
l 刪除和新建索引
l 在數(shù)據(jù)庫(kù)對(duì)象上執(zhí)行分析語(yǔ)句
l 修改初始參數(shù)值
l 從rule-based切換至cost-based優(yōu)化方式
l 升級(jí)應(yīng)用程序或數(shù)據(jù)庫(kù)到新版本之后
如果之前的計(jì)劃仍然在(例如，從V$SQL_PLAN選擇出記錄并保存到oracle表中供參考)，那么就有可能去鑒別一條SQL語(yǔ)句在執(zhí)行計(jì)劃改變后性能方面有什么變化。
注意：
Oracle公司強(qiáng)烈推薦你使用DBMS_STATS包而非ANALYZE收集優(yōu)化統(tǒng)計(jì)。該包可以讓你平行地搜集統(tǒng)計(jì)項(xiàng)，收集分區(qū)對(duì)象(partitioned objects)的全集統(tǒng)計(jì)，并且通過(guò)其它方式更好的調(diào)整你的統(tǒng)計(jì)收集方式。此處，cost-based優(yōu)化器將最終使用被DBMS_STATS收集的統(tǒng)計(jì)項(xiàng)。瀏覽Oracle9i Supplied PL/SQL包和類型參考以獲得關(guān)于此包的更多信息。
不過(guò)，你必須使用ANALYZE語(yǔ)句而非DBMS_STATS進(jìn)行統(tǒng)計(jì)收集，不涉及cost-based優(yōu)化器，就像：
·使用VALIDATE或LIST CHAINED ROWS子句
·在freelist blocks上收集信息。
V$SQL_PLAN中的常用列：
除了一些新加列，本視圖幾乎包括所有的PLAN_TABLE列，那些同樣存在于PLAN_TABLE中的列擁有相同的值：
l ADDRESS：當(dāng)前cursor父句柄位置
l HASH_VALUE：在library cache中父語(yǔ)句的HASH值。
ADDRESS和HASH_VALUE這兩列可以被用于連接v$sqlarea查詢 cursor-specific 信息。
l CHILD_NUMBER：使用這個(gè)執(zhí)行計(jì)劃的子cursor數(shù)
列ADDRESS,HASH_VALUE以及CHILD_NUMBER可被用于連接v$sql查詢子cursor信息。
l OPERATION: 在各步驟執(zhí)行內(nèi)部操作的名稱，例如：TABLE ACCESS
l OPTIONS: 描述列OPERATION在操作上的變種，例如：FULL
l OBJECT_NODE: 用于訪問(wèn)對(duì)象的數(shù)據(jù)庫(kù)鏈接database link 的名稱對(duì)于使用并行執(zhí)行的本地查詢?cè)摿心軌蛎枋霾僮髦休敵龅拇涡颉?/span>
l OBJECT#: 表或索引對(duì)象數(shù)量
l OBJECT_OWNER: 對(duì)于包含有表或索引的架構(gòu)schema 給出其所有者的名稱
l OBJECT_NAME: 表或索引名
l OPTIMIZER: 執(zhí)行計(jì)劃中首列的默認(rèn)優(yōu)化模式；例如，CHOOSE。比如業(yè)務(wù)是個(gè)存儲(chǔ)數(shù)據(jù)庫(kù)，它將告知是否對(duì)象是最優(yōu)化的。
l ID: 在執(zhí)行計(jì)劃中分派到每一步的序號(hào)。
l PARENT_ID: 對(duì)ID 步驟的輸出進(jìn)行操作的下一個(gè)執(zhí)行步驟的ID。
l DEPTH: 業(yè)務(wù)樹(shù)深度(或級(jí))。
l POSITION: 對(duì)于具有相同PARENT_ID 的操作其相應(yīng)的處理次序。
l COST: cost-based方式優(yōu)化的操作開(kāi)銷的評(píng)估，如果語(yǔ)句使用rule-based方式，本列將為空。
l CARDINALITY: 根據(jù)cost-based方式操作所訪問(wèn)的行數(shù)的評(píng)估。
l BYTES: 根據(jù)cost-based方式操作產(chǎn)生的字節(jié)的評(píng)估，。
l OTHER_TAG: 其它列的內(nèi)容說(shuō)明。
l PARTITION_START: 范圍存取分區(qū)中的開(kāi)始分區(qū)。
l PARTITION_STOP: 范圍存取分區(qū)中的停止分區(qū)。
l PARTITION_ID: 計(jì)算PARTITION_START和PARTITION_STOP這對(duì)列值的步數(shù)
l OTHER: 其它信息即執(zhí)行步驟細(xì)節(jié)，供用戶參考。
l DISTRIBUTION: 為了并行查詢，存儲(chǔ)用于從生產(chǎn)服務(wù)器到消費(fèi)服務(wù)器分配列的方法
l CPU_COST: 根據(jù)cost-based方式CPU操作開(kāi)銷的評(píng)估。如果語(yǔ)句使用rule-based方式，本列為空。
l IO_COST: 根據(jù)cost-based方式I/O操作開(kāi)銷的評(píng)估。如果語(yǔ)句使用rule-based方式，本列為空。
l TEMP_SPACE: cost-based方式操作(sort or hash-join)的臨時(shí)空間占用評(píng)估。如果語(yǔ)句使用rule-based方式，本列為空。
l ACCESS_PREDICATES: 指明以便在存取結(jié)構(gòu)中定位列，例如，在范圍索引查詢中的開(kāi)始或者結(jié)束位置。
l FILTER_PREDICATES: 在生成數(shù)據(jù)之前即指明過(guò)濾列。
CONNECT BY操作產(chǎn)生DEPTH列替換LEVEL偽列，有時(shí)被用于在SQL腳本中幫助indent PLAN_TABLE數(shù)據(jù)
V$SQL_PLAN中的連接列
列ADDRESS,HASH_VALUE和CHILD_NUMBER被用于連接V$SQL或V$SQLAREA來(lái)獲取cursor-specific信息，例如，BUFFER_GET,或連接V$SQLTEXT獲取完整的SQL語(yǔ)句。
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
確認(rèn)SQL語(yǔ)句的優(yōu)化計(jì)劃
下列語(yǔ)句顯示一條指定SQL語(yǔ)句的執(zhí)行計(jì)劃。查看一條SQL語(yǔ)句的執(zhí)行計(jì)劃是調(diào)整優(yōu)化SQL語(yǔ)句的第一步。這條被查詢到執(zhí)行計(jì)劃的SQL語(yǔ)句是通過(guò)語(yǔ)句的HASH_VALUE和ADDRESS列識(shí)別。分兩步執(zhí)行：
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