解決一例latch:cache buffers chains小記

531968912發表於2016-07-04
AI
 
最近發現一個資料庫的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/25462274/viewspace-2121463/，如需轉載，請註明出處，否則將追究法律責任。

相關文章