解决一例latch:cache buffers chains小记
最近发现一个数据库的latch:cache buffers chains等待较多，因为近期很关注这个库，所以打算找一下问题出在哪儿。
select * from v$latch_misses where sleep_count>300 order by 4;
--8 cache buffers chains kcbgtcr: kslbegin excl 0 42185302 41430510 2148970 kcbgtcr: kslbegin excl
--创建索引语句
create index IND_EPGIS_CLUSTER_SYN$ on EPGIS_CLUSTER_SYN$(syn_id);
--查看执行计划
select state_id, context, groupid, clusterid, broadcast, syn_id from sde.EPGIS_CLUSTER_SYN$ a where (( groupid=2 and clusterid !=2) or ( groupid !=2 and broadcast = 1 and exists (select state_id from sde.EPGIS_SYN_PARTITIONS$ where syn_id = a.syn_id and (partition_id=-1 or (partition_id >= 5 and partition_id <=8))))) and syn_id > 1149467 order by syn_id
SELECT STATEMENT, GOAL = ALL_ROWS Cost=4 Cardinality=1 Bytes=110
FILTER
TABLE ACCESS BY INDEX ROWID Object wner=SDE Object name=EPGIS_CLUSTER_SYN$ Cost=4 Cardinality=1 Bytes=110
INDEX RANGE SCAN Object wner=SDE Object name=IND_EPGIS_CLUSTER_SYN$ Cost=3 Cardinality=1
TABLE ACCESS FULL Object wner=SDE Object name=EPGIS_SYN_PARTITIONS$ Cost=156 Cardinality=1 Bytes=10
--记录一下现在latch的统计信息，明天再看一下，以对比建完索引后latch cbc的情况是否有改善
7 cache buffers chains kcbrls: kslbegin 0 1505387 2205794 447150 kcbrls: kslbegin
8 cache buffers chains kcbgtcr: kslbegin excl 0 42478831 41719690 2165734 kcbgtcr: kslbegin excl
select sid,event,p1text,p1,p2text,p2,p3text,p3,wait_class from v$session_wait where wait_class<>'Idle'
/*
946 latch: cache buffers chains address 5.04403191383227E17 number 122 tries 1 Concurrency
982 latch: cache buffers chains address 5.04403191903495E17 number 122 tries 0 Concurrency
1003 SQL*Net message to client driver id 1413697536 #bytes 1 0 Network
*/
/*
946 latch: cache buffers chains address 5.04403191383227E17 number 122 tries 1 Concurrency
982 latch: cache buffers chains address 5.04403191903495E17 number 122 tries 0 Concurrency
1003 SQL*Net message to client driver id 1413697536 #bytes 1 0 Network
*/
select * from v$latch_misses where sleep_count>300 order by 4;
--8 cache buffers chains kcbgtcr: kslbegin excl 0 42185302 41430510 2148970 kcbgtcr: kslbegin excl
select se.sid,se.serial#,se.sql_id,se.prev_sql_id,sql_text from v$session se,v$sql sq where se.sql_id=sq.sql_id and se.sid=946;
--1 946 26136 7p5ds3m5vtg1y 89camvzd2vfu8 select state_id, context, groupid, clusterid, broadcast, syn_id from sde.EPGIS_CLUSTER_SYN$ a where (( groupid=3 and clusterid !=3) or ( groupid !=3 and broadcast = 1 and exists (select state_id from sde.EPGIS_SYN_PARTITIONS$ where syn_id = a.syn_id and (partition_id=-1 or (partition_id >= 9 and partition_id <=12))))) and syn_id > 1149467 order by syn_id
--1 946 26136 7p5ds3m5vtg1y 89camvzd2vfu8 select state_id, context, groupid, clusterid, broadcast, syn_id from sde.EPGIS_CLUSTER_SYN$ a where (( groupid=3 and clusterid !=3) or ( groupid !=3 and broadcast = 1 and exists (select state_id from sde.EPGIS_SYN_PARTITIONS$ where syn_id = a.syn_id and (partition_id=-1 or (partition_id >= 9 and partition_id <=12))))) and syn_id > 1149467 order by syn_id
select * from v$session_wait where wait_class<>'Idle'
/*
946 5722 latch: cache buffers chains address 5.04403191446476E17 07000007B9BD9418 number 122 000000000000007A tries 0 00 3875070507 4 Concurrency -1 0 WAITED SHORT TIME
957 51103 latch: cache buffers chains address 5.04403191563259E17 07000007C0B38D90 number 122 000000000000007A tries 0 00 3875070507 4 Concurrency -1 0 WAITED SHORT TIME
982 4487 latch: cache buffers chains address 5.04403191446476E17 07000007B9BD9418 number 122 000000000000007A tries 0 00 3875070507 4 Concurrency -1 0 WAITED SHORT TIME
1003 22664 SQL*Net message to client driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 2000153315 7 Network -1 0 WAITED SHORT TIME
*/
--根据P1值对应latch的地址的关系，查看相关latch的信息
select child# "cCHILD",ADDR "sADDR",GETS "sGETS",MISSES "sMISSES",SLEEPS "sSLEEPS" from v$latch_children
where name='cache buffers chains' and ADDR in ('07000007B9BD9418','07000007C0B38D90','07000007B9BD9418')
order by 5,1,2,3;
/*
19040 07000007B9BD9418 1288115 381030 88
61918 07000007C0B38D90 1217153 320667 90
*/
/*
946 5722 latch: cache buffers chains address 5.04403191446476E17 07000007B9BD9418 number 122 000000000000007A tries 0 00 3875070507 4 Concurrency -1 0 WAITED SHORT TIME
957 51103 latch: cache buffers chains address 5.04403191563259E17 07000007C0B38D90 number 122 000000000000007A tries 0 00 3875070507 4 Concurrency -1 0 WAITED SHORT TIME
982 4487 latch: cache buffers chains address 5.04403191446476E17 07000007B9BD9418 number 122 000000000000007A tries 0 00 3875070507 4 Concurrency -1 0 WAITED SHORT TIME
1003 22664 SQL*Net message to client driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 2000153315 7 Network -1 0 WAITED SHORT TIME
*/
--根据P1值对应latch的地址的关系，查看相关latch的信息
select child# "cCHILD",ADDR "sADDR",GETS "sGETS",MISSES "sMISSES",SLEEPS "sSLEEPS" from v$latch_children
where name='cache buffers chains' and ADDR in ('07000007B9BD9418','07000007C0B38D90','07000007B9BD9418')
order by 5,1,2,3;
/*
19040 07000007B9BD9418 1288115 381030 88
61918 07000007C0B38D90 1217153 320667 90
*/
--查询热点buffer出自哪些对象
select /*+ RULE */ e.owner ||'.'|| e.segment_name segment_name,e.extent_id extent#,x.dbablk-e.block_id+1 block#,x.tch,l.child#
from sys.v$latch_children l,sys.x$bh x,sys.dba_extents e
where x.hladdr in ('07000007B9BD9418','07000007C0B38D90') and e.file_id=x.file# and x.hladdr=l.addr and x.dbablk between e.block_id and e.block_id + e.blocks-1
order by x.tch desc;
/*
1 SDE.EPGIS_CLUSTER_SYN$ 6 294 13087 61918
2 SDE.EPGIS_CLUSTER_SYN$ 12 109 2191 19040
3 SDE.CONF_WORKITEM 0 45 417 19040
4 SDE.STATION_GEO_ST_SWIT 4 95 230 19040
5 SDE.EQU_SD_YW_YXGT 56 88 72 61918
6 SDE.EQU_PD_PDDTSB 82 671 71 19040
7 SDE.STATION_GEO_ST_LINK 4 408 51 61918
*/
--从中可以看到latch(07000007B9BD9418，07000007C0B38D90)所保护的地址上主要的对象是SDE.EPGIS_CLUSTER_SYN$，下面查看一下这是个什么对象：
select * from dba_segments where segment_name in ('EPGIS_CLUSTER_SYN$','SYS_LOB0000359108C00002$$','ECS_INDEX','SYS_IL0000359108C00002$$');
--该表上一共有2042MB
select sum(bytes)/1024/1024 from dba_segments where segment_name in ('EPGIS_CLUSTER_SYN$','SYS_LOB0000359108C00002$$','ECS_INDEX','SYS_IL0000359108C00002$$');
--该表上存在一个BLOB字段和一个索引，索引存在于STATE_ID字段
select * from dba_ind_columns where index_name='ECS_INDEX';
--看看与这张表相关的SQL有哪些吧
select sql_id,version_count,fetches,executions,last_active_time from v$sqlarea where sql_text
like 'select state_id, context, groupid, clusterid, broadcast, syn_id from sde.EPGIS_CLUSTER_SYN$ a where%' order by 5 desc;
1 3kq8r67skb7vr 1 221 221 2013-9-22 15:31:00
2 gj5rrrqvwm586 1 220 221 2013-9-22 15:31:00
3 0hwz296t9scs4 1 221 221 2013-9-22 15:30:54
4 7p5ds3m5vtg1y 1 219 219 2013-9-22 15:30:54
/*
select state_id, context, groupid, clusterid, broadcast, syn_id from sde.EPGIS_CLUSTER_SYN$ a where (( groupid=4 and clusterid !=4) or ( groupid !=4 and broadcast = 1 and exists (select state_id from sde.EPGIS_SYN_PARTITIONS$ where syn_id = a.syn_id and (partition_id=-1 or (partition_id >= 13 and partition_id <=19))))) and syn_id > 1149467 order by syn_id
select state_id, context, groupid, clusterid, broadcast, syn_id from sde.EPGIS_CLUSTER_SYN$ a where (( groupid=2 and clusterid !=2) or ( groupid !=2 and broadcast = 1 and exists (select state_id from sde.EPGIS_SYN_PARTITIONS$ where syn_id = a.syn_id and (partition_id=-1 or (partition_id >= 5 and partition_id <=8))))) and syn_id > 1149467 order by syn_id
select state_id, context, groupid, clusterid, broadcast, syn_id from sde.EPGIS_CLUSTER_SYN$ a where (( groupid=3 and clusterid !=3) or ( groupid !=3 and broadcast = 1 and exists (select state_id from sde.EPGIS_SYN_PARTITIONS$ where syn_id = a.syn_id and (partition_id=-1 or (partition_id >= 9 and partition_id <=12))))) and syn_id > 1149467 order by syn_id
select state_id, context, groupid, clusterid, broadcast, syn_id from sde.EPGIS_CLUSTER_SYN$ a where (( groupid=1 and clusterid !=1) or ( groupid !=1 and broadcast = 1 and exists (select state_id from sde.EPGIS_SYN_PARTITIONS$ where syn_id = a.syn_id and (partition_id=-1 or (partition_id >= 2 and partition_id <=4))))) and syn_id > 1149464 order by syn_id
*/
通过观察上述这些sql执行频率很高，并且又是同时访问还存在全表扫描现象，所以latch: cache buffers chains现象比较严重，两个进程一起访问同一个数据块，肯定会产生CBC Latch竞争，可以通过查询V$Latch_misses视图确认，建议在EPGIS_CLUSTER_SYN$表的syn_id列上建立索引以降低一致性读取次数，从而就减少了latch的竞争。
from sys.v$latch_children l,sys.x$bh x,sys.dba_extents e
where x.hladdr in ('07000007B9BD9418','07000007C0B38D90') and e.file_id=x.file# and x.hladdr=l.addr and x.dbablk between e.block_id and e.block_id + e.blocks-1
order by x.tch desc;
/*
1 SDE.EPGIS_CLUSTER_SYN$ 6 294 13087 61918
2 SDE.EPGIS_CLUSTER_SYN$ 12 109 2191 19040
3 SDE.CONF_WORKITEM 0 45 417 19040
4 SDE.STATION_GEO_ST_SWIT 4 95 230 19040
5 SDE.EQU_SD_YW_YXGT 56 88 72 61918
6 SDE.EQU_PD_PDDTSB 82 671 71 19040
7 SDE.STATION_GEO_ST_LINK 4 408 51 61918
*/
--从中可以看到latch(07000007B9BD9418，07000007C0B38D90)所保护的地址上主要的对象是SDE.EPGIS_CLUSTER_SYN$，下面查看一下这是个什么对象：
select * from dba_segments where segment_name in ('EPGIS_CLUSTER_SYN$','SYS_LOB0000359108C00002$$','ECS_INDEX','SYS_IL0000359108C00002$$');
--该表上一共有2042MB
select sum(bytes)/1024/1024 from dba_segments where segment_name in ('EPGIS_CLUSTER_SYN$','SYS_LOB0000359108C00002$$','ECS_INDEX','SYS_IL0000359108C00002$$');
--该表上存在一个BLOB字段和一个索引，索引存在于STATE_ID字段
select * from dba_ind_columns where index_name='ECS_INDEX';
--看看与这张表相关的SQL有哪些吧
select sql_id,version_count,fetches,executions,last_active_time from v$sqlarea where sql_text
like 'select state_id, context, groupid, clusterid, broadcast, syn_id from sde.EPGIS_CLUSTER_SYN$ a where%' order by 5 desc;
1 3kq8r67skb7vr 1 221 221 2013-9-22 15:31:00
2 gj5rrrqvwm586 1 220 221 2013-9-22 15:31:00
3 0hwz296t9scs4 1 221 221 2013-9-22 15:30:54
4 7p5ds3m5vtg1y 1 219 219 2013-9-22 15:30:54
/*
select state_id, context, groupid, clusterid, broadcast, syn_id from sde.EPGIS_CLUSTER_SYN$ a where (( groupid=4 and clusterid !=4) or ( groupid !=4 and broadcast = 1 and exists (select state_id from sde.EPGIS_SYN_PARTITIONS$ where syn_id = a.syn_id and (partition_id=-1 or (partition_id >= 13 and partition_id <=19))))) and syn_id > 1149467 order by syn_id
select state_id, context, groupid, clusterid, broadcast, syn_id from sde.EPGIS_CLUSTER_SYN$ a where (( groupid=2 and clusterid !=2) or ( groupid !=2 and broadcast = 1 and exists (select state_id from sde.EPGIS_SYN_PARTITIONS$ where syn_id = a.syn_id and (partition_id=-1 or (partition_id >= 5 and partition_id <=8))))) and syn_id > 1149467 order by syn_id
select state_id, context, groupid, clusterid, broadcast, syn_id from sde.EPGIS_CLUSTER_SYN$ a where (( groupid=3 and clusterid !=3) or ( groupid !=3 and broadcast = 1 and exists (select state_id from sde.EPGIS_SYN_PARTITIONS$ where syn_id = a.syn_id and (partition_id=-1 or (partition_id >= 9 and partition_id <=12))))) and syn_id > 1149467 order by syn_id
select state_id, context, groupid, clusterid, broadcast, syn_id from sde.EPGIS_CLUSTER_SYN$ a where (( groupid=1 and clusterid !=1) or ( groupid !=1 and broadcast = 1 and exists (select state_id from sde.EPGIS_SYN_PARTITIONS$ where syn_id = a.syn_id and (partition_id=-1 or (partition_id >= 2 and partition_id <=4))))) and syn_id > 1149464 order by syn_id
*/
通过观察上述这些sql执行频率很高，并且又是同时访问还存在全表扫描现象，所以latch: cache buffers chains现象比较严重，两个进程一起访问同一个数据块，肯定会产生CBC Latch竞争，可以通过查询V$Latch_misses视图确认，建议在EPGIS_CLUSTER_SYN$表的syn_id列上建立索引以降低一致性读取次数，从而就减少了latch的竞争。
--创建索引语句
create index IND_EPGIS_CLUSTER_SYN$ on EPGIS_CLUSTER_SYN$(syn_id);
--查看执行计划
select state_id, context, groupid, clusterid, broadcast, syn_id from sde.EPGIS_CLUSTER_SYN$ a where (( groupid=2 and clusterid !=2) or ( groupid !=2 and broadcast = 1 and exists (select state_id from sde.EPGIS_SYN_PARTITIONS$ where syn_id = a.syn_id and (partition_id=-1 or (partition_id >= 5 and partition_id <=8))))) and syn_id > 1149467 order by syn_id
SELECT STATEMENT, GOAL = ALL_ROWS Cost=4 Cardinality=1 Bytes=110
FILTER
TABLE ACCESS BY INDEX ROWID Object wner=SDE Object name=EPGIS_CLUSTER_SYN$ Cost=4 Cardinality=1 Bytes=110
INDEX RANGE SCAN Object wner=SDE Object name=IND_EPGIS_CLUSTER_SYN$ Cost=3 Cardinality=1
TABLE ACCESS FULL Object wner=SDE Object name=EPGIS_SYN_PARTITIONS$ Cost=156 Cardinality=1 Bytes=10
--记录一下现在latch的统计信息，明天再看一下，以对比建完索引后latch cbc的情况是否有改善
7 cache buffers chains kcbrls: kslbegin 0 1505387 2205794 447150 kcbrls: kslbegin
8 cache buffers chains kcbgtcr: kslbegin excl 0 42478831 41719690 2165734 kcbgtcr: kslbegin excl
--经过观察已经没有latch:cache buffers chains的等待了，该问题到此告一段落。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26753337/viewspace-772982/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- Oracle Job 迁移小记
- 如何以最快方式干净的关闭数据库?
- Oracle用户密码中含有特殊字符的登录办法
- Oracel 12c Alert日志中的Creating new log segment
- 测试RMAN的backup archivelog all delete input会不会删除还未传输到备库的归档日志
- 关于数据文件autoextend on的一点记录
- 12c 新特性之大表自动缓存 Automatic Big Table Caching
- 12c 新特性之 Limiting the Size of the Program Global Area
- 11gR2 RAC+DG打PSU补丁的简要步骤
- Oracle Data Redaction实验记录
