Oracle高資源消耗SQL語句定位
Oracle高資源消耗SQL語句定位
Oracle SQL語句資源消耗監控最常用的系統檢視有v$sql、v$sqlarea、v$sqltext和v$session。本文我們先了解這些檢視的作用與區別,然後瞭解如何定位高資源消耗SQL語句,最後再瞭解一下各檢視欄位具體含義。
相關係統檢視功能與區別
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命中率、記憶體命中率等內容,暫不總結,見轉載內容“Oracle調優相關的各種命中率、使用率彙總”。
相關檢視重要欄位
v$sqlarea
v$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:登入時間;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31397003/viewspace-2142838/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle 中定位重要(消耗資源多)的SQLOracleSQL
- 如何快速定位當前資料庫消耗 CPU 最高的 sql 語句?資料庫SQL
- MySQL 5.7定位消耗CPU高的SQLMySql
- Sqlserver定位哪些物件和哪些會話哪些sql語句消耗了tempdbSQLServer物件會話
- 20140321]檢視大量消耗資源的sql語句.txtSQL
- oracle中找出最消耗資源的sqlOracleSQL
- oracle 高耗cpu sql語句的捕捉 。OracleSQL
- sql_util_消耗資源SQL
- oracle v$sqlare 分析SQL語句使用資源情況OracleSQL
- oracle sql語句OracleSQL
- 乾貨分享|快速定位UXDB中CPU高負荷的SQL語句UXSQL
- Oracle 查耗CPU資源sql語句及程式代號-轉OracleSQL
- 快速定位sql語句執行內容SQL
- 找出消耗CPU最高的程式對應的SQL語句SQL
- Oracle - 執行過的SQL、正在執行的SQL、消耗資源最多的SQLOracleSQL
- Oracle基本SQL語句OracleSQL
- oracle常用SQL語句OracleSQL
- oracle的sql語句OracleSQL
- Oracle 資料庫監控SQL語句Oracle資料庫SQL
- Oracle表部分資料提取SQL語句OracleSQL
- oracle資料庫巡檢最佳化-使用sql語句快速定位資料庫瓶頸Oracle資料庫SQL
- 幾個定位、查詢session的sql語句SessionSQL
- SQL查詢語句 (Oracle)SQLOracle
- Oracle SQL語句分類OracleSQL
- Oracle SQL精妙SQL語句講解OracleSQL
- Oracle sql with 語句語法與例子OracleSQL
- SQL語句資料SQL
- Sql語句本身的優化-定位慢查詢SQL優化
- 生產SQL語句突然變慢問題定位SQL
- MySql定位執行效率較低的SQL語句MySql
- MySQL使用profile分析語句效能消耗MySql
- Oracle SQL精妙SQL語句講解(轉)OracleSQL
- Oracle之sql語句優化OracleSQL優化
- Oracle維護常用SQL語句OracleSQL
- ORACLE很重要的sql語句OracleSQL
- 高效率Oracle SQL語句OracleSQL
- 【SQL】Oracle程式設計藝術指令碼學習之runsat(語句執行消耗對比)SQLOracle程式設計指令碼
- oracle 檢索最近10分鐘裡消耗IO最嚴重的前5條SQL語句OracleSQL