Oracle 學習動態效能表

Oracle 學習動態效能表 v$locked_object

本視圖列出系統上的每個交易處理所獲得的所有鎖。

V$LOCKED_OBJECT中的列說明：

XIDUSN：復原段號
XIDSLOT：槽號
XIDSQN：序號
OBJECT_ID：被鎖對象ID
SESSION_ID：持有鎖的sessionID
ORACLE_USERNAME：持有鎖的Oracle 使用者名稱
OS_USER_NAME：持有鎖的作業系統 使用者名稱
PROCESS：作業系統進程號
LOCKED_MODE：鎖模式，值同上表1

樣本：1.以DBA角色, 查看當前資料庫裡鎖的情況可以用如下SQL語句：
select object_id,session_id,locked_mode from v$locked_object;

select t2.username, t2.sid, t2.serial#, t2.logon_time
from v$locked_object t1, v$session t2
where t1.session_id = t2.sid order by t2.logon_time;

如果有長期出現的一列，可能是沒有釋放的鎖。我們可以用下面SQL語句殺掉長期沒有釋放非正常的鎖：

alter system kill session 'sid,serial#';

Oracle 學習動態效能表 v$locked

V$LOCK這個視圖列出Oracle 伺服器當前擁有的鎖以及未完成的鎖或栓鎖請求。如果你覺著session在等待等待事件隊列那你應該檢查本視圖。如果你發現session在等待一個鎖，那麼按如下先後順序：

使用V$LOCK找出session持有的鎖。
使用V$SESSION找出持有鎖或等待鎖的session執行的sql語句。
使用V$SESSION_WAIT找出什麼原因導致session持有鎖堵塞。
使用V$SESSION擷取關於持有鎖的程式和使用者的更多資訊。

V$LOCK中的常用列

SID：表示持有鎖的會話資訊。
TYPE：表示鎖的類型。值包括TM和TX等。
LMODE：表示會話等待的鎖模式的資訊。用數字0－6表示，和表1相對應。
REQUEST：表示session請求的鎖模式的資訊。
ID1,ID2：表示鎖的對象標識。

公用鎖類型

在Oracle資料庫中，DML鎖主要包括TM鎖和TX鎖，其中TM鎖稱為表級鎖，TX鎖稱為事務鎖或行級鎖。

當Oracle執行DML語句時，系統自動在所要操作的表上申請TM類型的鎖。當TM鎖獲得後，系統再自動申請TX類型的鎖，並將實際鎖定的資料行的鎖標誌位進行置位。這樣在事務加鎖前檢查TX鎖相容性時就不用再逐行檢查鎖標誌，而只需檢查TM鎖模式的相容性即可，大大提高了系統的效率。TM鎖包括了SS、SX、S、X等多種模式，在資料庫中用0－6來表示。不同的SQL操作產生不同類型的TM鎖，如下表1。

TX：行級鎖，事務鎖

在改變資料時必須是排它模式(mode 6)。每一個活動事務都擁有一個鎖。它將在事務結束(commit/rollback)時釋放。如果一個塊包括的列被改變而沒有ITL(interested transaction list)槽位(entries)，那麼session將鎖置於共用模式(mode 4)。當session獲得塊的ITL槽位時釋放。當一個事務首次發起一個DML語句時就獲得一個TX鎖，該鎖保持到事務被提交或復原。當兩個或多個會話在表的同一條記錄上執行DML語句時，第一個會話在該條記錄上加鎖，其他的會話處於等待狀態。當第一個會話提交後，TX鎖被釋放，其他會話才可以加鎖。指出復原段和事務表項。

按下列項以避免競爭：
避免TX－6類型競爭，需要根據您的應用而定。
避免TX－4類型競爭，可以考慮增加對象INITRANS參數值。

TM：表級鎖

資料庫執行任何DDL語句時必須是排它模式；例如alter table,drop table。執行像insert,update,delete這類DML語句時處於共用模式。它防止其它session對同一個對象同時執行ddl語句。任何對象擁有正被改變的資料，TM鎖都將必須存在。鎖指向對象。在TM隊列避免競爭，可以考慮屏蔽對象表級鎖，屏蔽表級鎖防止對象執行任何ddl語句。

ST：空間事務鎖

每個資料庫(非執行個體)擁有一個ST鎖。除了本地管理資料表空間，在space管理操作(建立或刪除extents)時必須是排它模式。對象creation, dropping, extension, 以及truncation都處於這種鎖。多數公用原因的爭奪，是在磁碟排序(並非使用真正的暫存資料表空間)或復原段擴充或收縮。

按如下項以避免競爭：
使用真正的暫存資料表空間(true temporary tablespaces)，利用臨時檔案。臨時段在磁碟排序之後並不建立或刪除。
使用本地管理資料表空間。
指定復原段避免動態擴充和收縮，或使用自動undo management。
避免應用執行建立或刪除資料庫物件。

UL：使用者定義鎖

使用者可以自訂鎖。內容較多並與此節關係不大，略過。

V$LOCK中的串連列

Column                View                Joined Column(s)
SID                   V$SESSION           SID
ID1, ID2, TYPE        V$LOCK              ID1, ID2, TYPE
ID1                   DBA_OBJECTS         OBJECT_ID
TRUNCID1/65536)       V$ROLLNAME          USN

如果session在等待鎖，這可被用於找出session持有的鎖。可被用於找出DML鎖類型的被鎖對象(type='TM')，可被用於找出行級事務鎖(TYPE='TX')使用中的復原段，不過需要通過V$TRANSACTION串連查詢得到。

 

表1 Oracle的TM鎖類型
鎖模式	鎖描述	解釋	SQL操作
0	none
1	NULL	空	Select
2	SS(Row-S)	行級共用鎖定，其他對象只能查詢這些資料行	Select for update、Lock for update、Lock row share
3	SX(Row-X)	行級排它鎖，在提交前不允許做DML操作	Insert、Update、Delete、Lock row share
4	S(Share)	共用鎖定	Create index、Lock share
5	SSX(S/Row-X)	共用行級排它鎖	Lock share row exclusive
6	X(Exclusive)	排它鎖	Alter table、Drop table、Drop index、Truncate table 、Lock exclusive

數字越大鎖層級越高, 影響的操作越多。一般的查詢語句如select ... from ... ;是小於2的鎖, 有時會在v$locked_object出現。select ... from ... for update; 是2的鎖。

當對話使用for update子串開啟一個遊標時，所有返回集中的資料行都將處於行級(Row-X)獨佔式鎖定，其他對象只能查詢這些資料行，不能進行update、delete或select...for update操作。insert / update / delete ... ; 是3的鎖。       

沒有commit之前插入同樣的一條記錄會沒有反應,因為後一個3的鎖會一直等待上一個3的鎖,我們必須釋放掉上一個才能繼續工作。

建立索引的時候也會產生3,4層級的鎖。locked_mode為2,3,4不影響DML(insert,delete,update,select)操作,但DDL(alter,drop等)操作會提示ora-00054錯誤。有主外鍵約束時 update / delete ... ; 可能會產生4,5的鎖。DDL語句時是6的鎖。

如果出現了鎖的問題,某個DML操作可能等待很久沒有反應。當你採用的是直接連接資料庫的方式，也不要用OS系統命令 $kill process_num 或者 $kill -9 process_num 來終止使用者串連，因為一個使用者進程可能產生一個以上的鎖, 殺OS進程並不能徹底清除鎖的問題。記得在資料庫層級用alter system kill session 'sid,serial#';殺掉不正常的鎖。

樣本：

我按照自己的理解示範的TX,TM鎖如下：

1.create table TMP1(col1 VARCHAR2(50));--建立暫存資料表
2.select * from v$lock; --關掉當前鎖資訊
3.select * from tmp1 for update; --加鎖
4.select * from v$lock; --看看現在的鎖列表，是不是多了兩條記錄。Type分別為tx,tm，對照表1。
5.新開一個串連，然後
select * from tmp1 for update; --呵呵，等待狀態了吧
select * from v$lock; --又新增了兩條記錄，其它一條type=tx,lmode=0
7.查看當前被鎖的session正在執行的sql語句
SQL> select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"
from v$session a, v$lock b, v$sqltext c
where a.username is not null and a.lockwait = b.kaddr
and c.hash_value =a.sql_hash_value
8.將之前的for update語句commit或者rollback，然後新開串連的session擁有鎖。有興趣的朋友還可以試試兩條for update的時候，關閉先執行的那個視窗，看看oracle會給出什麼樣的響應。

這一節是我在自整理v$系列視圖以來花費時間和精力最多的一個，我反覆看了document，又從網上搜尋了各種資料實際使用案例等，就是不開竅。這一節至今我也仍未有把握說盡在掌握，所以在上述文字中除了例子，我如實貼出了收集來的內容，未加任何自我理解，就是擔心萬一我的理解有誤，會對其它瀏覽本文的人造成困擾。

1 2 3 下一頁