enq: MS - contention when selecting from base tbl during refresh

From: Wayne Adams <work_at_wayneadams.com>
Date: Thu, 21 Jun 2012 17:39:47 -0400
Message-ID: <00be01cd4ff6$6237ddf0$26a799d0$_at_wayneadams.com>

Got a weird issue that I'm hoping somebody else has seen. So far Oracle hasn't been able to figure it out.  

We have a scenario where sessions querying one of the base tables of an MV are experiencing "enq: MS - contention" when the MV is being refreshed
(FAST).
  We setup the following test:  

Note: MV is HT_DISTRICT_SKU_2. It's a 3 table join of SKU_TBL, HT_ITEM_CAT_SUB_CAT, and HT_STORE_INFO. Definitions at the bottom of this email.  

Session1:

SQL> alter session set events '10046 ....';

SQL> exec dbms_refresh.refresh('HT_DISTRICT_SKU_2');  

Waited until Session1 was doing the following:  

update "RVADMIN"."MLOG$_SKU_TBL" set snaptime$$ = :1 where rowid in (select rowid from "RVADMIN"."MLOG$_SKU_TBL" AS OF SNAPSHOT (:2) log$ where snaptime$$ > to_date('2100-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'))  

This update usually takes it around 1 - 10 min (depending on the current HWM and the # of updates to SKU in the interval). Then started Session2.  

Session2:

SQL> alter session set events '10046 ....';

SQL> select sku from sku_tbl where whre org_id = 239 and sku=762575;  

The PK of SKU_TBL is org_id, sku. So this should have returned immediately. Instead, it hangs until Session1 is done with the "update MLOG$" portion of the refresh. Monitoring tools show the session waiting on 'enq: MS - contention'.  

When we looked at the trace file for Session2, we saw that after the "select sku from sku_tbl.." is parsed, the database does a bunch of internal/recursive queries (i.e. obj$, snap$, etc.) and then does the following:  

/* QSMQ VALIDATION */ ALTER SUMMARY "RVADMIN"."HT_DISTRICT_SKU_2" COMPILE;   It is this statement that is waiting on "enq: MS - contention", and continues to wait until the "update MLOG$" portion of the refresh is done.  

Now, it seems rather self explanatory why the ALTER SUMMARY .. COMPILE; is experiencing that wait (another session is doing a refresh). What is not obvious, to me at least, is why the session is trying to do that COMPILE in the first place. Since we are only querying one of the base tables.  

Any ideas would be appreciated!! We've opened an SR, but the analyst hasn't been able to find anything matching. We're working on coming up with a test case, but haven't been successful yet.  

This is 11.2.0.3 on AIX 7.1.  

The MV has the following definition (stripped down):  

CREATE MATERIALIZED VIEW HT_DISTRICT_SKU_2 (SKU,DESCRIPTION,. . . .) TABLESPACE SGMEDIUMDATA NOCACHE LOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE USING INDEX             TABLESPACE SGMEDIUMINDX REFRESH FAST START WITH TO_DATE('22-Jun-2012 04:47:58','dd-mon-yyyy hh24:mi:ss')

NEXT SYSDATE + 1 WITH PRIMARY KEY ENABLE QUERY REWRITE AS

SELECT . . .   FROM sku_tbl sk,

ht_item_cat_sub_cat ct,

                ht_store_info ts

WHERE     . . . .

GROUP BY . . . .;   CREATE MATERIALIZED VIEW LOG ON SKU_TBL TABLESPACE SGMEDIUMDATA NOCACHE LOGGING ENABLE ROW MOVEMENT NOPARALLEL WITH ROWID, SEQUENCE
(AUTO_ORDER,CATEGORY_CODE,DEPT_NUMBER,DESCRIPTION,ORG_ID,PERPETUAL_FLAG,PRIM
ARY_ITEM_ID,QTY_AVAILABLE,SKU,USER_NUM9_1,WKLY_NS_MOVE,WKLY_PROMO_MOVE) INCLUDING NEW VALUES;   Thanks in advance for the help!!  

Wayne Adams

Wayne Adams Consulting LLC.

 <mailto:wadams_at_wayneadamsconsulting.com> wadams_at_wayneadamsconsulting.com

602-769-6839  

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 21 2012 - 16:39:47 CDT