怎樣快速查出Oracle 數(shù)據(jù)庫(kù)中的鎖等待

---- 在大型數(shù)據(jù)庫(kù)系統(tǒng)中，為了保證數(shù)據(jù)的一致性，在對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行操作時(shí)，系統(tǒng)會(huì)進(jìn)行對(duì)數(shù)據(jù)相應(yīng)的鎖定。

---- 這些鎖定中有"只讀鎖"、"排它鎖"，"共享排它鎖"等多種類(lèi)型，而且每種類(lèi)型又有"行級(jí)鎖"（一次鎖住一條記錄），"頁(yè)級(jí)鎖"（一次鎖住一頁(yè)，即數(shù)據(jù)庫(kù)中存儲(chǔ)記錄的最小可分配單元），"表級(jí)鎖"（鎖住整個(gè)表）。

---- 若為"行級(jí)排它鎖"，則除被鎖住的該行外，該表中其它行均可被其它的用戶(hù)進(jìn)行修改(Update)或刪除(delete)操作，若為"表級(jí)排它鎖"，則所有其它用戶(hù)只能對(duì)該表進(jìn)行查詢(xún)(select)操作，而無(wú)法對(duì)其中的任何記錄進(jìn)行修改或刪除。當(dāng)程序?qū)λ龅男薷倪M(jìn)行提交(commit)或回滾后(rollback)后，鎖住的資源便會(huì)得到釋放，從而允許其它用戶(hù)進(jìn)行操作。

---- 但是，有時(shí)，由于程序中的原因，鎖住資源后長(zhǎng)時(shí)間未對(duì)其工作進(jìn)行提交；或是由于用戶(hù)的原因，如調(diào)出需要修改的數(shù)據(jù)后，未及時(shí)修改并提交，而是放置于一旁；或是由于客戶(hù)服務(wù)器方式中客戶(hù)端出現(xiàn)"死機(jī)"，而服務(wù)器端卻并未檢測(cè)到，從而造成鎖定的資源未被及時(shí)釋放，影響到其它用戶(hù)的操作。

---- 因而，如何迅速地診斷出鎖住資源的用戶(hù)以及解決其鎖定便是數(shù)據(jù)庫(kù)管理員的一個(gè)挑戰(zhàn)。

---- 由于數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)越來(lái)越復(fù)雜， 一旦出現(xiàn)由于鎖資源未及時(shí)釋放的情況，便會(huì)引起對(duì)一相同表進(jìn)行操作的大量用戶(hù)無(wú)法進(jìn)行操作，從而影響到系統(tǒng)的使用。此時(shí)，DBA應(yīng)盡量快地解決問(wèn)題。但是，由于在Oracle 8.0.x 中執(zhí)行"獲取正在等待鎖資源的用戶(hù)名"的查詢(xún)語(yǔ)句

select a.username, a.sid, a.serial#, b.id1
from v$session a, v$lock b
where a.lockwait = b.kaddr

---- 十分緩慢，（在 Oracle 7.3.4中執(zhí)行很快)，而且，執(zhí)行"查找阻塞其它用戶(hù)的用戶(hù)進(jìn)程"的查詢(xún)語(yǔ)句
select a.username, a.sid, a.serial#, b.id1
from v$session a, v$lock b
where b.id1 in
(select distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and b.request = 0

---- 執(zhí)行得也十分緩慢。因而，往往只好通過(guò)將 v$session 中狀態(tài)為"inactive"（不活動(dòng)）并且最后一次進(jìn)行操作時(shí)間至當(dāng)前已超過(guò) 20 分鐘以上(last_call_et>20*60 秒)的用戶(hù)進(jìn)程清除，然后才使得問(wèn)題得到解決。
---- 但是，這種方法實(shí)際上是"把嬰兒與臟水一起潑掉"。因?yàn)椋行┯脩?hù)的進(jìn)程盡管也為"inactive"，并且也已有較長(zhǎng)時(shí)間未活動(dòng)，但是，那是由于他們處于鎖等待狀態(tài)。

---- 因而，我想出了一個(gè)解決辦法。即通過(guò)將問(wèn)題發(fā)生時(shí)的 v$lock，v$session視圖中的相關(guān)記錄保存于自己建立的表中，再對(duì)該表進(jìn)行查詢(xún)，則速度大大提高，可以迅速發(fā)現(xiàn)問(wèn)題。經(jīng)實(shí)際使用，效果非常好。在接到用戶(hù)反映后，幾秒鐘即可查出由于鎖住資源而影響其它用戶(hù)的進(jìn)程，并進(jìn)行相應(yīng)的處理。

---- 首先，以 dba 身份（不一定為system）登錄入數(shù)據(jù)庫(kù)中，創(chuàng)建三個(gè)基本表:my_session，my_lock, my_sqltext，并在將會(huì)進(jìn)行查詢(xún)的列上建立相應(yīng)的索引。語(yǔ)句如下： rem 從 v$session 視圖中取出關(guān)心的字段，創(chuàng)建 my_session 表,并在查詢(xún)要用到的字段上創(chuàng)建索引，以加快查詢(xún)速度

drop table my_session;
create table my_session
as
select a.username, a.sid, a.serial#,
a.lockwait, a.machine,a.status,
a.last_call_et,a.sql_hash_value,a.program
from v$session a
where 1=2 ;

create unique index my_session_u1 on my_session(sid);
create index my_session_n2 on my_session(lockwait);
create index my_session_n3 on my_session(sql_hash_value);

---- rem 從 v$lock 視圖中取出字段，創(chuàng)建 my_lock 表，并在查詢(xún)要用到的字段上創(chuàng)建索引，以加快查詢(xún)速度
drop table my_lock;
create table my_lock
as
select id1, kaddr, sid, request,type
from v$lock
where 1=2;

create index my_lock_n1 on my_lock(sid);
create index my_lock_n2 on my_lock(kaddr);

---- rem 從 v$sqltext 視圖中取出字段，創(chuàng)建 my_sqltext 表，并在查詢(xún)要用到的字段上創(chuàng)建索引，以加快查詢(xún)速度
drop table my_sqltext;
create table my_sqltext
as
select hash_value , sql_text
from v$sqltext
where 1=2;

create index my_sqltext_n1 on my_sqltext ( hash_value);

---- 然后，創(chuàng)建一個(gè) SQL 腳本文件，以便需要時(shí)可從 SQL*Plus 中直接調(diào)用。其中，首先用 truncate table 表名命令將表中的記錄刪除。之所以用 truncate 命令，而不是用delete 命令，是因?yàn)閐elete 命令執(zhí)行時(shí)，將會(huì)產(chǎn)生重演記錄，速度較慢，而且索引所占的空間并未真正釋放，若反復(fù)做 insert及delete,則索引所占的空間會(huì)不斷增長(zhǎng)，查詢(xún)速度也會(huì)變慢。而 truncate命令不產(chǎn)生重演記錄，速度執(zhí)行較delete快，而且索引空間被相應(yīng)地釋放出來(lái)。刪除記錄后，再將三個(gè)視圖中的相關(guān)記錄插入自己創(chuàng)建的三個(gè)表中。最后，對(duì)其進(jìn)行查詢(xún)，由于有索引，同時(shí)由于在插入時(shí)條件過(guò)濾后，記錄數(shù)相對(duì)來(lái)說(shuō)較少，因而查詢(xún)速度很快，馬上可以看到其結(jié)果。
---- 此時(shí)，若發(fā)現(xiàn)該阻塞其它用戶(hù)進(jìn)程的進(jìn)程是正常操作中，則可通知該用戶(hù)對(duì)其進(jìn)行提交，從而達(dá)到釋放鎖資源的目的；若為未正常操作，即，其狀態(tài)為"inactive"，且其last_call_et已為較多長(zhǎng)時(shí)間，則可執(zhí)行以下語(yǔ)句將該進(jìn)程進(jìn)行清除，系統(tǒng)會(huì)自動(dòng)對(duì)其進(jìn)行回滾，從而釋放鎖住的資源。

alter system kill session 'sid, serial#';
---- SQL 腳本如下：
set echo off
set feedback off
prompt '刪除舊記錄.....'
truncate table my_session;
truncate table my_lock;
truncate table my_sqltext;

prompt '獲取數(shù)據(jù).....'
insert into my_session
select a.username, a.sid, a.serial#,
a.lockwait, a.machine,a.status,
a.last_call_et,a.sql_hash_value,a.program
from v$session a
where nvl(a.username,'NULL')< >'NULL;

insert into my_lock
select id1, kaddr, sid, request,type
from v$lock;

insert into my_sqltext
select hash_value , sql_text
from v$sqltext s, my_session m
where s.hash_value=m.sql_hash_value;

column username format a10
column machine format a15
column last_call_et format 99999 heading "Seconds"
column sid format 9999

prompt "正在等待別人的用戶(hù)"
select a.sid, a.serial#,
a.machine,a.last_call_et, a.username, b.id1
from my_session a, my_lock b
where a.lockwait = b.kaddr;

prompt "被等待的用戶(hù)"
select a.sid, a.serial#,
a. machine, a.last_call_et,a.username,
b. b.type,a.status,b.id1
from my_session a, my_lock b
where b.id1 in
(select distinct e.id1
from my_session d, my_lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and b.request=0;

prompt "查出其 sql "
select a.username, a.sid, a.serial#,
b.id1, b.type, c.sql_text
from my_session a, my_lock b, my_sqltext c
where b.id1 in
(select distinct e.id1
from my_session d, my_lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and b.request=0
and c.hash_value =a.sql_hash_value;

---- 以上思路也可用于其它大型數(shù)據(jù)庫(kù)系統(tǒng)如 Informix, Sybase,DB2中。通過(guò)使用該腳本，可以極大地提高獲取系統(tǒng)中當(dāng)前鎖等待的情況，從而及時(shí)解決數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)中的鎖等待問(wèn)題。而且，由于實(shí)際上已取出其 program 名及相應(yīng)的 sql 語(yǔ)句，故可以在事后將其記錄下來(lái)，交給其開(kāi)發(fā)人員進(jìn)行分析并從根本上得到解決。