概述
等待事件gc cr/current grant 2-way是ORACLE RAC Cache Fusion过程中常见的等待事件，典型的情形，当请求一个数据块，且该数据块不在其它实例的BUFFER CACHE中时，那么向管理该数据块的MASTER节点请求一个授权，然后从磁盘读取该数据块。如果一个对象频繁在一个节点上访问，但该对象的MASTER节点不是本节点，将会产生大gc cr/current grant 2-way等待。有没有办法调整该MASTER节点呢，DRM就是自动做这个事的。但一般生产系统中为了系统的稳定会禁用DRM。但如果系统中大量的gc cr/current grant 2-way等待，排除网络或BUG等原因，这时可以尝试人工做 Remastering操作，注意该操作过程在繁忙的系统会产生性能问题，应找个系统闲时的维护窗口做。下面做个测试演示这一过程。
测试准备
- 创建测试表,INSERT测试数据
create table dbmt.test_gc(id number,c varchar2(1000)) tablespace users;
insert into dbmt.test_gc select 0+rownum,rpad('d',200,'x') from dual connect by rownum<900000;
create index dbmt.idx_test_gc on dbmt.test_gc(id) tablespace users;
select max(id) from dbmt.test_gc;
- 查询MASTER节点
select /*+full(t) */count(*) from test_gc t;
---查看表DATA_OBJECT_ID
SQL> @o dbmt.test_gc
owner object_name object_type status OID D_OID CREATED LAST_DDL_
------------------------- ------------------------------ ------------------ --------- ---------- ---------- --------- ---------
DBMT TEST_GC TABLE VALID 87781 87783 06-JAN-20 06-JAN-20
---KJBRPKEY 条件值为DATA_OBJECT_ID
select KJBRMASTER,count(*) from x$kjbr where KJBRPKEY=87783 group by KJBRMASTER;
SQL> select KJBRMASTER,count(*) from x$kjbr where KJBRPKEY=87783 group by KJBRMASTER;
KJBRMASTER COUNT(*)
---------- ----------
0 8301
- 测试SQL
在非MASTER节点并发执行以下SQL，为了能测试出效果，可以把DB_CACHE调小。
select *from dbmt.test_gc where id=abs(mod(dbms_random.random(),899999));
- FLUSH BUFFER_CACHE
测试
- 在非MASTER节点，并发查询可以看到TOP EVENT中产生 gc cr grant 2-way
SQL> @ashtop event "username='DBMT'" sysdate-1/1440 sysdate
Total
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN COUNT(DISTINCTSQL_EXEC_START||':'||SQL_EXEC_ID)
--------- ------- ------- ---------------------------------------- ------------------- ------------------- -----------------------------------------------
183 3.1 35% | 2020-01-06 10:47:34 2020-01-06 10:48:33 154
150 2.5 29% | gc cr grant 2-way 2020-01-06 10:47:34 2020-01-06 10:48:32 150
133 2.2 26% | db file sequential read 2020-01-06 10:47:34 2020-01-06 10:48:33 133
19 .3 4% | library cache: mutex X 2020-01-06 10:47:36 2020-01-06 10:48:21 15
13 .2 3% | latch free 2020-01-06 10:48:19 2020-01-06 10:48:19 1
6 .1 1% | gc cr grant congested 2020-01-06 10:47:35 2020-01-06 10:48:15 6
5 .1 1% | gc cr request 2020-01-06 10:48:33 2020-01-06 10:48:33 5
4 .1 1% | latch: ges resource hash list 2020-01-06 10:48:14 2020-01-06 10:48:27 2
2 .0 0% | SQL*Net message to client 2020-01-06 10:47:44 2020-01-06 10:47:49 2
2 .0 0% | cursor: pin S 2020-01-06 10:48:09 2020-01-06 10:48:09 1
10 rows selected.
- 人工remaster
SQL> oradebug setmypid
Statement processed.
SQL>
SQL> oradebug lkdebug -m
Usage:lkdebug [options]
-l [r|p] <enqueue pointer> Enqueue Object
-r <resource pointer> Resource Object
-b <gcs shadow pointer> GCS shadow Object
-p <oracle process number> client process number
-P <process pointer> Process Object
-O <i1> <i2> <types> Oracle Format resname
-a <res/lock/proc> all <res/lock/proc> pointers
-A <res/lock/proc> all <res/lock/proc> contexts
-a <res> [<type>] all <res> pointers by an optional type
-a convlock all converting enqueue (pointers)
-A convlock all converting enqueue contexts
-a convres all res ptr with converting enqueues
-A convres all res contexts with converting enqueues
-a name list all resource names
-a hashcount list all resource hash bucket counts
-t Traffic controller info
-s summary of all enqueue types
-k GES SGA summary info
-m pkey <objectno> request for setting affinity on objectno at current instance
-m pkey_readmostly <objectno> request for setting readmostly property on objectno
-m dpkey <objectno> request for dissolving affinity of this object at current instance
-m dpkey_readmostly <objectno> request for dissolving the readmostly property of objectno
-m dpkey_drop <objectno> request for dissolving the affinity on objectno due to dropping it
-m rora <options> request for pseudo reconfiguration with reason rora
-m reconfig [disrm | lkdebug] request for pseudo reconfiguration
-f <objectno> dump affinity info about objectno
-m dpkey <objectno> request for dissolving remastering of this object at current instance
-m banlist <objectno> add object to DRM banlist
-m dbanlist <objectno> delete object from DRM banlist
-m qbanlist query objects in DRM balist
-I Close and reopen IPC context
-e Lower all LMS priorities
-E Raise all LMS priorities
SQL>
---pkey 后面跟DATA_OBJECT_ID
SQL> oradebug lkdebug -m pkey 87783
Statement processed.
SQL>
过程中可以看到remaster过程导致一些性能问题
SQL> select ses.username, ses.sid, CASE WHEN ses.state != 'WAITING' THEN 'On CPU / runqueue' ELSE event end as event, ses.machine, ses.program, ses.status, ses.last_call_et, ses.sql_id,wait_time||':'||SECONDS_IN_WAIT wai_secinwait ,
seq# ,ROW_WAIT_OBJ# ,substr(sql.sql_text,1,30) sqltext,blocking_instance||':'||blocking_session bs,sql_child_number ch# ,osuser
from v$session ses left join v$sql sql on ses.sql_hash_value = sql.hash_value and
ses.sql_child_number=sql.child_number where ses.type = 'USER' and ses.status<>'INACTIVE' -- and sql_text like 'select t.subsid,s.servnumber,t%'
2 3 4 5 order by SECONDS_IN_WAIT,last_call_et,4;
USERNAME SID EVENT MACHINE PROGRAM STATUS LAST_CALL_ET SQL_ID WAI_SECINW SEQ# ROW_WAIT_OBJ# SQLTEXT BS CH# OSUSER
---------- ---------- -------------------- ---------- -------------- -------- ------------ --------------- ---------- ---------- ------------- ------------------------------ ---------- ---- ----------
DBMT 29 latch: ges resource ABC JDBC Thin Clie ACTIVE 0 0:0 10954 87781 : Think
DBMT 140 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 0:0 10854 87781 : Think
DBMT 30 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 0:0 11045 87781 : Think
DBMT 32 latch: ges resource ABC JDBC Thin Clie ACTIVE 0 0:0 10838 87781 : Think
DBMT 34 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 0:0 10962 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 35 latch: ges resource ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 0:0 11058 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 37 db file sequential r ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 0:0 10899 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 38 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 0:0 11018 87781 : Think
DBMT 39 latch: ges resource ABC JDBC Thin Clie ACTIVE 0 0:0 10795 87781 : Think
DBMT 40 latch: ges resource ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 0:0 10999 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 42 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 0:0 10941 87781 : Think
DBMT 45 On CPU / runqueue ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 1:0 11054 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 49 db file sequential r ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 0:0 10947 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 50 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 0:0 10832 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 51 latch: ges resource ABC JDBC Thin Clie ACTIVE 0 0:0 10955 87781 : Think
DBMT 53 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 0:0 10786 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 55 latch: ges resource ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 0:0 10937 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 57 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 0:0 10835 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 112 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 0:0 10813 87781 : Think
DBMT 119 db file sequential r ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 0:0 10940 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 121 latch: ges resource ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 0:0 11016 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 122 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 0:0 11064 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 124 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 0:0 11216 87781 : Think
DBMT 125 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 0:0 10971 87781 : Think
DBMT 127 latch: ges resource ABC JDBC Thin Clie ACTIVE 0 0:0 10940 87781 : Think
DBMT 129 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 0:0 10886 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 130 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 0:0 10989 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 132 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 0:0 10937 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 134 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 0:0 10896 87781 select /*+index(t)*/*from dbm : 1 Think
select sysdate current_time from dual;
DBMT 135 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 0:0 10984 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 136 On CPU / runqueue ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 1:0 10863 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 138 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 888wkughx3qxa 0:0 10944 87781 select /*+index(t)*/*from dbm : 1 Think
DBMT 139 library cache: mutex ABC JDBC Thin Clie ACTIVE 0 0:0 10902 87781 : Think
SYS 16 On CPU / runqueue rac2 sqlplus@rac2 ( ACTIVE 0 5ykq87vzqkghp -1:0 3668 16 select ses.username, ses. : 0 oracle
查看REMASTER结果
SQL> select * from V$GCSPFMASTER_INFO;
FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT
---------- -------------- ----------- -------------- --------------- ------------
0 87746 Affinity 1 32767 1
- 完成操作后，再看TOP EVENT中gc cr grant 2-way 减少到几乎没有了，可能会有INDEX BLOCK或其它块产生一些gc cr grant 2-way
SQL> @ashtop event "username='DBMT'" sysdate-1/1440 sysdate
Total
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN COUNT(DISTINCTSQL_EXEC_START||':'||SQL_EXEC_ID)
--------- ------- ------- ---------------------------------------- ------------------- ------------------- -----------------------------------------------
118 2.0 43% | db file sequential read 2020-01-06 10:52:20 2020-01-06 10:53:18 118
110 1.8 40% | 2020-01-06 10:52:19 2020-01-06 10:53:18 87
29 .5 11% | latch: ges resource hash list 2020-01-06 10:52:20 2020-01-06 10:52:58 29
14 .2 5% | library cache: mutex X 2020-01-06 10:52:20 2020-01-06 10:53:16 12
2 .0 1% | gc cr grant 2-way 2020-01-06 10:52:35 2020-01-06 10:52:35 2
1 .0 0% | SQL*Net message to client 2020-01-06 10:52:25 2020-01-06 10:52:25 1
6 rows selected.
最后修改时间：2020-01-06 20:54:28
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
