[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

[20160104]enq RC-Result Cache Contention

相關文章