[20160104]enq RC - Result Cache Contention.txt
--今天检查awr报表，无意间发现enq RC - Result Cache Contention排在靠前的位置。我们服务器很强劲，出现这个给仔细检查。
1.环境：
SYSTEM@AAA.AAA.AA.AAA:1521/XXXX> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.分析：
SELECT sql_id, COUNT (*)
FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE event = 'enq: RC - Result Cache: Contention'
GROUP BY sql_id;
SQL_ID COUNT(*)
------------- ----------
5wh51638vh3jc 1
futfjyqv0c6b8 28
--很明显问题集中在sql_id='futfjyqv0c6b8'.
SYSTEM@AAA.AAA.AA.AAA:1521/XXXX> @ &r/sqlid futfjyqv0c6b8
SQL_ID SQLTEXT
------------- --------------------------------------------------------------------------------------------------------------------------------------------------------
futfjyqv0c6b8 select emr_zlsqmx.mxid,emr_zlsqmx.sqdh,emr_zlsqmx.zlxmid,emr_zlsqmx.xmmc,emr_zlsqmx.xmlb,emr_zlsqmx.sypc,emr_zlsqmx.sysl,emr_zlsqmx.plsx,emr_zlsqmx.ysy
zbh,emr_zlsqmx.yszt from emr_zlsqmx
--可以发现很简单访问就是表emr_zlsqmx。而且这张表是我节前修改result cache模式的。
SYSTEM@AAA.AAA.AA.AAA:1521/XXXX> select segment_name,SEGMENT_TYPE,SEGMENT_SUBTYPE,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name='EMR_ZLSQMX';
SEGMENT_NAME SEGMENT_TYPE SEGMENT_SU BYTES BLOCKS EXTENTS
-------------------- ------------------ ---------- ---------- ---------- ----------
EMR_ZLSQMX TABLE ASSM 17825792 2176 32
--大小17825792/1024/1024=17M。
SYSTEM@AAA.AAA.AA.AAA:1521/XXXX> @ &r/ddl BBBBBB_BBB.EMR_ZLSQMX
C100
------------------------------------------------------------------------------
CREATE TABLE "BBBBBB_BBB"."EMR_ZLSQMX"
( "MXID" NUMBER(18,0) NOT NULL ENABLE,
"SQDH" NUMBER(18,0) NOT NULL ENABLE,
"ZLXMID" NUMBER(8,0) NOT NULL ENABLE,
"XMMC" VARCHAR2(255) NOT NULL ENABLE,
"XMLB" NUMBER(1,0) NOT NULL ENABLE,
"SYPC" VARCHAR2(6),
"SYSL" NUMBER(5,2),
"PLSX" NUMBER(3,0),
"YSYZBH" NUMBER(18,0),
"YSZT" VARCHAR2(120),
CONSTRAINT "PK_EMR_ZLSQMX" PRIMARY KEY ("MXID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "BBBBBB_BBB" ENABLE,
SUPPLEMENTAL LOG GROUP "GGS_87299" ("MXID") ALWAYS
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 589824 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "BBBBBB_BBB"
RESULT_CACHE(MODE FORCE) ;
SYSTEM@AAA.AAA.AA.AAA:1521/XXXX> select * from DBA_TAB_MODIFICATIONS where table_name='EMR_ZLSQMX';
TABLE_OWNER TABLE_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
------------ ---------- ------------------ ------- ---------- ---------- ------------------- --- -------------
BBBBBB_BBB EMR_ZLSQMX 4730 148 13 2016-01-03 17:11:45 NO 0
--奇怪这个是应用的字典表，估计这段时间用户在维护这张表吗？
SELECT sql_id,count(*)
FROM V$ACTIVE_SESSION_HISTORY
WHERE event = 'enq: RC - Result Cache: Contention'
group by sql_id
SQL_ID COUNT(*)
------------- ----------
g7ytdh9mxt1s0 2
futfjyqv0c6b8 46
--会不会显示这个的结果集太大，导致的问题。我在sqlplus设置
set autot traceonly
set timing on
--大约执行10次会出现1次存在consistent gets的情况。
SYSTEM@AAA.AAA.AA.AAA:1521/XXXX> select * from DBA_TAB_MODIFICATIONS where table_name='EMR_ZLSQMX';
TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
------------ ---------- --------------- ------------------ ---------- ---------- ---------- ------------------- --- -------------
BBBBBB_BBB EMR_ZLSQMX 4730 148 13 2016-01-03 17:11:45 NO 0
SYSTEM@AAA.AAA.AA.AAA:1521/XXXX> exec sys.DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO()
PL/SQL procedure successfully completed.
SYSTEM@AAA.AAA.AA.AAA:1521/XXXX> select * from DBA_TAB_MODIFICATIONS where table_name='EMR_ZLSQMX';
TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
------------ ---------- --------------- ------------------ ---------- ---------- ---------- ------------------- --- -------------
BBBBBB_BBB EMR_ZLSQMX 6298 197 15 2016-01-04 15:27:28 NO 0
--很明显这个表存在"大量"的DML操作。
SYSTEM@AAA.AAA.AA.AAA:1521/XXXX> SELECT id,TYPE,status,NAME,object_no,cache_id,invalidations,scn FROM v$result_cache_objects where INVALIDATIONS>=1000;
ID TYPE STATUS NAME OBJECT_NO CACHE_ID INVALIDATIONS SCN
---------- ------------ --------- --------------------- ---------- ---------------------- ------------- -------------
129667 Dependency Published BBBBBB_BBB.EMR_ZLSQMX 93765 BBBBBB_BBB.EMR_ZLSQMX 3817 14406729743
336708 Dependency Published BBBBBB_BBB.YF_DB01 96396 BBBBBB_BBB.YF_DB01 25145 14406725573
39171 Dependency Published BBBBBB_BBB.GY_XTCS 94089 BBBBBB_BBB.GY_XTCS 397620 14406731157
39283 Dependency Published BBBBBB_BBB.GY_YHCS 94111 BBBBBB_BBB.GY_YHCS 660917 14406730951
3 Dependency Published BBBBBB_BBB.GY_YGDM 94105 BBBBBB_BBB.GY_YGDM 5378 14406354949
--这条语句本来不存在优化的可能性。而且查询有关Result Cache的等待事件都是围绕这条语句。先取消RESULT_CACHE的设置。
SYSTEM@AAA.AAA.AA.AAA:1521/XXXX> ALTER TABLE BBBBBB_BBB.EMR_ZLSQMX RESULT_CACHE (MODE DEFAULT);
Table altered.
--昏，仔细检查这个表不是应用的字典表，叫"治疗申请单_单据分类明细项目",没有谓词条件，这样不是越查越慢吗？对于这样的开发真
--的很无语。
SYSTEM@AAA.AAA.AA.AAA:1521/XXXX> select * from DBA_TAB_MODIFICATIONS where table_name='EMR_ZLSQMX';
TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
------------ ---------- --------------- ------------------ ---------- ---------- ---------- ------------------- --- -------------
BBBBBB_BBB EMR_ZLSQMX 6298 197 15 2016-01-04 15:27:28 NO 0
SYSTEM@AAA.AAA.AA.AAA:1521/XXXX> exec sys.DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO()
PL/SQL procedure successfully completed.
SYSTEM@AAA.AAA.AA.AAA:1521/XXXX> select * from DBA_TAB_MODIFICATIONS where table_name='EMR_ZLSQMX';
TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
------------ ---------- --------------- ------------------ ---------- ---------- ---------- ------------------- --- -------------
BBBBBB_BBB EMR_ZLSQMX 6317 198 15 2016-01-04 15:35:11 NO 0