|
10g中enqueue TX等待分為4類，分別是
1. enq:TX - row lock contention
2. enq:TX - index contention
3. enq:TX - ITL
4. enq:TX - contention
前三種的含義比較明顯，第4種是表示其它類型的transaction contention,即除了前三種之外的都包含在其中。 -
有多種情況都可能造成enq:TX - contention。比如：一個(gè)session中執(zhí)行DML而不提交，另一個(gè)session執(zhí)行alter tablespace XXX read only,就會(huì)出現(xiàn)這個(gè)等待事件。
測試情況：
單實(shí)例情況：
session 1:
SQL> select sid from v$mystat where rownum <2;
SID
----------
145
SQL> select table_name,tablespace_name from user_tables where table_name='INFO';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
INFO USERS
SQL> update info set note=upper(note);
已更新35行。
SQL> (注意未提交)
session 2:
SQL> select sid from v$mystat where rownum <2;
SID
----------
148
SQL> alter tablespace users read only;
由于session 1未提交，還在使用users表空間，session 2出現(xiàn)等待。
session 3:
SQL> select sid,event,p1,p2,p3 from v$session_wait where sid=148;
SID EVENT P1 P2 P3
------- ---------------------------------------------------------------- ---------- ---------- ----------
148 enq: TX - contention 1415053316 65563 166
查詢得知session 2在等待enq: TX - contention事件。其中p1,p2,p3含義可以從如下視圖中得到。
SQL> SELECT name, parameter1, parameter2, parameter3 from v$event_name where name = 'enq: TX - contention';
NAME PARAMETER1 PARAMETER2 PARAMETER3
------------------------- -------------------- -------------------- --------------------
enq: TX - contention name|mode usn<<16 | slot sequence
從上述結(jié)果中可以看到：
parameter1表示enqueue的name和mode。parameter2的高16位表示事務(wù)的xidusn,低16位表示事務(wù)的xidslot,parameter3表示事務(wù)的xidsqn，即p2,p3表示一個(gè)特定的事務(wù)。
結(jié)合v$transaction和v$session,就可以知道阻塞session 2的會(huì)話信息了。
檢查enqueue的name和mode
SQL> SELECT sid, CHR (BITAND (p1,-16777216) / 16777215) ||
2 CHR (BITAND (p1, 16711680) / 65535) enq,
3 DECODE (BITAND (p1, 65535), 1, 'Null', 2, 'Sub-Share',
4 3, 'Sub-Exclusive', 4, 'Share', 5, 'Share/Sub-Exclusive',
5 6, 'Exclusive', 'Other') lock_mode
6 FROM v$session_wait WHERE sid = 148;
SID ENQ LOCK_MODE
------- ---- -------------------
148 TX Share
檢查阻塞session 2的會(huì)話：
SQL> select sid from v$session where taddr in
2 (select b.addr from v$session_wait a,v$transaction b
3 where a.event='enq: TX - contention' and trunc(a.p2/power(2,16)) = xidusn
4 and (bitand(a.p2,to_number('ffff','xxxx'))+0) = xidslot and a.p3 = xidsqn);
SID
-------
145
這里我們可以看到，造成session 2等待的事務(wù)是由session 1執(zhí)行的。
這里還可以用另一種方法找到阻塞session 2的會(huì)話：
1、先查看session 2請求和持有的事務(wù)鎖情況：
SQL> select sid,id1,id2,trunc(id1/power(2,16)) rbs,bitand(id1,to_number('ffff','xxxx'))+0 slot,id2 seq,lmode,request,type from v$lock where type = 'TX' and sid=&sid;
輸入 sid 的值: 148
原值 2: from v$lock where type = 'TX' and sid=&sid
新值 2: from v$lock where type = 'TX' and sid=148
SID ID1 ID2 RBS SLOT SEQ LMODE REQUEST TY
------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --
148 65563 166 1 27 166 0 4 TX
148 196646 168 3 38 168 6 0 TX
148在請求4(share)類型的鎖時(shí)出現(xiàn)等待。
注意這里的ID1和ID2與v$session_wait中的p2,p3完全一致，都是表示某個(gè)事務(wù)的。
SQL> select sid,event,p1,p2,p3 from v$session_wait where sid=148;
SID EVENT P1 P2 P3
------- ---------------------------------------------------------------- ---------- ---------- ----------
148 enq: TX - contention 1415053316 65563 166
2、查看事務(wù)(ID1,ID2)使用鎖的情況
SQL> select sid,trunc(id1/power(2,16)) rbs,bitand(id1,to_number('ffff','xxxx'))+0 slot,id2 seq,lmode,request
from v$lock where type='TX' and id1=&id1 and id2=&id2;
輸入 id1 的值: 65563
輸入 id2 的值: 166
原值 2: from v$lock where type='TX' and id1=&id1 and id2=&id2
新值 2: from v$lock where type='TX' and id1=65563 and id2=166
SID RBS SLOT SEQ LMODE REQUEST
------- ---------- ---------- ---------- ---------- ----------
148 1 27 166 0 4
145 1 27 166 6 0
這里可以看到148在請求share型事務(wù)鎖，而145持有exclusive型事務(wù)鎖，因此造成了session 2的等待。
RAC情況：與單實(shí)例的情況類似
在node 1上執(zhí)行session：
SQL> conn test/test
Connected.
SQL> select sid from v$mystat where rownum < 2;
SID
----------
617
SQL> insert into info values (1);
1 row created.
不提交
在node 2上執(zhí)行session:
SQL> alter tablespace test read only;
出現(xiàn)等待。
在任意node上執(zhí)行：
SQL> select c.inst_id,c.sid
from gv$session_wait a,gv$transaction b, gv$session c
where a.event='enq: TX - contention'
and trunc(a.p2/power(2,16)) = b.xidusn
and (bitand(a.p2,to_number('ffff','xxxx'))+0) = b.xidslot
and a.p3 = b.xidsqn
and c.taddr = b.addr;
INST_ID SID
---------- ----------
1 617
Enqueue （隊(duì)列等待）：
Enqueue是一種保護(hù)共享資源的鎖定機(jī)制。該鎖定機(jī)制保護(hù)共享資源，以避免因并發(fā)操作而損壞數(shù)據(jù)，比如通過鎖定保護(hù)一行記錄，避免多個(gè)用戶同時(shí)更新。Enqueue采用排隊(duì)機(jī)制，即FIFO（先進(jìn)先出）來控制資源的使用。
在Oracle 10g之前，Enqueue事件是一組鎖定事件的集合，如果數(shù)據(jù)庫中這個(gè)等待事件比較顯著，我們還需要進(jìn)一步來追蹤是哪個(gè)類別的鎖定引發(fā)了數(shù)據(jù)庫等待。
從Oracle 10g開始，Oracle對(duì)于隊(duì)列等待進(jìn)行了細(xì)分，v$event_name視圖中可以查詢這些細(xì)分后的等待事件，簡要摘錄幾個(gè)示例如下：
sys@CCDB> select name,wait_class
2 from v$event_name
3 where name like '%enq%';
NAME WAIT_CLASS
------------------------------------- ------------------------
enq: PW - flush prewarm buffers Application
enq: RO - contention Application
enq: RO - fast object reuse Application
enq: KO - fast object checkpoint Application
enq: TM - contention Application
enq: ST - contention Configuration
enq: TX - row lock contention Application
enq: TX - allocate ITL entry Configuration
enq: TX - index contention Concurrency
enq: TW - contention Administrative
enq: HW - contention Configuration
......
Oracle 的鎖按照類型可以分為排他鎖（Exclusive，縮寫為X）與共享鎖（Share，縮寫為S），或者是兩者的組合鎖。排他鎖（X）也被稱為獨(dú)占鎖，在排他鎖釋放之前，一個(gè)對(duì)象上不能施加任何其他類型的鎖定；而共享鎖（S）在釋放之前，對(duì)象上還可以繼續(xù)加其他類型的共享鎖，但是不能加排他鎖。
如果按照事務(wù)的類型劃分，又可以將鎖定劃分為DML鎖，DDL鎖以及內(nèi)存鎖（也即通常所說的Latch）。Oracle在數(shù)據(jù)庫內(nèi)部用Enqueue等待來記錄鎖定，通過Latch Free等待事件來記錄閂。Enqueue等待常見的有ST、HW、TX、TM等，下面擇要進(jìn)行介紹。
1. 最重要的鎖定：TM與TX鎖
對(duì)于數(shù)據(jù)庫來說，最常見的鎖定類型是TM以及TX鎖定。
TX鎖通常被稱為事務(wù)鎖，當(dāng)一個(gè)事務(wù)開始時(shí)，如執(zhí)行INSERT/DELETE/UPDATE/MERGE等操作或者使用SELECT ... FOR UPDATE語句進(jìn)行查詢時(shí)，會(huì)首先獲取事務(wù)鎖，直到該事務(wù)結(jié)束。Oracle的TX鎖定是在行級(jí)獲得的，每個(gè)數(shù)據(jù)行上都存在一個(gè)鎖定位（1b-Lock Byte），用于判斷該記錄是否被鎖定，同時(shí)在每個(gè)數(shù)據(jù)塊的頭部（Header）存在一個(gè)ITL的數(shù)據(jù)結(jié)構(gòu)，用于記錄事務(wù)信息等，當(dāng)需要修改數(shù)據(jù)時(shí)，首先需要獲得回滾段空間用于存儲(chǔ)前鏡像信息，然后這個(gè)事務(wù)信息同樣被記錄在ITL上，通過ITL可以將回滾信息和數(shù)據(jù)塊關(guān)聯(lián)起來，所以說Oracle的行級(jí)鎖定是在數(shù)據(jù)塊上獲得的，行級(jí)鎖只有排他鎖沒有共享模式。
TM鎖通常稱為表級(jí)鎖，可以通過手工發(fā)出lock命令獲得，或者通過DML操作以及SELECT FOR UPDATE獲得，表級(jí)鎖可以防止其他進(jìn)程對(duì)表加X排他鎖，防止在對(duì)數(shù)據(jù)修改時(shí)，其他任務(wù)通過DDL來修改表結(jié)構(gòu)或者truncate、drop表等操作。可以通過v$lock視圖來觀察鎖定信息，其中TYPE字段表示鎖定類型。對(duì)于TM鎖LMODE字段又代表了不同級(jí)別的TM鎖，這些級(jí)別包括2-row-S(SS)、3-row-X(SX)、4-share(S)、5-S/Row-X(SSX)和6-exclusive(X)。
當(dāng)執(zhí)行DML操作時(shí)，首先加TM鎖，如果能獲得鎖定，則繼續(xù)加TX事務(wù)鎖。在一個(gè)會(huì)話中，一般只存在一個(gè)TX事務(wù)鎖，在提交或回滾之前，該會(huì)話的所有DML操作都屬于一個(gè)事務(wù)，使用一個(gè)回滾段，占用一個(gè)回滾段事務(wù)槽（Slot）。
以下通過SCOTT用戶鎖定一行記錄，暫時(shí)不要提交：
scott@CCDB> update emp set sal = 4000 where empno = 7788;
1 row updated.
在另外session通過v$lock視圖可以看到相關(guān)的鎖定信息；
sys@CCDB> select sid,username from v$session where username = 'SCOTT';
SID USERNAME
---------- ------------------------------
1075 SCOTT
sys@CCDB> select * from v$lock where sid = 1075;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000008F836260 000000008F8362B8 1075 AE 99 0 4 0 1208 0
00002BA14E74A7F8 00002BA14E74A858 1075 TM 69539 0 3 0 16 0
000000008DF49A30 000000008DF49AA8 1075 TX 65551 30498 6 0 16 0
此時(shí)表上的行級(jí)排他鎖會(huì)阻塞對(duì)于表的DDL語句：
sys@CCDB> truncate table scott.emp;
truncate table scott.emp
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
此外，TM鎖定的ID1代表的就是鎖定的對(duì)象號(hào)：
sys@CCDB> select owner,object_name from dba_objects where object_id = 69539;
OWNER OBJECT_NAME
--------------- ---------------
SCOTT EMP
而TX鎖的ID1代表的是事務(wù)的回滾段回滾段號(hào)、事務(wù)槽號(hào)，ID2代表的是順序號(hào)：
sys@CCDB> select trunc(65551/power(2,16)),mod(65551,power(2,16)) from dual;
TRUNC(65551/POWER(2,16)) MOD(65551,POWER(2,16))
------------------------ ----------------------
1 15
通過v$transaction視圖也可以找到這個(gè)事務(wù)的信息（注意XIDSQN正是TX鎖的ID2信息）：
sys@CCDB> select XIDUSN,XIDSLOT,XIDSQN from v$transaction;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
1 15 30498
如果轉(zhuǎn)儲(chǔ)回滾段信息進(jìn)行分析，再結(jié)合ITL事務(wù)槽，可以清晰地看到鎖定的含義以及整個(gè)事務(wù)的處理過程。
2. 最常見的鎖定：MR與AE鎖
可能很多朋友都注意過，在v$lock視圖中，最常見的其實(shí)是MR鎖，也就是介質(zhì)恢復(fù)鎖（Media Recovery）：
sys@CCDB> select * from v$lock where type = 'MR';
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000BC2EE378 00000000BC2EE3D0 1097 MR 1 0 4 0 6984045 0
00000000BC2EE448 00000000BC2EE4A0 1097 MR 2 0 4 0 6984045 0
00000000BC2EE518 00000000BC2EE570 1097 MR 3 0 4 0 6984045 0
00000000BC2EE5E8 00000000BC2EE640 1097 MR 4 0 4 0 6984045 0
00000000BC2EE6B8 00000000BC2EE710 1097 MR 5 0 4 0 6984045 0
00000000BC2EE788 00000000BC2EE7E0 1097 MR 6 0 4 0 6984045 0
00000000BC2EE858 00000000BC2EE8B0 1097 MR 7 0 4 0 6984045 0
00000000BC2EE940 00000000BC2EE998 1097 MR 8 0 4 0 6984045 0
00000000BC2EEA10 00000000BC2EEA68 1097 MR 201 0 4 0 6984045 0
00000000BC2F12F8 00000000BC2F1350 1097 MR 9 0 4 0 1132526 0
10 rows selected.
MR鎖用于保護(hù)數(shù)據(jù)庫文件，使得文件在數(shù)據(jù)庫打開、表空間Online時(shí)不能執(zhí)行恢復(fù)。當(dāng)進(jìn)程對(duì)數(shù)據(jù)文件執(zhí)行恢復(fù)時(shí)，需要排他的獲得MR鎖。當(dāng)數(shù)據(jù)庫打開時(shí)，每個(gè)文件上都分配一個(gè)MR鎖。注意在以上輸出中ID1代表文件號(hào)，其中也包含了201號(hào)臨時(shí)文件。
從Oracle Database 11g開始，除了每個(gè)文件要獲得MR鎖之外，每個(gè)登錄數(shù)據(jù)庫的會(huì)話現(xiàn)在都會(huì)缺省獲得一個(gè)AE鎖：
sys@CCDB> select * from v$lock where type = 'AE' and rownum <= 5;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000BC2EDF68 00000000BC2EDFC0 822 AE 99 0 4 0 2761930 0
00000000BC2EE108 00000000BC2EE160 946 AE 99 0 4 0 3458645 0
00000000BC2EE1D8 00000000BC2EE230 1003 AE 99 0 4 0 207674 0
00000000BC2EE2A8 00000000BC2EE300 1092 AE 99 0 4 0 6984538 0
00000000BC2EEAE0 00000000BC2EEB38 991 AE 99 0 4 0 3458644 0
現(xiàn)在MR鎖定和AE鎖定是數(shù)據(jù)庫中最為常見的鎖定。
sys@CCDB> select name from v$event_name where name like '%AE%';
NAME
------------------------------------------------------------
enq: AE - lock
3. ST（空間事務(wù)鎖）
ST鎖主要用于空間管理和字典管理的表空間（DMT）的區(qū)間分配，在DMT中典型的是對(duì)于uet$和fet$數(shù)據(jù)字典表的爭用。對(duì)于支持LMT的版本。應(yīng)該盡量使用本地管理表空間，或者考慮手工預(yù)分配一定數(shù)量的區(qū)（Extent），減少動(dòng)態(tài)擴(kuò)展時(shí)發(fā)生的嚴(yán)重隊(duì)列競爭。
以下案例說明了ST鎖可能會(huì)導(dǎo)致的嚴(yán)重性能問題。
DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------------
DB 40757346 tqgzs 1 8.1.7.4.0 NO server
Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 2845 31-10月-03 02:10:16 46
End Snap: 2848 31-10月-03 03:40:05 46
Elapsed: 89.82 (mins)
對(duì)于一個(gè)Statspack的report，采樣時(shí)間是非常重要的維度，離開時(shí)間做參考，任何等待都不足以說明問題。
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
enqueue 53,793 16,192,686 67.86
rdbms ipc message 19,999 5,927,350 24.84
pmon timer 1,754 538,797 2.26
smon timer 17 522,281 2.19
SQL*Net message from client 94,525 520,104 2.18
-------------------------------------------------------------
在Statspack分析中，Top 5等待事件是我們最為關(guān)注的部分。這個(gè)系統(tǒng)中，除了enqueue等待事件以外，其他4個(gè)都屬于空閑等待事件，無須關(guān)注。來關(guān)注一下enqueue等待事件，在89.82 (mins)的采樣間隔內(nèi)，累計(jì)enqueue等待長達(dá)16,192,686(cs)，即45小時(shí)左右。這個(gè)等待已經(jīng)太過顯著，實(shí)際上這個(gè)系統(tǒng)也正因此遭遇了巨大的困難，觀察到隊(duì)列等待以后，這應(yīng)該關(guān)注隊(duì)列等待在等待什么資源�？焖偬D(zhuǎn)的Statspack的其他部分，看到以下詳細(xì)內(nèi)容：
Enqueue activity for DB: DB Instance: aaa Snaps: 2845 -2848
-> ordered by waits desc, gets desc
Enqueue Gets Waits
---------- ------------ ----------
ST 1,554 1,554
-------------------------------------------------------------
看到主要隊(duì)列等待在等待ST鎖定，對(duì)于DMT，我們說這個(gè)等待和FET$、UET$的爭用緊密相關(guān)。再回過頭來研究捕獲SQL語句：
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
4,800,073 10,268 467.5 51.0 2913840444
select length from fet$ where file#=:1 and block#=:2 and ts#=:3
803,187 10,223 78.6 8.5 528349613
delete from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 a
nd ext#=:4
454,444 10,300 44.1 4.8 1839874543
select file#,block#,length from uet$ where ts#=:1 and segfile#=:
2 and segblock#=:3 and ext#=:4
23,110 10,230 2.3 0.2 3230982141
insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
21,201 347 61.1 0.2 1705880752
select file# from file$ where ts#=:1
….
9,505 12 792.1 0.1 1714733582
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe
re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
6,426 235 27.3 0.1 1877781575
delete from fet$ where file#=:1 and block#=:2 and ts#=:3
可以看到數(shù)據(jù)庫頻繁操作UET$、FET$系統(tǒng)表已經(jīng)成為了系統(tǒng)的主要瓶頸。
至此，已經(jīng)可以準(zhǔn)確地為該系統(tǒng)定位問題，相應(yīng)的解決方案也很容易確定，在Oracle 8.1.7中使用LMT代替DMT，這是解決問題的根本辦法，當(dāng)然實(shí)施起來還要進(jìn)行綜合考慮，實(shí)際情況還要復(fù)雜得多。
- The End -
本文出自：億恩科技【mszdt.com】
服務(wù)器租用/服務(wù)器托管中國五強(qiáng)！虛擬主機(jī)域名注冊頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]
|
0371-60135900
京公網(wǎng)安備41019702002023號(hào)
香港服務(wù)器租用
美國服務(wù)器租用
電信骨干機(jī)房
聯(lián)通骨干機(jī)房
