Oracle 死結的檢測查詢及處理

標籤：

 

-- 死結查詢語句

SELECT    bs.username "Blocking User", bs.username "DB User",

          ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",

          bs.serial# "Serial#", bs.sql_address "address",

          bs.sql_hash_value "Sql hash", bs.program "Blocking App",

          ws.program "Waiting App", bs.machine "Blocking Machine",

          ws.machine "Waiting Machine", bs.osuser "Blocking OS User",

          ws.osuser "Waiting OS User", bs.serial# "Serial#",

          ws.serial# "WSerial#",

          DECODE (wk.TYPE,

                  ‘MR‘, ‘Media Recovery‘,

                  ‘RT‘, ‘Redo Thread‘,

                  ‘UN‘, ‘USER Name‘,

                  ‘TX‘, ‘Transaction‘,

                  ‘TM‘, ‘DML‘,

                  ‘UL‘, ‘PL/SQL USER LOCK‘,

                  ‘DX‘, ‘Distributed Xaction‘,

                  ‘CF‘, ‘Control FILE‘,

                  ‘IS‘, ‘Instance State‘,

                  ‘FS‘, ‘FILE SET‘,

                  ‘IR‘, ‘Instance Recovery‘,

                  ‘ST‘, ‘Disk SPACE Transaction‘,

                  ‘TS‘, ‘Temp Segment‘,

                  ‘IV‘, ‘Library Cache Invalidation‘,

                  ‘LS‘, ‘LOG START OR Switch‘,

                  ‘RW‘, ‘ROW Wait‘,

                  ‘SQ‘, ‘Sequence Number‘,

                  ‘TE‘, ‘Extend TABLE‘,

                  ‘TT‘, ‘Temp TABLE‘,

                  wk.TYPE

                 ) lock_type,

          DECODE (hk.lmode,

                  0, ‘None‘,

                  1, ‘NULL‘,

                  2, ‘ROW-S (SS)‘,

                  3, ‘ROW-X (SX)‘,

                  4, ‘SHARE‘,

                  5, ‘S/ROW-X (SSX)‘,

                  6, ‘EXCLUSIVE‘,

                  TO_CHAR (hk.lmode)

                 ) mode_held,

          DECODE (wk.request,

                  0, ‘None‘,

                  1, ‘NULL‘,

                  2, ‘ROW-S (SS)‘,

                  3, ‘ROW-X (SX)‘,

                  4, ‘SHARE‘,

                  5, ‘S/ROW-X (SSX)‘,

                  6, ‘EXCLUSIVE‘,

                  TO_CHAR (wk.request)

                 ) mode_requested,

          TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,

          DECODE

             (hk.BLOCK,

              0, ‘NOT Blocking‘,          /**//* Not blocking any other processes */

              1, ‘Blocking‘,              /**//* This lock blocks other processes */

              2, ‘Global‘,           /**//* This lock is global, so we can‘t tell */

              TO_CHAR (hk.BLOCK)

             ) blocking_others

     FROM v$lock hk, v$session bs, v$lock wk, v$session ws

    WHERE hk.BLOCK = 1

      AND hk.lmode != 0

      AND hk.lmode != 1

      AND wk.request != 0

      AND wk.TYPE(+) = hk.TYPE

      AND wk.id1(+) = hk.id1

      AND wk.id2(+) = hk.id2

      AND hk.SID = bs.SID(+)

      AND wk.SID = ws.SID(+)

      AND (bs.username IS NOT NULL)

      AND (bs.username <> ‘SYSTEM‘)

      AND (bs.username <> ‘SYS‘)

ORDER BY 1;

查詢發生死結的select語句

select sql_text from v$sql where hash_value in (

    select sql_hash_value from v$session where sid in (select session_id from v$locked_object)

)

 

關於資料庫死結的檢查方法

 

一、資料庫死結的現象
程式在執行的過程中，點擊確定或儲存按鈕，程式沒有響應，也沒有出現報錯。

二、死結的原理
當對於資料庫某個表的某一列做更新或刪除等操作，執行完畢後該條語句不提
交，另一條對於這一列資料做更新操作的語句在執行的時候就會處於等待狀態，
此時的現象是這條語句一直在執行，但一直沒有執行成功，也沒有報錯。

三、死結的定位方法
通過檢查資料庫表，能夠檢查出是哪一條語句被死結，產生死結的機器是哪一台。

 

1）用dba使用者執行以下語句

select username,lockwait,status,machine,program from v$session where sid 

in (select session_id from v$locked_object)

如果有輸出的結果，則說明有死結，且能看到死結的機器是哪一台。欄位說明：
Username：死結語句所用的資料庫使用者；
Lockwait：死結的狀態，如果有內容表示被死結。
Status： 狀態，active表示被死結
Machine： 死結語句所在的機器。
Program： 產生死結的語句主要來自哪個應用程式。

2）用dba使用者執行以下語句，可以查看到被死結的語句
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))

 

四、死結的解決方案
     一般情況下，只要將產生死結的語句提交就可以了，但是在實際的執行過程中。使用者可
能不知道產生死結的語句是哪一句。可以將程式關閉並重新啟動就可以了。
　經常在Oracle的使用過程中碰到這個問題，所以也總結了一點解決方案。

　　

 

     1）尋找死結的進程：

sqlplus "/as sysdba" (sys/change_on_install)

SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS 

FROM V$LOCKED_OBJECT l,V$SESSION S 

WHERE l.SESSION_ID=S.SID;

　　2）kill掉這個死結的進程：

　　alter system kill session ‘sid,serial#’; （其中sid=l.session_id）

 

　　3）如果還不能解決：

          select pro.spid from v$session ses, v$process pro where ses.sid=XX and ses.paddr=pro.addr;

   其中sid用死結的sid替換:

       exit
       ps -ef|grep spid

       其中spid是這個進程的進程號，kill掉這個Oracle進程。

轉自：http://sungang-82.iteye.com/blog/310781

Oracle 死結的檢測查詢及處理