Oracle高資源消耗SQL語(yǔ)句定位

Oracle高資源消耗SQL語(yǔ)句定位

http://www.ecdoer.com/post/oracle-highcost-sql-locate.html

相關(guān)系統(tǒng)視圖功能與區(qū)別

v$sql和v$sqlarea基本相同，記錄了共享SQL區(qū)（share pool）中SQL統(tǒng)計(jì)信息，如內(nèi)存消耗、IO（物理磁盤讀和邏輯內(nèi)存讀）、排序操作、哈希ID等數(shù)據(jù)。不同之處在于v$sql為每一條SQL保留一個(gè)條目，而v$sqlarea中根據(jù)sql_text（需要注意，該處存儲(chǔ)的為當(dāng)前SQL指針的前1000個(gè)字符，也就是說(shuō)這里記錄的SQL可能是不完整的?。┻M(jìn)行g(shù)roup by，統(tǒng)計(jì)列進(jìn)行sum()，通過(guò)version_count計(jì)算子指針的個(gè)數(shù)。

然而，文本（sql_text）相同的SQL語(yǔ)句在數(shù)據(jù)庫(kù)中意義可能完全不同。比如數(shù)據(jù)庫(kù)中存在兩個(gè)用戶User1和User2，這兩個(gè)用戶各擁有一張數(shù)據(jù)表EMP。那么當(dāng)兩個(gè)用戶發(fā)出查詢select count(*) from emp;時(shí)各自訪問(wèn)自己SCHEMA中的表EMP，而兩者表內(nèi)容不同所以其資源消耗肯定也不同。此時(shí)，在v$sql中會(huì)有這兩條完全一樣的SQL各自的統(tǒng)計(jì)信息，而在v$sqlarea中sql_text相同的2個(gè)指針會(huì)合并起來(lái)，執(zhí)行次數(shù)、DISK_READS、BUFFER_GETS等統(tǒng)計(jì)信息都會(huì)累加（sum），version_count會(huì)顯示為2，這就是v$sqlarea的聚合作用。

v$sqltext中沒有統(tǒng)計(jì)信息，然而卻存儲(chǔ)著完整的SQL語(yǔ)句及其哈希ID等。

對(duì)于這三者，我們可以使用視圖v$fixed_view_definition來(lái)查看視圖的源表，如下：

SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQL';

SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQLAREA';

SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQLTEXT';

注：視圖名為V$SQL但該視圖的源又是GV$SQL，所以直接使用GV$SQL，其他兩個(gè)也如此。

通過(guò)以上3條語(yǔ)句可以發(fā)現(xiàn)，V$SQL數(shù)據(jù)來(lái)源X$KGLCURSOR_CHILD，其實(shí)數(shù)據(jù)還是來(lái)源于X$KGLOB；而V$SQLAREA數(shù)據(jù)來(lái)源X$KGLCURSOR_CHILD_SQLID本質(zhì)是對(duì)X$KGLCURSOR_CHILD按照sql_id等字段分組匯總后的結(jié)果；V$SQLTEXT數(shù)據(jù)來(lái)源X$KGLNA。

v$session主要用來(lái)確定會(huì)話相關(guān)信息，如通過(guò)SID和SERIAL#來(lái)唯一確定一個(gè)session（SID可能會(huì)重復(fù)）、會(huì)話擁有者用戶名USERNAME、會(huì)話狀態(tài)（active:正在執(zhí)行SQL語(yǔ)句、inactive:等待操作、killed:被殺死）、會(huì)話由哪個(gè)客戶端發(fā)起（MACHINE、TERMINAL）、正在執(zhí)行什么SQL（通過(guò)SQL_ADDRESS、SQL_HASH_VALUE、SQL_ID、SQL_CHILD_NUMBER確定，有這些再借助v$sqltext就能知道）、甚至上一次執(zhí)行的SQL是什么（通過(guò)PREV_SQL_ADDRESS等確定）、鎖等待相關(guān)信息（如所在表、文件、塊、被鎖行）等。

高資源消耗SQL查找定位

1）查看讀硬盤多或占用內(nèi)存可能多的SQL：

select sql_text, disk_reads, buffer_gets, parsing_schema_name, executions

from v$sqlarea

order by disk_reads desc;

說(shuō)明：?jiǎn)渭儚腣$sqlarea中是無(wú)法查出每個(gè)SQL消耗的內(nèi)存量的，但我們可以借助磁盤讀次數(shù)間接反映可能的消耗內(nèi)存量較大的SQL語(yǔ)句，然后再借助執(zhí)行計(jì)劃（如v$sql_plan視圖）具體查看。

利用系統(tǒng)視圖v$sqlarea，其中disk_reads是磁盤讀次數(shù)，也是主要字段，剩余字段均為參考字段。其中，buffer_gets是內(nèi)存讀次數(shù)，parsing_schema_name是首次編譯者模式名（一般與user名相同），executions是語(yǔ)句執(zhí)行次數(shù)。

需要注意的是，v$sqlarea中sql_text可能不完整，若需要完整的則需要借助hash_value或sql_id結(jié)合v$sqltext來(lái)查看分析。

2）查看執(zhí)行次數(shù)多的SQL

select sql_text, executions, parsing_schema_name

from v$sqlarea

order by executions desc;

3）查看排序多的SQL

select sql_text, sorts, parsing_schema_name

from v$sqlarea

order by sorts desc;

該處還應(yīng)涉及Library Cache命中率、內(nèi)存命中率等內(nèi)容，暫不總結(jié)，見轉(zhuǎn)載內(nèi)容“Oracle調(diào)優(yōu)相關(guān)的各種命中率、使用率匯總”。

相關(guān)視圖重要字段

v$sqlarea

v$sql和v$sqlarea基本類似，而v$sqlarea更常用，故僅對(duì)v$sqlarea常用字段進(jìn)行說(shuō)明，如下（個(gè)人參考Oracle官方文檔翻譯的，因是最新版本，所以會(huì)跟網(wǎng)絡(luò)上的有些出入）：

• SQL_TEXT：SQL語(yǔ)句的前1000個(gè)字符；
• SQL_FULLTEXT：SQL語(yǔ)句的所有字符；
• SQL_ID：緩存在高速緩沖區(qū)（library cache）中的SQL父游標(biāo)的唯一標(biāo)識(shí)ID（注，類似于hash_value，不過(guò)hash_value是4bytes而sql_id是8bytes，sql_id更精確后期可能會(huì)替代hash_value）；
• SHARABLE_MEM：SQL語(yǔ)句及其子游標(biāo)占用的共享內(nèi)存大?。?/strong>
• PERSISTENT_MEM：打開SQL語(yǔ)句的生命周期內(nèi)所占用的固定內(nèi)存大?。ò佑螛?biāo)）；
• RUNTIME_MEM：游標(biāo)執(zhí)行期間所占用的固定內(nèi)存大??；
• SORTS：語(yǔ)句執(zhí)行導(dǎo)致的排序次數(shù)；
• VERSION_COUNT：在緩存中以該語(yǔ)句為父語(yǔ)句的子游標(biāo)總數(shù)；
• LOADED_VERSIONS：緩存中載入了這條語(yǔ)句上下文堆（KGL heap 6）的子游標(biāo)數(shù)；
• OPEN_VERSIONS：父游標(biāo)下打開的子游標(biāo)個(gè)數(shù)；
• USERS_OPENING：打開子游標(biāo)的用戶個(gè)數(shù)；
• FETCHES：SQL語(yǔ)句的fetch數(shù)；
• EXECUTIONS：包含所有子游標(biāo)在內(nèi)該SQL語(yǔ)句共執(zhí)行次數(shù)；
• USERS_EXECUTING：執(zhí)行過(guò)該語(yǔ)句所有子游標(biāo)的用戶總數(shù)；
• LOADS：語(yǔ)句被載入的總次數(shù)；
• FIRST_LOAD_TIME：父游標(biāo)被首次載入（編譯）的時(shí)間；
• PARSE_CALLS：父游標(biāo)下所有子游標(biāo)解析調(diào)用次數(shù)；
• DISK_READS：該語(yǔ)句通過(guò)所有子游標(biāo)導(dǎo)致的讀磁盤次數(shù)；
• DIRECT_WRITES：該語(yǔ)句通過(guò)所有子游標(biāo)導(dǎo)致的直接寫入次數(shù)；
• BUFFER_GETS：該語(yǔ)句通過(guò)所有子游標(biāo)導(dǎo)致的讀緩存次數(shù)；
• APPLICATION_WAIT_TIME：應(yīng)用等待時(shí)間；
• USER_IO_WAIT_TIME：用戶I/O等待時(shí)間；
• PLSQL_EXEC_TIME：PLSQL執(zhí)行時(shí)間；
• ROWS_PROCESSED：該SQL語(yǔ)句處理的總行數(shù)；
• OPTIMIZER_COST：此查詢優(yōu)化給出的成本數(shù)；
• PARSING_USER_ID：第一次解析該父語(yǔ)句的用戶ID；
• PARSING_SCHEMA_ID：第一次解析該語(yǔ)句SCHEMA的ID；
• PARSING_SCHEMA_NAME：解析該語(yǔ)句的SCHEMA的NAME；
• KEPT_VERSIONS：指出是否當(dāng)前子游標(biāo)被使用DBMS_SHARED_POOL包標(biāo)記為常駐內(nèi)存；
• ADDRESS：當(dāng)前游標(biāo)父句柄（唯一指向該游標(biāo)的一種地址編號(hào)）；
• HASH_VALUE：該語(yǔ)句在library cache中hash值；
• PLAN_HASH_VALUE：執(zhí)行計(jì)劃的hash值，可依此確定兩個(gè)執(zhí)行計(jì)劃是否相同（取代每行每字符進(jìn)行比較的方式）；
• CPU_TIME：該語(yǔ)句解析、執(zhí)行和fetch（取值）所消耗的CPU時(shí)間；
• ELAPSED_TIME：該語(yǔ)句解析、執(zhí)行和fetch（取值）所經(jīng)過(guò)的時(shí)間；
• LAST_ACTIVE_TIME：查詢計(jì)劃最后一次執(zhí)行的時(shí)間；
• LOCKED_TOTAL：所有子游標(biāo)被鎖的次數(shù)；'