Enqueue events part two

可以被lock manager並行鎖的enqueue resources的最大數量由ENQUEUE_RESOURCES初始化引數控制.該引數預設值通常來講已經足夠,如果在應用程式中使用並行DML操作,可以考慮增加ENQUEUE_RESOURCES的值.[@more@]

關於ENQUEUE_RESOURCES [10..65535]
At database startup time, Oracle allocates the number of enqueues specified by the ENQUEUE_RESOURCES parameter. The default value of ENQUEUE_RESOURCES is derived from the SESSIONS parameter and is usually adequate, as long as its value is greater than DML_LOCKS + 20. For three or fewer sessions, the default value is the number of database files + 20. For 4 to 10 sessions, the default value is the number of database files + ((SESSIONS - 3) * 5) + 20. For more than 10 sessions, it is the number of database files + ((SESSIONS - 10) * 2) + 55.
If you explicitly set ENQUEUE_RESOURCES to a value higher than DML_LOCKS + 20, then Oracle uses the value you provide. Oracle will automatically allocate additional enqueues from the shared pool as needed if the number specified by ENQUEUE_RESOURCES is exceeded. You can check resource usage by querying V$RESOURCE_LIMIT.
不能獲取an enqueue resource 的程式會報ORA-00052: "maximum number of enqueue resources exceeded" error.

V$RESOURCE_LIMIT檢視提供系統資源使用的資訊.透過這個檢視可以監控很多資料庫資源(resources, locks, or processes)的消耗情況.
注意一下這個檢視的幾個欄位:
MAX_UTILIZATION 表示例項啟動後該類資源的最大消耗值
以下監控與enqueue有關的資源消耗情況:

col name for a18
col limit_usage for a15
select RESOURCE_NAME name,
CURRENT_UTILIZATION cur_usage,
MAX_UTILIZATION max_usage,
LIMIT_VALUE limit_usage,
INITIAL_ALLOCATION init_allo
from v$resource_limit
where resource_name in ('enqueue_resources','enqueue_locks',
'dml_locks','processes','sessions')
/
NAME CUR_USAGE MAX_USAGE LIMIT_USAGE INIT_ALLO
------------------ ---------- ---------- --------------- -----------
processes 19 24 90 90
sessions 23 30 104 104
enqueue_locks 13 24 1480 1480
enqueue_resources 13 13 UNLIMITED 676
dml_locks 0 7 UNLIMITED 456

What Is an Enqueue Lock?

An enqueue lock 是lock本身.Oracle用一個單獨的陣列管理.可以透過X$KSQEQ (kernel service enqueue object) 或者 V$ENQUEUE_LOCK 試圖察看.隱含引數_ENQUEUE_LOCKS影響這個陣列的大小.

Oracle使用不同的結構管理TX and TM 佇列.
X$KTCXB (kernel transaction control transaction object—the base view for V$TRANSACTION_ENQUEUE) and X$KTADM (kernel transaction access definition dml lock). TRANSACTIONS and DML_LOCKS 初始化引數會決定管理TX and TM 佇列的結構大小.
當然透過查詢V$LOCK可以獲得所有資料庫locks的資訊.也是我們診斷enqueue爭用的一個重要檢視.

Enqueue Architecture

在資料庫內部,enqueue architecture 和 sga中cache buffers architecture十分近似. (讀過eygle <>這本書的朋友可以參照學習enqueue architecture)
enqueue architecture 的最主要元件由enqueue hash chains latches, enqueue hash table, enqueue hash chains, and enqueue resources組成.
它們之間的數量關係:
enqueue hash chains latch (1:m) a hash bucket(1:1) a enqueue hash chain .

子enqueue hash chains latches 保護enqueue hash table and hash chains.預設狀態下enqueue hash chains latches的個數等於CPU_COUNT,這個數量可以透過隱含引數_ENQUEUE_HASH_CHAIN_LATCHES調整.

根據resource type and identifiers v$resource(TY,ID1,ID2) Enqueue resources 被hash到enqueue hash table 並且 被置於相應enqueue hash chains.要使用某個enqueue resource必須獲取相應enqueue hash chain. enqueue hash table 的大小來源於SESSIONS初始化引數,也可以透過設定_ENQUEUE_HASH設定.
If you ever need to increase the ENQUEUE_RESOURCES parameter significantly from its default value, you might want to keep an eye on the sleep rate of the enqueue hash chains latches. This is because the enqueue hash table length will remain the same because it is derived from SESSIONS, not from ENQUEUE_RESOURCES. The combination of a high demand for enqueue resources and a small enqueue hash table will result in a higher hash collision rate and potentially lengthy hash chains. This problem manifests itself as latch contentions for the enqueue hash chains latches. In this case, you need to increase the _ENQUEUE_HASH.
enqueue hash table length = ((SESSIONS – 10) * 2) + 55
透過dump enqueue 結構進一步學習:
alter session set events ’immediate trace name enqueues level 3’;

Decoding Enqueue Type and Mode

Oracle 透過對P1列的解碼,我們可以得知enqueue type 和 mode.

select sid, event, p1, p1raw,
chr(bitand(P1,-16777216)/16777215)||chr(bitand(P1,16711680)/65535) type,
mod(P1, 16) "MODE"
from v$session_wait
where event ='enqueue'
/

另一種方法.透過v$session_wait(p1raw)列也可以.
The values from the above example are from a 64-bit Oracle Database. You can ignore the leading zeros and focus on the last 4 bytes (that is, the last eight numbers). The high order 2 bytes give the enqueue type. Using 54580006 as an example, the 2 high order bytes are 0x5458Hex. Now, 54Hex is decimal 84 and 58Hex is decimal 88, so the enqueue type can be discovered as follows: (Appendix B has a complete list of enqueue types.)
select chr(84) || chr(88) from dual;CH--TX

Oracle 10g後enqueue wait event name 可以透過v$session_wait(event)直接獲得.

Processes request enqueue locks in one of these six modes: Null (N), Row Share (RS), Row Exclusive (RX), Share (S), Share Row Exclusive (SRX), or Exclusive (X)
這部分可參見Oracle相關文件.

Common Causes, Diagnosis, and Actions

由於enqueue type種類繁多,產生an enqueue wait event的原因也不同.我們可以根據多個session正在競爭的enqueue的type和mode找到不同的解決方法.
對於每一種type的enqueue,Oracle內部透過X$KSQST 維護一條相應的記錄.在Oracle9i中,透過V$ENQUEUE_STAT檢視, 可以具體獲得不同type enqueue的請求統計資訊(例項啟動後的統計值).

-- Oracle 7.1.6 to 8.1.7
select inst_id,
ksqsttyp inst_lock,
ksqstget inst_Gets,
ksqstwat inst_Waits
from x$ksqst
where ksqstwat > 0
order by inst_id,ksqstwat
/

-- Oracle9i Database and above
select *
from v$enqueue_stat
where cum_wait_time > 0
order by inst_id, cum_wait_time
/

下面就最常見的一些enqueue等待事件進行討論.

貼itpub上鳥..

http://www.itpub.net/860346.html