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

  • From: "Wayne Adams" <work@xxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 21 Jun 2012 17:39:47 -0400

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@xxxxxxxxxxxxxxxxxxxxxxxx> wadams@xxxxxxxxxxxxxxxxxxxxxxxx

602-769-6839

 



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


Other related posts: