解决一例latch:cache buffers chains小记

 

最近发现一个数据库的latch:cache buffers chains等待较多，因为近期很关注这个库，所以打算找一下问题出在哪儿。

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
*/

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

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
*/

 

--查询热点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的竞争。

--创建索引语句
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博客 ” ，链接：http://blog.itpub.net/26753337/viewspace-772982/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26753337/viewspace-772982/