oracle9204下这个cache buffers chains latch是怎么产生的？

显示全部楼层 · 2007-9-26 18:42:10

环境hpux 11i oracle 9204
我发现cache buffers chains latch涉及的block是index上的，而且根据sql应该只是read而已，不是说9i里面对cache buffers chains的访问不再产生latch了吗？

千问 · 2007-9-26 18:42:10

具体情况如下：
发现系统上10几个session很慢（有几个报了ora-01555才知道的），v$session看到这些session都在做同样的sql:
[PHP]
DELETE FROM oos_cache
WHERE NOT oosc_oos_key IN (SELECT sat_oos_key

FROM service_applies_to, svc_cache

WHERE sat_source_obligation_id =

sc_source_obligation_id

AND es_fill.calc_active(sat_agg_svc_status,

sat_agg_start_date, sat_agg_end_date,

:b1, sat_agg_delivery_block) = 'Y'

UNION

SELECT oos_key

FROM service_applies_to_group,

object_of_service, svc_cache, oos_cache

WHERE satg_source_obligation_id =

sc_source_obligation_id

AND oos_key = oosc_oos_key

AND oos_group_id = satg_oos_group_id

AND es_fill.calc_active(

satg_agg_svc_status,

satg_agg_start_date, satg_agg_end_date,

:b1, satg_agg_delivery_block) = 'Y')
[/PHP]
做了几个的trace，发现主要在等latch free，根据p2=98知道是
cache buffers chains
[PHP]
WAIT #9: nam='resmgr:waiting in check' ela= 6944 p1=0 p2=0 p3=0
WAIT #9: nam='latch free' ela= 83 p1=-4611686013028647712 p2=98 p3=0
WAIT #9: nam='latch free' ela= 6689 p1=-4611686013028647712 p2=98 p3=0
WAIT #9: nam='latch free' ela= 3612 p1=-4611686013028625080 p2=98 p3=0
WAIT #9: nam='latch free' ela= 56 p1=-4611686013028625080 p2=98 p3=0
WAIT #9: nam='latch free' ela= 172 p1=-4611686013028647712 p2=98 p3=0
WAIT #9: nam='latch free' ela= 67 p1=-4611686013028625080 p2=98 p3=0
WAIT #9: nam='latch free' ela= 66 p1=-4611686013028625080 p2=98 p3=0
[/PHP]
根据p1定位具体buffer chain和下面的block是：
[PHP]
SQL> SQL> select /*+ RULE */
2e.owner ||'.'|| e.segment_namesegment_name,
e.extent_idextent#,
x.dbablk - e.block_id + 1block#,
x.tch,
l.child#
from
sys.v$latch_childrenl,
sys.x$bhx,
sys.dba_extentse
where
x.hladdr= 'C000000141CA9548' and
e.file_id = x.file# and
3456789 10 11 12 13 14x.hladdr = l.addr and
x.dbablk between e.block_id and e.block_id + e.blocks -1
order by x.tch desc ;
15 16
SEGMENT_NAME
EXTENT# BLOCK#TCH CHILD#
----------------------------------- ---------- ---------- ---------- ----------
ENTADMIN.PK_SATG
4241 6769 2261
ENTADMIN.PK_SATG
4241 3627 2261
ENTADMIN.IDX_OOS_PN_SEARCH
4 2099 26 2261
ENTADMIN.IDX_SAT_OOS_JOIN
61499 18 2261
ENTADMIN.SERVICE_APPLIES_TO
716311 13 2261
ENTADMIN.PK_SMOD
1203503 12 2261
ENTADMIN.IDX_SATG_OOSG_JOIN
1118
6 2261
ENTADMIN.PK_OOS
103518
6 2261
ENTADMIN.PK_SVC
93918
6 2261
ENTADMIN.SERVICE
18 7148
5 2261
ENTADMIN.PK_OOS
130918
5 2261
SEGMENT_NAME
EXTENT# BLOCK#TCH CHILD#
----------------------------------- ---------- ---------- ---------- ----------
ENTADMIN.IDX_OOS_UNIFIED_PN_SEARCH 9 1718
5 2261
ENTADMIN.OBJECT_OF_SERVICE
11 6767
4 2261
ENTADMIN.PK_CNTC
0275
4 2261
ENTADMIN.PK_OOSU
31222
4 2261
ENTADMIN.SERVICE
212596
4 2261
ENTADMIN.IDX_OOS_PN_SEARCH
2 6214
4 2261
ENTADMIN.SVC_MODIFIER
6415340
4 2261
ENTADMIN.IDX_OOS_UNIFIED_PN_SEARCH
1413644
3 2261
ENTADMIN.IDX_OOS_UNIFIED_PN_SEARCH 210291
2 2261
ENTADMIN.IDX_OOS_PN_SEARCH
12 1356
2 2261
ENTADMIN.IDX_OOS_RED_GRP_SN_SEARCH 8 9929
2 2261
SEGMENT_NAME
EXTENT# BLOCK#TCH CHILD#
----------------------------------- ---------- ---------- ---------- ----------
ENTADMIN.IDX_SAT_OOS_JOIN
184865
2 2261
ENTADMIN.SERVICE_APPLIES_TO
4 5604
2 2261
ENTADMIN.IDX_OOS_PN_SEARCH
2 2118
2 2261
SYSTEM.LOGMNR_TABPART$
0
1
1 2261
SYSTEM.LOGMNR_TABPART$
0
1
1 2261
SYSTEM.LOGMNR_TABPART$
0
1
1 2261
SYSTEM.LOGMNR_TABPART$
0
1
1 2261
SYSTEM.LOGMNR_TABPART$
0
1
1 2261
SYS._SYSSMU29$
1
1
1 2261
ENTADMIN.IDX_SAT_OOS_JOIN
164865
1 2261
ENTADMIN.SVC_MODIFIER
10 1508
1 2261
SEGMENT_NAME
EXTENT# BLOCK#TCH CHILD#
----------------------------------- ---------- ---------- ---------- ----------
ENTADMIN.PK_CNT
2346
1 2261
SYS._SYSSMU51$
3 33
1 2261
SYS._SYSSMU46$
7 33
1 2261
SYS._SYSSMU25$
7 36
1 2261
ENTADMIN.DLV_MODIFIER
601918
1 2261
ENTADMIN.PK_OHCR
321537
1 2261
ENTADMIN.SERVICE
1710863
0 2261
ENTADMIN.OBJECT_OF_SERVICE
4110501
0 2261
ENTADMIN.SVC_MODIFIER
4811244
0 2261
ENTADMIN.IDX_SAT_OOS_JOIN
48380
0 2261
ENTADMIN.IDX_OHAR_CUST_ID
86761
0 2261
SEGMENT_NAME
EXTENT# BLOCK#TCH CHILD#
----------------------------------- ---------- ---------- ---------- ----------
ENTADMIN.IDX_OHCR_PERSON_ID
157118
0 2261
ENTADMIN.PK_OHDR
42137
0 2261
ENTADMIN.DLV_MODIFIER
285137
0 2261
ENTADMIN.PK_OHDR
1346
0 2261
ENTADMIN.ADDRESS
135708
0 2261
ENTADMIN.PK_OOSU
87989
0 2261
ENTADMIN.PK_CNT
1708
0 2261
ENTADMIN.PK_OHDR
0989
0 2261
ENTADMIN.PK_OHDR
48989
0 2261
ENTADMIN.PK_CPD
75989
0 2261
ENTADMIN.IDX_ADR_UPD_ROW_SEARCH
51327
0 2261
SEGMENT_NAME
EXTENT# BLOCK#TCH CHILD#
----------------------------------- ---------- ---------- ---------- ----------
SYS._SYSSMU52$
6 39
0 2261
ENTADMIN.PK_OHDR
29241
0 2261
ENTADMIN.OBLIGATION_HEADER
187503
0 2261
ENTADMIN.PK_OHCR
340503
0 2261
ENTADMIN.IDX_SAT_OOS_JOIN
64865
0 2261
ENTADMIN.IDX_ADR_UPD_ROW_SEARCH
2865
0 2261
ENTADMIN.SERVICE_APPLIES_TO
16 1508
0 2261
ENTADMIN.PK_OHDR
15103
0 2261
ENTADMIN.PK_OHDR
51103
0 2261
ENTADMIN.PK_OHDR
38365
0 2261
ENTADMIN.PK_OHDR
50365
0 2261
SEGMENT_NAME
EXTENT# BLOCK#TCH CHILD#
----------------------------------- ---------- ---------- ---------- ----------
ENTADMIN.PK_OOSU
64346
0 2261
ENTADMIN.PK_OOSU
76346
0 2261
ENTADMIN.PK_OOS
157346
0 2261
ENTADMIN.IDX_OOS_PN_SEARCH
10 6214
0 2261
ENTADMIN.IDX_OOS_PN_SEARCH
1414406
0 2261
ENTADMIN.IDX_OOS_PN_SEARCH
9 9929
0 2261
ENTADMIN.IDX_OOS_UNIFIED_PN_SEARCH 613644
0 2261
ENTADMIN.IDX_OOS_RED_GRP_SN_SEARCH 2 9910
0 2261
ENTADMIN.IDX_OOS_GROUP_SEARCH
1714006
0 2261
ENTADMIN.PK_ADRC
0275
0 2261
ENTADMIN.PK_OHDR
11275
0 2261
SEGMENT_NAME
EXTENT# BLOCK#TCH CHILD#
----------------------------------- ---------- ---------- ---------- ----------
ENTADMIN.PK_ADRC
10537
0 2261
ENTADMIN.PK_OOS
177537
0 2261
ENTADMIN.PK_OHDR
9537
0 2261
ENTADMIN.PK_OHDR
8899
0 2261
ENTADMIN.PK_OHDR
44156
0 2261
ENTADMIN.PK_OHDR
56156
0 2261
ENTADMIN.PK_OHAR
67518
0 2261
ENTADMIN.PK_OHAR
79518
0 2261
ENTADMIN.PK_OHDR
18137
0 2261
ENTADMIN.PK_OHDR
54137
0 2261
ENTADMIN.IDX_SAT_OOS_JOIN
140118
0 2261
SEGMENT_NAME
EXTENT# BLOCK#TCH CHILD#
----------------------------------- ---------- ---------- ---------- ----------
ENTADMIN.IDX_OHCR_PERSON_ID
165380
0 2261
ENTADMIN.SERVICE_APPLIES_TO
18 3052
0 2261
ENTADMIN.SERVICE_APPLIES_TO
3011244
0 2261
ENTADMIN.DELIVERABLE
2414597
0 2261
[/PHP]

查了几个trace，发现tch比较高的都在两个索引上：
IDX_SAT_OOS_JOIN or PK_SATG

千问 · 2007-9-26 18:42:10

我觉得引起争用的应该是ENTADMIN.PK_SATG的block，但这个sql对他来说应该是只读的，不懂。
另外oos_cache是一张临时表，临时表本身都不在SGA里应该不会产生这个latch哈。。。。。。

千问 · 2007-9-26 18:42:10

谁与你说的9i里面对cache buffers chains的访问不再产生latch了

千问 · 2007-9-26 18:42:10

执行计划中有NESTED LOOPS很容易出现latch free

千问 · 2007-9-26 18:42:10

最初由 oracledba 发布
[B]谁与你说的9i里面对cache buffers chains的访问不再产生latch了 [/B]
是我表达的有问题，不是不产生，是可以共享。
eygle的书里说：
从oracle9i开始，对于cache buffer chain的只读访问，其latch可以被共享。

hotsos上的一篇文章也说过：
Throughout all Oracle8 releases, only one Oracle process can hold a cache buffers chains latch at a time. If a process
attempts to acquire a latch that is not available (because it is currently held by another process), then the requesting
process must wait until the latch becomes available. Thus, by holding a latch, a writer or even a reader of a cache
buffers chain will block all other prospective readers and writers of every chain protected by that latch.5 We understand
that in release 9 a latch holder who only reads a cache buffers chain can share access to its latch with other
readers. This kernel design optimization should provide relief from cache buffers chains latch contention induced by
concurrent readers. However, a writer holding a latch will still block all other prospective readers and writers of
chains protected by that latch. And, of course, readers will continue to block writers.

不过我监控过程中没有看到可能修改这个index的进程，连上来的都是readonly的user

千问 · 2007-9-26 18:42:10

最初由 flytooz 发布
[B]执行计划中有NESTED LOOPS很容易出现latch free [/B]
确实是有nested loops，但我的sql对index是只读的

千问 · 2007-9-26 18:42:10

plan

千问 · 2007-9-26 18:42:10

关注一下

千问 · 2007-9-26 18:42:10

最初由 fusnow 发布
[B]
是我表达的有问题，不是不产生，是可以共享。
eygle的书里说：
从oracle9i开始，对于cache buffer chain的只读访问，其latch可以被共享。

hotsos上的一篇文章也说过：
Throughout all Oracle8 releases, only one Oracle process can hold a cache buffers chains latch at a time. If a process
attempts to acquire a latch that is not available (because it is currently held by another process), then the requesting
process must wait until the latch becomes available. Thus, by holding a latch, a writer or even a reader of a cache
buffers chain will block all other prospective readers and writers of every chain protected by that latch.5 We understand
that in release 9 a latch holder who only reads a cache buffers chain can share access to its latch with other
readers. This kernel design optimization should provide relief from cache buffers chains latch contention induced by
concurrent readers. However, a writer holding a latch will still block all other prospective readers and writers of
chains protected by that latch. And, of course, readers will continue to block writers.

不过我监控过程中没有看到可能修改这个index的进程，连上来的都是readonly的user [/B]
所有的这些都没有办法证实，我确实怀疑这种说法，原因简单，每次你的操作尽管是读，但是代表你的sp是在chain上做的是pin！这不大可能做到最大程度共享，不要太相信这种所谓只读共享latch，如果真这样9以后cbc就不会这样突出了