Oracle 死鎖與慢查詢總結

查看死鎖

SELECT 
s.sid "會話ID",
s.lockwait "等待鎖",
s.event "等待的資源/事件",  -- 最近等待或正在等待的資源/事件
DECODE(lo.locked_mode, 0, '尚未獲得鎖', 1, NULL, 2, '行共用鎖', 3, '行排它鎖', 4, '共用表鎖',
5,'共用行排它鎖',6, '排它表鎖') "鎖模式",
do.object_name "被鎖對象",
s.status "會話狀態",
sq.SQL_TEXT,
sq.SQL_FULLTEXT, 
sq.executions "SQL執行次數",
ROUND(sq.elapsed_time/1000000, 2) "SQL執行時間(秒)",
DECODE(sq.executions,0,'-',NULL,'-',ROUND(sq.elapsed_time/1000000/sq.executions, 2)) "SQL平均執行時間(秒)", 
DECODE(sq.executions,0,'-',NULL,'-',ROUND(sq.rows_processed/sq.executions, 2)) "平均返回行數", 
s.sql_exec_start "SQL開始執行時間",
sq.last_active_time "查詢計劃最後活躍時間",
lo.process "操作系統進程ID",
s.port "進程埠號",
s.program "進程名稱", 
lo.os_user_name "操作系統用戶名",
s.machine "操作系統機器名稱",
'ALTER SYSTEM KILL SESSSION '''||s.sid||','||s.serial#||''';' "終止會話操作"
FROM v$sql sq 
JOIN v$session s on s.sql_hash_value = sq.hash_value
JOIN v$locked_object lo on lo.session_id = s.sid
JOIN dba_objects do on do.object_id = lo.object_id
WHERE s.username='OPT_WMS_USER' ; -- Oracle用戶名稱，大寫

說明：

• 如果lockwait值不為空(形如0000001F83D6C748)，並且status為ACTIVE，則說明存在死鎖

• event 最近等待或正在等待的資源/事件：

  • enq: TX - row lock contention ：按模式6等待TX：當會話等待另一個會話已持有的行級鎖時發生該事件，即某個用戶正在更新、刪除另一個會話希望更新、刪除的行時，會發生這種情況。這種類型的TX排隊等待對應於等待事件 enq:TX - row lock contention。

    解決方案：已經持有鎖的第一個會話執行提交或回

查看慢查詢

查詢執行最慢的SQL

SELECT * FROM (
  SELECT s.sql_text,
  --s.sql_fulltext, 註釋掉該列，可以加快查詢速度（如果需要查詢完整sql文本，可以考慮通過sql_id二次查詢）
  s.sql_id,
  s.executions "執行次數",
  ROUND(s.elapsed_time / 1000000, 2) "總執行時間(秒)",
  ROUND(s.elapsed_time / 1000000 / s.executions, 2) "平均執行時間", --單位：秒
  s.first_load_time "父游標創建時間",
  s.parsing_user_id "用戶id",
  u.username "用戶名"
  FROM v$sqlarea s
  LEFT JOIN all_users u ON s.parsing_user_id = u.user_id
  WHERE s.executions > 0
  AND u.username = 'OPT_WMS_USER' --註意 用戶名大寫
  ORDER BY 平均執行時間 DESC)
WHERE rownum <= 50

說明：為什麼不從v$sql統計信息？這是因為即便相同的SQL，每次執行耗時也可能不一樣，所以，考慮求平均值，所以需要對SQL分組統計，SQL_TEXT相同，大概率為同一條SQL，所以考慮從按SQL_TEXT分組統計的v$sqlarea讀取信息。當然，出於嚴謹的考慮，也可以不分組統計，把v$sqlarea替換成v$sql就好了。

查詢SQL執行次數，按次數降序排序

SELECT * FROM (
  SELECT s.sql_text,
    --s.sql_fulltext,
    s.sql_id,
    s.executions "執行次數",
    s.last_active_time "最後執行時間",
    s.first_load_time "父游標創建時間",
    s.parsing_user_id "執行用戶id",
    u.username "執行用戶", 
  RANK() OVER(ORDER BY executions DESC) executions_rank
  FROM v$sql s
  LEFT JOIN all_users u
  ON u.user_id = s.parsing_user_id) T
WHERE executions_rank <= 100;

註意：之所以從v$sql獲取統計數據，是因為這裡未對SQL_TEXT做GROUP BY（SQL_TEXT是完整SQL文本前1000個字元，存在截斷的可能，按在這個統計可能不准確），就針對每條SQL(不管是否相同)單獨統計，當然，也可以考慮按SQL_TEXT分組統計，把v$sql改成v$sqlarea就好了。

查看存在TABLE ACCESS FULL行為的SQL

SELECT s.sql_text, s.sql_fulltext, sp.sql_id 
FROM v$sql_plan sp
LEFT JOIN v$sql s on sp.sql_id = s.sql_id
WHERE sp.operation = 'TABLE ACCESS'
AND sp.options = 'FULL'
AND sp.object_owner = 'OPT_WMS_USER_B' --註意 用戶名大寫 --可選查詢條件

V$SQL

V$SQL列出了關於共用SQL區，不含GROUP BY子句的統計，輸入的原始SQL文本的每個子項各占一行。通常在查詢執行結束時更新V$SQL中展示的統計信息，然而針對長耗時查詢，每5秒更新一次。這樣更容易在運行期間查看長時間運行的SQL語句帶來的影響

V$SQLAREA顯示共用SQL區域的統計信息，每條SQL字元串為一行。它提供記憶體中、已解析並準備執行的SQL語句的統計信息。V$SQLAREA和V$SQL兩個視圖的不同之處在於，V$SQL中為每一條SQL保留一個條目，而V$SQLAREA中根據SQL_TEXT進行GROUP BY，通過VERSION_COUNT計運算元指針的個數

V$SESSION

V$SESSION 顯示當前會話的會話信息，常見視圖欄位及欄位描述說明如下：

V$LOCKED_OBJECT

V$LOCKED_OBECT列出了系統上每個事務獲取的所有鎖。它顯示了哪些會話在什麼對象上以及在什麼模式下持有DML鎖（即TM類型的隊列）。視圖常見欄位及描述如下：

SELECT object_id "被鎖住的對象ID",
locked_mode "鎖模式",
session_id "會話ID",
oracle_username "Oracle用戶名",
os_user_name "操作系統用戶名",
process "操作系統進程ID"
FROM V$LOCKED_OBJECT;

參考連接

https://docs.oracle.com/database/121/REFRN/GUID-2B9340D7-4AA8-4894-94C0-D5990F67BE75.htm#REFRN30246

https://docs.oracle.com/database/121/REFRN/GUID-09D5169F-EE9E-4297-8E01-8D191D87BDF7.htm#REFRN30259

https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/V-SESSION.html

https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/V-LOCKED_OBJECT.html#GUID-3F9F26AA-197F-4D36-939E-FAF1EFD8C0DD

作者：授客
微信/QQ：1033553122
全國軟體測試QQ交流群：7156436
Git地址：https://gitee.com/ishouke
友情提示：限於時間倉促，文中可能存在錯誤，歡迎指正、評論！
作者五行缺錢，如果覺得文章對您有幫助，請掃描下邊的二維碼打賞作者，金額隨意，您的支持將是我繼續創作的源動力，打賞後如有任何疑問，請聯繫我!!!
           微信打賞                        支付寶打賞                  全國軟體測試交流QQ群