关于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
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/76065/viewspace-972340/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/76065/viewspace-972340/
扫一扫
138
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
