oracle sql資源消耗相關視圖

標籤：oracle

一、常用視圖說明

Oracle sql語句資源消耗監控，最常用的系統檢視表有：

v$sql

v$sqlarea

v$sqltext

v$session

v$sql和v$sqlarea基本相同，記錄了共用SQL區（share pool）中SQL統計資訊，如記憶體消耗、IO（物理磁碟讀和邏輯記憶體讀）、排序操作、雜湊ID等資料。不同之處在於v$sql為每一條SQL保留一個條目，而v$sqlarea中根據sql_text（需要注意，該處儲存的為當前SQL指標的前1000個字元，也就是說這裡記錄的SQL可能是不完整的！）進行group by，統計列進行sum()，通過version_count計運算元指標的個數。

  然而，文本（sql_text）相同的SQL語句在資料庫中意義可能完全不同。比如資料庫中存在兩個使用者User1和User2，這兩個使用者各擁有一張資料表EMP。那麼當兩個使用者發出查詢select count(*) from emp;時各自訪問自己SCHEMA中的表EMP，而兩者表內容不同所以其資源消耗肯定也不同。此時，在v$sql中會有這兩條完全一樣的SQL各自的統計資訊，而在v$sqlarea中sql_text相同的2個指標會合并起來，執行次數、DISK_READS、BUFFER_GETS等統計資訊都會累加（sum），version_count會顯示為2，這就是v$sqlarea的彙總作用。

 

v$sqltext中沒有統計資訊，然而卻儲存著完整的SQL語句及其雜湊ID等。

對於這三者，我們可以使用視圖v$fixed_view_definition來查看視圖的源表，如下：

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，其他兩個也如此。

通過以上3條語句可以發現，V$SQL資料來源X$KGLCURSOR_CHILD，其實資料還是來源於X$KGLOB；而V$SQLAREA資料來源X$KGLCURSOR_CHILD_SQLID本質是對X$KGLCURSOR_CHILD按照sql_id等欄位分組匯總後的結果；V$SQLTEXT資料來源X$KGLNA。

v$session主要用來確定會話相關資訊，如通過SID和SERIAL#來唯一確定一個session（SID可能會重複）、會話擁有者使用者名稱USERNAME、工作階段狀態（active:正在執行SQL語句、inactive:等待操作、killed:被殺死）、會話由哪個用戶端發起（MACHINE、TERMINAL）、正在執行什麼SQL（通過SQL_ADDRESS、SQL_HASH_VALUE、SQL_ID、SQL_CHILD_NUMBER確定，有這些再藉助v$sqltext就能知道）、甚至上一次執行的SQL是什麼（通過PREV_SQL_ADDRESS等確定）、鎖等待相關資訊（如所在表、檔案、塊、被鎖行）等。

 

高資源消耗SQL尋找定位

1）查看讀硬碟多或佔用記憶體可能多的SQL：

select sql_text, disk_reads, buffer_gets, parsing_schema_name, executions

from v$sqlarea

order by disk_reads desc;

說明：單純從V$sqlarea中是無法查出每個SQL消耗的記憶體量的，但我們可以藉助磁碟讀次數間接反映可能的消耗記憶體量較大的SQL語句，然後再藉助執行計畫（如v$sql_plan視圖）具體查看。

利用系統檢視表v$sqlarea，其中disk_reads是磁碟讀次數，也是主要欄位，剩餘欄位均為參考欄位。其中，buffer_gets是記憶體讀次數，parsing_schema_name是首次編譯者模式名（一般與user名相同），executions是語句執行次數。

需要注意的是，v$sqlarea中sql_text可能不完整，若需要完整的則需要藉助hash_value或sql_id結合v$sqltext來查看分析。

2）查看執行次數多的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;

該處還應涉及Library Cache命中率、記憶體命中率等內容，暫不總結。

4）查看總消耗時間最多的前10條SQL語句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.elapsed_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

5）查看CPU消耗時間最多的前10條SQL語句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.cpu_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

6）查看消耗磁碟讀取最多的前10條SQL語句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.disk_reads desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

註：如查執行上面的查尋提示 RA-00942: table or view does not exist

出現這個體會提示並不是說資料庫執行個體中沒有 v$sql 對象，而是你當前的使用者沒有許可權。解決辦法就是給目前使用者賦予 v$sqlarea 的查詢許可權

sql>grant select any dictionary to myuser;

650) this.width=650;" title="QQ20170808161914.jpg" src="https://s4.51cto.com/wyfs02/M01/9D/FB/wKioL1mJgPzyH_arAABn5WFYJxI467.jpg-wh_500x0-wm_3-wmp_4-s_216741007.jpg" alt="wKioL1mJgPzyH_arAABn5WFYJxI467.jpg-wh_50" />

相關視圖重要欄位

v$sqlarea

v$sqlareav$sql和v$sqlarea基本類似，而v$sqlarea更常用，故僅對v$sqlarea常用欄位進行說明，如下（個人蔘考Oracle官方文檔翻譯的，因是最新版本，所以會跟網路上的有些出入）：

SQL_TEXT：SQL語句的前1000個字元；

SQL_FULLTEXT：SQL語句的所有字元；

SQL_ID：緩衝在高速緩衝區（library cache）中的SQL父遊標的唯一標識ID（注，類似於hash_value，不過hash_value是4bytes而sql_id是8bytes，sql_id更精確後期可能會替代hash_value）；

SHARABLE_MEM：SQL語句及其子遊標佔用的共用記憶體大小；

PERSISTENT_MEM：開啟SQL語句的生命週期內所佔用的固定記憶體大小（包含子遊標）；

RUNTIME_MEM：遊標執行期間所佔用的固定記憶體大小；

SORTS：語句執行導致的排序次數；

VERSION_COUNT：在緩衝中以該語句為父語句的子遊標總數；

LOADED_VERSIONS：緩衝中載入了這條語句上下文堆（KGL heap 6）的子遊標數；

OPEN_VERSIONS：父遊標下開啟的子遊標個數；

USERS_OPENING：開啟子遊標的使用者個數；

FETCHES：SQL語句的fetch數；

EXECUTIONS：包含所有子遊標在內該SQL語句共執行次數；

USERS_EXECUTING：執行過該語句所有子遊標的使用者總數；

LOADS：語句被載入的總次數；

FIRST_LOAD_TIME：父遊標被首次載入（編譯）的時間；

PARSE_CALLS：父遊標下所有子遊標解析調用次數；

DISK_READS：該語句通過所有子遊標導致的讀磁碟次數；

DIRECT_WRITES：該語句通過所有子遊標導致的直接寫入次數；

BUFFER_GETS：該語句通過所有子遊標導致的讀緩衝次數；

APPLICATION_WAIT_TIME：應用等待時間；

USER_IO_WAIT_TIME：使用者I/O等待時間；

PLSQL_EXEC_TIME：PLSQL執行時間；

ROWS_PROCESSED：該SQL語句處理的總行數；

OPTIMIZER_COST：此查詢最佳化給出的成本數；

PARSING_USER_ID：第一次解析該父語句的使用者ID；

PARSING_SCHEMA_ID：第一次解析該語句SCHEMA的ID；

PARSING_SCHEMA_NAME：解析該語句的SCHEMA的NAME；

KEPT_VERSIONS：指出是否當前子遊標被使用DBMS_SHARED_POOL包標記為常駐記憶體；

ADDRESS：當前遊標父控制代碼（唯一指向該遊標的一種地址編號）；

HASH_VALUE：該語句在library cache中hash值；

PLAN_HASH_VALUE：執行計畫的hash值，可依此確定兩個執行計畫是否相同（取代每行每字元進行比較的方式）；

CPU_TIME：該語句解析、執行和fetch（取值）所消耗的CPU時間；

ELAPSED_TIME：該語句解析、執行和fetch（取值）所經過的時間；

LAST_ACTIVE_TIME：查詢計劃最後一次執行的時間；

LOCKED_TOTAL：所有子遊標被鎖的次數；

v$sqltext

ADDRESS：當前遊標父控制代碼（唯一指向該遊標的一種地址編號）；

HASH_VALUE：該遊標（子遊標）在library cache中唯一hash值；

SQL_ID：緩衝遊標中該SQL的一個唯一標識值；

COMMAND_TYPE：SQL語句類型，如select、insert、update等；

PIECE：排序SQL文本的片段數；

SQL_TEXT：包含一個完整SQL中的某一小塊SQL文本字元（要完整的SQL語句需要把這些片段組合起來）；

v$session

SADDR：session地址；

SID：session標識值，常跟serial#聯合唯一確定一個session（在殺進程時，有時SID會重用，造成誤殺。而serial會增加但不會重複，sid 在同一個instance的當前session中是一個unique key，而sid ,serial#則是在整個instance生命期內的所有session中是unique key）；

SERIAL#：會話序號，用於在一個會話結束而另一個會話重用這該會話的SID時，唯一確定一個會話；

AUDSID：審計會話ID，可以通過audsid查詢當前session的sid，select sid from v$session where audsid=userenv(‘sessionid‘)；

PADDR：進程地址，關聯v$process的addr欄位，通過這個可以查詢到進程對應的session；

USER#：同於dba_users中的user_id，Oracle內部進程user#為0；

USERNAME：會話擁有者使用者名稱，等於dba_users中的username，Oracle內部進程的username為空白；

COMMAND：正在執行的SQL語句類型，如1為create table、3為select等；

OWNERID：如果該列值為2147483644則值無效，否則值用於會話遷移、並行等；

TADDR：Address of transaction state object；

LOCKWAIT：標識當前查詢是否處於鎖等待狀態，為空白則表示無等待；

STATUS：標識session狀態，Active正執行SQL語句，inactive等待操作，killed被標註為殺死；

SERVER：伺服器類型，DEDICATED專用、SHARED共用等；

SCHEMA#：SCHEMA標識ID值，Oracle內部進程的schema#為0；

SCHEMANAME：SCHEMA使用者名稱，Oracle內部進程的為sys；

OSUSER：用戶端作業系統使用者名稱；

PROCESS：用戶端作業系統進程ID；

MACHINE：作業系統機器名；

TERMINAL：作業系統終端名；

PROGRAM：作業系統應用程式名稱，如EXE或sqlplus.exe；

TYPE：會話類型，如BACKGROUND或USER；

SQL_ADDRESS：和SQL_HASH_VALUE一起使用標識正在執行的SQL語句；

SQL_HASH_VALUE：和SQL_ADDRESS一起使用標識正在執行的SQL語句；

SQL_ID：正在執行的SQL語句的標識ID；

SQL_CHILD_NUMBER：正在執行的SQL語句的子ID；

FIXED_TABLE_SEQUENCE：當session完成一個user call後就會增加的一個數值，也就是說，如果session掛起，它就不會增加。因此可以根據這個欄位來監控某個時間點以來的session效能情況。例如，一個小時前某個session的此欄位數值為10000，而現在是20000，則表明一個小時內其user call較頻繁，可以重點關注此session的performance statistics。

ROW_WAIT_OBJ#：被鎖定行所在table的object_id，和dba_object中的object_id關聯可以得到被鎖定的table name；

ROW_WAIT_FILE#：被鎖定行所在的datafile id，和v$datafile中的file#關聯可以得到datafile name；

ROW_WAIT_BLOCK#：被鎖定的塊ID；

ROW_WAIT_ROW#：被鎖定的當前行；

LOGON_TIME：登入時間；

 

本文出自 “59090939” 部落格，請務必保留此出處http://59090939.blog.51cto.com/6338052/1954557

oracle sql資源消耗相關視圖