|
事務(wù)的鎖定和阻塞機(jī)制
1,，oracle鎖的分類與產(chǎn)生 Oracle是一個(gè)多用戶使用的共享資源,。加鎖是實(shí)現(xiàn)數(shù)據(jù)庫(kù)并發(fā)控制的一個(gè)非常重要的技術(shù)。當(dāng)事務(wù)在對(duì)某個(gè)數(shù)據(jù)對(duì)象進(jìn)行操作前,，先向系統(tǒng)發(fā)出請(qǐng)求,，對(duì)其加鎖。加鎖后事務(wù)就對(duì)該數(shù)據(jù)對(duì)象有了一定的控制,，在該事務(wù)釋放鎖之前,，其他的事務(wù)不能對(duì)數(shù)據(jù)對(duì)象進(jìn)行更行操作。 在數(shù)據(jù)庫(kù)中有兩種基本類型的鎖：排他鎖（exclusive locks即X鎖）和共享鎖（share locks即S鎖）。
1.1 oracle的鎖類型
根據(jù)保護(hù)的對(duì)象不同,，oracle數(shù)據(jù)庫(kù)鎖可以分為以下幾個(gè)大類：DML鎖（data Locks數(shù)據(jù)鎖）,，用于保護(hù)數(shù)據(jù)的完整性； DDL鎖（dictionary locks,，字典鎖）,，用于保護(hù)數(shù)據(jù)庫(kù)對(duì)象的結(jié)構(gòu)，如表,、索引等的結(jié)構(gòu)定義,；內(nèi)部鎖和閂鎖（internal locks and latches），保護(hù)數(shù)據(jù)庫(kù)的內(nèi)部結(jié)構(gòu),，比如library cache,，shared pool等,。 DML鎖主要包括TM鎖和TX鎖,，其中TM鎖稱為表級(jí)別鎖，TX鎖稱為事務(wù)鎖或者行級(jí)別鎖,。
A table-level lock(TM)is set for any dml transaction that modifies a table: insert, update, delete, select for update or lock table , the TM prevents DDL operations that would conflict with the transaction.
就是說(shuō)TM是用來(lái)防止和事務(wù)沖突的DDL操作,，比如在insert時(shí)防止表被drop。
The row-level lock(TX) is automatically acquired for each row modified by insert, update, delete or select for update . the row-level lock ensures that no other user can modify the same row at the same time . therefore , there is no risk that a user can modify a row that is being modified and not yet committed by another user.
TX鎖是為了保護(hù)數(shù)據(jù)的一致性,，就是說(shuō)不能在同一時(shí)間修改相同的行,。
當(dāng)oracle執(zhí)行DML語(yǔ)句時(shí)，系統(tǒng)自動(dòng)在所要操作的表上申請(qǐng)TM類型的鎖,，當(dāng)TM鎖獲得后,，系統(tǒng)再申請(qǐng)TX類型的鎖。這樣在事務(wù)加鎖前檢查T(mén)X鎖相容性時(shí)就不用再逐行檢查鎖標(biāo)志,，而只需要檢查T(mén)M鎖模式的兼容性即可,，大大提高了系統(tǒng)的效率。
在數(shù)據(jù)行上只有TX鎖,，oracle執(zhí)行select語(yǔ)句時(shí)不對(duì)行對(duì)象進(jìn)行任何鎖定,，當(dāng)oracle數(shù)據(jù)庫(kù)發(fā)生TX鎖等待時(shí)，如果不及時(shí)處理會(huì)引起oracle數(shù)據(jù)庫(kù)掛起,，或者導(dǎo)致鎖的發(fā)生,，產(chǎn)生ora-600錯(cuò)誤。這些現(xiàn)象都會(huì)對(duì)實(shí)際應(yīng)用產(chǎn)生極大的危害,，如長(zhǎng)時(shí)間未響應(yīng),，大量事務(wù)失敗等。
1.2 悲觀封鎖和樂(lè)觀封鎖
悲觀封鎖 鎖在用戶修改之前就發(fā)揮作用（select * from table1 for update）,。用戶發(fā)出上述語(yǔ)句后,，oracle將會(huì)對(duì)返回的結(jié)果集建立行級(jí)別TX鎖，以防止其他用戶對(duì)相同記錄的修改，同時(shí)會(huì)在表上建立一個(gè)編號(hào)為3的TM鎖,。
樂(lè)觀封鎖
樂(lè)觀的封鎖認(rèn)為數(shù)據(jù)在select出來(lái)到update進(jìn)行到提交這段時(shí)間數(shù)據(jù)不會(huì)被更改,，這里面有一種潛在的威脅就是由于被選出來(lái)的結(jié)果集并沒(méi)有被鎖定，所以可能被其他用戶修改了,。比如我們系統(tǒng)中經(jīng)常出現(xiàn)的：
Declare
C1 integer;
Begin
Select count(1) into C1 from table1 where f1 = :f1;
If C1 = 1 then
Update …
Elsif
Insert
End if;
End;
1.3 阻塞
定義： 當(dāng)一個(gè)會(huì)話保持另一個(gè)會(huì)話正在請(qǐng)求的資源上的鎖定時(shí),，就會(huì)發(fā)生阻塞。被阻塞的會(huì)話將會(huì)一直掛起,，知道持有鎖的會(huì)話放棄鎖定的資源為止,。四個(gè)常見(jiàn)的dml語(yǔ)句會(huì)產(chǎn)生阻塞。
Insert
Update
Delete
Select ,。,。。 for update
Insert 發(fā)生阻塞的唯一情況就是用戶擁有一個(gè)建有主鍵（唯一約束）的表,。當(dāng)兩個(gè)會(huì)話同時(shí)試圖向表中插入相同的數(shù)據(jù)時(shí),，其中的一個(gè)會(huì)話將被阻塞，直到另外一個(gè)會(huì)話提交或回滾,。
Update&delete當(dāng)執(zhí)行操作的數(shù)據(jù)行已經(jīng)被另外的會(huì)話鎖定時(shí),，將會(huì)發(fā)生阻塞，直到另一個(gè)會(huì)話提交或回滾,。
當(dāng)用戶發(fā)出select for update語(yǔ)句準(zhǔn)備對(duì)返回的結(jié)果集進(jìn)行修改時(shí),，如果結(jié)果集已經(jīng)被另外的會(huì)話鎖定，則會(huì)發(fā)生阻塞,。需要等另外一個(gè)會(huì)話結(jié)束才可繼續(xù)進(jìn)行,。可以通過(guò)發(fā)出select for update nowait的語(yǔ)句來(lái)避免發(fā)生阻塞,。這時(shí)如果資源被鎖定,，則會(huì)返回錯(cuò)誤。
1.4 TM鎖的類型
鎖模式 鎖描述 解釋 SQL操作 0 None 1 Null 空 Select 2 RS(ROW-S) 行級(jí)別共享鎖,，其他對(duì)象只能查詢這些數(shù)據(jù) Select for update Lock for update
Lock row share
3 RX(ROW-X) 行級(jí)別排他鎖,，提交前不允許DML操作 Insert ,update , delete, lock row share 4 S(SHARE) 共享鎖 Create index Lock share
5 SRX（S/ROW-X） 共享行級(jí)別排他鎖 Lock share row exclusive 6 X（EXCLUSIVE） 排他鎖 Alter table、drop table,、drop index,、truncate table、lock exclusive TM是對(duì)象鎖,，表示可能在這個(gè)對(duì)象上做什么操作,，還沒(méi)有結(jié)束，所以不允許DDL,。即使update where 1=0,，因?yàn)殒i定發(fā)生在真實(shí)修改發(fā)生之前,，不知道會(huì)修改多少記錄，所以TM先產(chǎn)生,。TM共具有5種模式 行級(jí)別排他鎖（RX row exclusive）
當(dāng)我們進(jìn)行DML時(shí)會(huì)自動(dòng)在被更新的表上加RX鎖,，或者也可以通過(guò)lock table t1 in row exclusive mode;命令顯式添加RX鎖。在該鎖定模式下,，允許其他的事務(wù)通過(guò)DML語(yǔ)句修改相同表里的其他數(shù)據(jù)行,，或者通過(guò)lock命令對(duì)相同表添加RX鎖定，但不允許其他事務(wù)對(duì)相同的表添加排他鎖（X鎖）,。
行級(jí)別共享鎖（RS row shared）
通常是通過(guò)select ,。。,。 for update語(yǔ)句添加的,，同時(shí)該方法也是我們用來(lái)手工鎖定某些記錄的主要方法。比如,，當(dāng)我們?cè)诓樵兡承┯涗浀倪^(guò)程中,，不希望其他用戶對(duì)查詢的記錄進(jìn)行更新操作，則可以發(fā)出這樣的語(yǔ)句,。當(dāng)數(shù)據(jù)使用完畢時(shí),，直接rollback命令將鎖定解除。當(dāng)表上添加了RS鎖定以后,，不允許其他事務(wù)對(duì)相同的表添加排他鎖，但是允許其他的事務(wù)通過(guò)DML語(yǔ)句或lock命令鎖定相同表里的其他數(shù)據(jù)行,。
共享鎖（S share）
通過(guò)lock table in share mode 命令添加S鎖,，在鎖定模式下，不允許任何用戶更新表,，但是允許其他用戶發(fā)出select ,。。,。 from table for update 命令對(duì)表添加RS鎖,。（這里有點(diǎn)問(wèn)題，10g的測(cè)試結(jié)果,，select for update給出的是rx鎖,，并且在s鎖已經(jīng)添加的情況下無(wú)法添加rx鎖）
排他鎖（X exclusive）
通過(guò)lock table in exclusive mode命令添加X(jué)鎖。在該鎖定模式下,，其他用戶不能對(duì)表進(jìn)行任何的DML和DDL操作,，該表上只能查詢。
共享行級(jí)別排他鎖（SRX share row exclusive）
通過(guò)lock table in share row exclusive mode命令添加SRX鎖,。該鎖定模式比行級(jí)別排他鎖和共享鎖的級(jí)別都要高,，這時(shí)不能對(duì)相同的表進(jìn)行DML操作,，也不能添加共享鎖。
對(duì)于通過(guò)lock table命令主動(dòng)添加的鎖定來(lái)說(shuō),，如果要釋放它們,，只需要發(fā)出rollback命令即可。
1.5 TX鎖的類型
假設(shè)某個(gè)用戶（假設(shè)為A）發(fā)出如下的語(yǔ)句更新一條記錄： SQL> update employees set last_name='HanSijie' where employee_id=100;
Oracle在對(duì)該SQL進(jìn)行解析以后,，找到employee_id為100的記錄所在的數(shù)據(jù)塊（假設(shè)為58號(hào)數(shù)據(jù)塊）,，并找一個(gè)可用的undo數(shù)據(jù)塊，將last_name列上被更新前的舊值放入該undo數(shù)據(jù)塊,，然后在數(shù)據(jù)塊頭部分配一個(gè)ITL槽,，在該ITL槽里存放當(dāng)前的事務(wù)ID號(hào)、SCN號(hào),、所使用的undo數(shù)據(jù)塊的地址,，以及當(dāng)前還未提交的標(biāo)記等信息。接下來(lái),，在58號(hào)數(shù)據(jù)塊中,，找到被更新的數(shù)據(jù)行，在其頭部設(shè)置一個(gè)鎖定標(biāo)記,，并在頭部記錄當(dāng)前事務(wù)所使用的ITL槽的槽號(hào),。做完這些工作以后，將控制權(quán)（也就是光標(biāo)）返回給用戶,。該鎖定標(biāo)記說(shuō)明當(dāng)前用戶在被修改的數(shù)據(jù)行上已經(jīng)添加了X鎖,。
如果這時(shí)，另一個(gè)用戶（假設(shè)為N）也對(duì)employee_id為100的記錄進(jìn)行修改,，則其過(guò)程和上面描述的一樣,，只不過(guò)B在對(duì)數(shù)據(jù)行的頭部設(shè)置鎖定標(biāo)記時(shí)，發(fā)現(xiàn)該數(shù)據(jù)行頭部已經(jīng)有一個(gè)鎖定標(biāo)記了,，說(shuō)明該記錄已經(jīng)被添加了X鎖,，于是用戶進(jìn)程N(yùn)必須等待，等待該X鎖被釋放,。
可以看到,，Oracle數(shù)據(jù)庫(kù)是在物理層面上實(shí)現(xiàn)對(duì)數(shù)據(jù)行的鎖定問(wèn)題。而且鎖定一條記錄,，并不影響其他用戶對(duì)該記錄的讀取,。比如，如果當(dāng)前有一個(gè)用戶（假設(shè)為C）發(fā)出SQL語(yǔ)句,，檢索employee_id為100的記錄信息,，這時(shí)服務(wù)器進(jìn)程發(fā)現(xiàn)被檢索的記錄有鎖定標(biāo)記，說(shuō)明當(dāng)前該記錄已經(jīng)被其他用戶修改了,，但是還沒(méi)提交,。于是根據(jù)數(shù)據(jù)行頭部記錄的ITL槽的槽號(hào),，在數(shù)據(jù)塊頭部找到該ITL槽，并根據(jù)其中記錄的undo數(shù)據(jù)塊的地址,，找到該undo數(shù)據(jù)塊,，將其中所保存的改變前的舊值取出，并據(jù)此構(gòu)建CR（Consistent Read一致性讀）塊,，該CR塊中的數(shù)據(jù)就是被更新的數(shù)據(jù)塊（也就是58號(hào)數(shù)據(jù)塊）在更新前的內(nèi)容,。于是根據(jù)該CR塊的內(nèi)容，將用戶所需要的信息返回給C,。
對(duì)于Oracle數(shù)據(jù)庫(kù)來(lái)說(shuō),，行級(jí)鎖只有X鎖定模式，沒(méi)有S鎖定模式,。Oracle的鎖定總是盡可能地在最低級(jí)別上完成,。比如更新數(shù)據(jù)行時(shí)，僅僅是鎖定被更新的數(shù)據(jù)行,，并不會(huì)鎖定同一個(gè)數(shù)據(jù)塊中的其他數(shù)據(jù)行,，也不會(huì)阻塞其他用戶查詢被更新的數(shù)據(jù)行。
2,，鎖相關(guān)的數(shù)據(jù)字典
2.1 關(guān)于v$lock This view lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch. Column
Datatype Description ADDR RAW(4 | 8) Address of lock state object KADDR RAW(4 | 8) Address of lock SID NUMBER Identifier for session holding or acquiring the lock TYPE VARCHAR2(2) Type of user or system lock The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:
TM - DML enqueue
TX - Transaction enqueue
UL - User supplied
The locks on the system types are held for extremely short periods of time. The system type locks are listed in Table 6-1.
我們主要關(guān)注TM和TX兩種鎖類型,。UL鎖為用戶自定義的鎖，一般很少會(huì)定義,，基本不用關(guān)注,。其他均為系統(tǒng)鎖，會(huì)很快自動(dòng)釋放,，不用關(guān)注,。
ID1 NUMBER Lock identifier #1 (depends on type) ID2 NUMBER Lock identifier #2 (depends on type) 當(dāng)lock type 為T(mén)M時(shí)，id1為DML-locked object的object_id,。當(dāng)LOCK TYPE為T(mén)X時(shí)，id1為usn+slot,，而id2為seq,。當(dāng)lock type為其他時(shí)，不用關(guān)注
LMODE NUMBER Lock mode in which the session holds the lock: 0 - none
1 - null (NULL) 2 - row-S (SS) 3 - row-X (SX) 4 - share (S) 5 - S/Row-X (SSX) 6 - exclusive (X) 大于0時(shí)表示當(dāng)前會(huì)話以某種方式占有該鎖,，等于0時(shí)表示當(dāng)前會(huì)話正在等待該鎖資源，即表示該會(huì)話被阻塞。 REQUEST NUMBER Lock mode in which the process requests the lock: 0 - none
1 - null (NULL) 2 - row-S (SS) 3 - row-X (SX) 4 - share (S) 5 - S/Row-X (SSX) 6 - exclusive (X) 大于0時(shí),，這里給出的是阻塞當(dāng)前會(huì)話的其他會(huì)話鎖定資源的模式,。 CTIME NUMBER Time since current mode was granted BLOCK NUMBER A value of either 0 or 1, depending on whether or not the lock in question is the blocker. 0：not blocking any other processes
1：this lock blocks other processes
Table 6-1 Values for the TYPE Column: System Types
System Type
Description System Type Description BL Buffer hash table instance NA..NZ Library cache pin instance (A..Z = namespace) CF Control file schema global enqueue PF Password File CI Cross-instance function invocation instance PI, PS Parallel operation CU Cursor bind PR Process startup DF datafile instance QA..QZ Row cache instance (A..Z = cache) DL Direct loader parallel index create RT Redo thread global enqueue DM Mount/startup db primary/secondary instance SC System change number instance DR Distributed recovery process SM SMON DX Distributed transaction entry SN Sequence number instance FS File set SQ Sequence number enqueue HW Space management operations on a specific segment SS Sort segment IN Instance number ST Space transaction enqueue IR Instance recovery serialization global enqueue SV Sequence number value IS Instance state TA Generic enqueue IV Library cache invalidation instance TS Temporary segment enqueue (ID2=0) JQ Job queue TS New block allocation enqueue (ID2=1) KK Thread kick TT Temporary table enqueue LA .. LP Library cache lock instance lock (A..P = namespace) UN User name MM Mount definition global enqueue US Undo segment DDL MR Media recovery WL Being-written redo log instance 2.2 其他相關(guān)視圖說(shuō)明
視圖名 描述 主要字段說(shuō)明 v$session 查詢會(huì)話的信息和鎖的信息。 sid,serial#：表示會(huì)話信息,。 program：表示會(huì)話的應(yīng)用程序信息,。
row_wait_obj#：表示等待的對(duì)象,和dba_objects中的object_id相對(duì)應(yīng),。
lockwait :該會(huì)話等待的鎖的地址,與v$lock的kaddr對(duì)應(yīng).
v$session_wait 查詢等待的會(huì)話信息。 sid：表示持有鎖的會(huì)話信息,。 Seconds_in_wait：表示等待持續(xù)的時(shí)間信息
Event：表示會(huì)話等待的事件,，鎖等于enqueue
dba_locks 對(duì)v$lock的格式化視圖。 Session_id：和v$lock中的Sid對(duì)應(yīng),。 Lock_type：和v$lock中的type對(duì)應(yīng),。
Lock_ID1： 和v$lock中的ID1對(duì)應(yīng)。
Mode_held,mode_requested：和v$lock中
的lmode,request相對(duì)應(yīng),。
v$locked_object 只包含DML的鎖信息,，包括回滾段和會(huì)話信息。 Xidusn,xidslot,xidsqn：表示回滾段信息,。和 v$transaction相關(guān)聯(lián),。
Object_id：表示被鎖對(duì)象標(biāo)識(shí)。
Session_id：表示持有鎖的會(huì)話信息,。
Locked_mode：表示會(huì)話等待的鎖模式的信
息,，和v$lock中的lmode一致。
3,，鎖相關(guān)的sql statement 3.1 TM阻塞的檢索 select /*+ rule*/
lpad('--',decode(b.block,1,0,4))||s.USERNAME user_name, b.TYPE,o.owner||'.'||o.object_name object_name, s.SID,s.SERIAL#,decode(b.REQUEST,0,'blocked','waiting') status from dba_objects o,v$session s,v$lock v,v$lock b where v.ID1 = o.object_id and v.SID = b.SID and v.SID = s.SID and (b.BLOCK = 1 or b.REQUEST > 0) and v.TYPE = 'TM' order by b.ID2,v.ID1,user_name desc; 3.2 latch阻塞的檢索
select sql_text
from v$sqlarea s where (s.ADDRESS,s.HASH_VALUE ) in ( select sql_address,sql_hash_value from v$session where sid in ( select sid from v$session a,sys.x$kglpn b where a.SADDR = b.kglpnuse and b.kglpnmod <> 0 and b.kglpnhdl in ( select p1raw from v$session_wait where sid = &sid and event like 'library%' ) ) ) 3.3. 用于檢查系統(tǒng)中鎖的簡(jiǎn)單腳本
select s.username, s.sid, l.type, l.id1, l.id2, l.lmode, l.request, p.spid PID
from v$lock l, v$session s, v$process p where s.sid = l.sid and p.addr = s.paddr and s.username is not null order by id1, s.sid, request; 3.4. 顯示數(shù)據(jù)庫(kù)鎖的信息
set pagesize 60
set linesize 132 select s.username username, a.sid sid, a.owner || '.' || a.object object, s.lockwait, t.sql_text sql from v$sqltext t, v$session s, v$access a where t.address = s.sql_address and t.hash_value = s.sql_hash_value and s.sid = a.sid and a.owner != 'SYS' and upper(substr(a.object,1,2)) != 'V$'; / 3.5. 產(chǎn)生在數(shù)據(jù)庫(kù)中持有的鎖的報(bào)表
select b.sid, c.username, c.osuser, c.terminal,
decode(b.id2, 0, a.object_name, 'Trans-' || to_char(b.id1)) object_name, b.type, decode(b.lmode, 0, '-Waiting-', 1, 'Null', 2, 'Row Share', 3, 'Row Excl', 4, 'Share', 5, 'Sha Row Exc', 6, 'Exclusive', 'Other') "Lock Mode", decode(b.request, 0, ' ', 1, 'Null', 2, 'Row Share', 3, 'Row Excl', 4, 'Share', 5, 'Sha Row Exc', 6, 'Exclusive', 'Other') "Req Mode" from dba_objects a, v$lock b, v$session c where a.object_id(+) = b.id1 and b.sid = c.sid and c.username is not null order by b.sid, b.id2; 3.6. 產(chǎn)生等待鎖的用戶的報(bào)告
column username format a15
column sid format 9990 heading sid column type format a4 column lmode format 990 heading 'HELD' column request format 990 heading 'REQ' column id1 format 9999990 column id2 format 9999990 break on id1 skip 1 dup spool tfslckwt.lst select sn.username, m.sid, m.type, decode(m.lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.' 6, 'Exclusive', lmode, ltrim(to_char(lmode, '990'))) lmode, decode(m.request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl,', 6, 'Exclusive', request, ltrim(to_char(m.request, '990'))) request, m.id1, m.id2 from v$session sn, v$lock m where (sn.sid = m.sid and m.request != 0) or (sn.sid = m.sid and m.request = 0 and lmode = 4 and (id1, id2) in (select s.id1, s.id2 from v$lock s where request != 0 and s.id1 = m.id1 and s.id2 = m.id2) ) order by id1, id2, m.request; spool off clear breaks 3.7. 顯示持有鎖的會(huì)話的信息
set linesize 132 pagesize 66
break on Kill on username on terminal column Kill heading 'Kill String' fromat a13 column res heading 'Resource Type' format 999 column id1 format 9999990 column id2 format 9999990 column lmode heading 'Lock Held' format a20 column request heading 'Lock Requested' format a20 column serial# format 99999 column username format a10 heading "Username" column terminal heading Term format a6 column tab format a35 heading "Table Name" column owner format a9 column Address format a18 select nvl(s.username, 'Internal') username, nvl(s.terminal, 'None') terminal, l.sid || ',' || s.serial# Kill, u1.name || '.' || substr(t1.name, 1, 20) tab, decode(l.lmode, 1, 'No Lock', 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null) lmode, decode(l.request, 1, 'No Lock', 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null) request from v$lock l, v$session s, sys.user$ u1, sys.obj$ t1 where l.sid = s.sid and t1.obj# = decode(l.id2, 0, l.id1, l.id2) and u1.user# = t1.owner# and s.type != 'BACKGROUND' order by 1, 2, 5; 3.8. 用于鑒別系統(tǒng)中閂性能的腳本
column name heading "Name" format a20
column pid heading "HSid" format a3 column gets heading "Gets" format 999999990 column misses heading "Miss" format 99990 column im_gets heading "ImG" format 99999990 column im_misses heading "ImM" format 999990 column sleeps heading "Sleeps" format 99990 select n.name name, h.pid pid, l.gets gets, l.misses misses, l.immediate_gets im_gets, l.immediate_misses im_misses, l.sleeps sleeps from v$latchname n, v$latchholder h, v$latch l where l.latch# = n.latch# and l.addr = h.laddr(+); 3.9. 使用v$session wait視圖來(lái)鑒別閂競(jìng)爭(zhēng)
select event, p1text, p1, p2text, p2, seq#, wait_time, state
from v$session_wait where sid = '&&1' and event = 'latch free'; 3.10. 列舉用于閂競(jìng)爭(zhēng)的信息
ttitle center 'Latch Contention Report' skip 3
col name form a25 col gets form 999,999,999 col misses form 999.99 col spins form 999.99 col igets form 999,999,999 col imisses form 999.99 select name, gets, misses * 100 / decode(gets, 0, 1, gets) misses, spin_gets * 100 / decode(misses, 0, 1, misses) spins, immediate_gets igets, immediate_misses * 100 / decode(immediate_gets, 0, 1, immediate_gets) imisses from v$latch order by gets + immediate_gets; / 3.11. 檢索閂睡眠率
col name form a18 trunc
col gets form 999,999,990 col miss form 90.9 col cspins form a6 heading 'spin | sl06' col csleep1 form a5 heading 'sl01 | sl07' col csleep2 form a5 heading 'sl02 | sl08' col csleep3 form a5 heading 'sl03 | sl09' col csleep4 form a5 heading 'sl04 | sl10' col csleep5 form a5 heading 'sl05 | sl11' col Interval form a12 set recsep off select a.name, a.gets gets, a.misses * 100 / decode(a.gets, 0, 1, a.gets) miss, to_char(a.spin_gets * 100 / decode(a.misses, 0, 1, a.misses), '990.9') || to_char(a.sleep6 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') cspins, to_char(a.sleep1 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') || to_char(a.sleep7 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') csleep1, to_char(a.sleep2 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') || to_char(a.sleep8 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') csleep2, to_char(a.sleep3 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') || to_char(a.sleep9 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') csleep3, to_char(a.sleep4 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') || to_char(a.sleep10 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') csleep4, to_char(a.sleep5 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') || to_char(a.sleep11 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') csleep5 from v$latch a where a.misses <> 0 order by 2 desc; / 3.12 oracle TX鎖的等待序列
2008-07-16 15:49今天測(cè)試GPS在線實(shí)時(shí)交通系統(tǒng)時(shí),，發(fā)現(xiàn)主計(jì)算節(jié)點(diǎn)在更新一張表時(shí)一直過(guò)不去，費(fèi)了好一番周折才找到罪魁禍?zhǔn)?。下面這段腳本是功臣,。
執(zhí)行這段腳本，能知道哪個(gè)數(shù)據(jù)庫(kù)用戶,、哪臺(tái)機(jī)器鎖住了該表,，哪個(gè)用戶哪臺(tái)機(jī)器在等待該資源。 SELECT /*+ choose */
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 本文來(lái)自CSDN博客,，轉(zhuǎn)載請(qǐng)標(biāo)明出處：http://blog.csdn.net/47522341/archive/2010/04/06/5454531.aspx
|
|
|
