Merge on SYS.MON_MODS_ALL$ Running Slow During Auto Stats Job (Doc ID 2708396.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

• Auto stats job ORA$AT_OS_OPT_SY_nn running slow due to below merge query accessing SYS.MON_MODS_ALL$ MM:
    
  MERGE /*+ dynamic_sampling(4) dynamic_sampling_est_cdn */ INTO SYS.MON_MODS_ALL$ MM USING ( SELECT OBJ#, SUM(FLGS) FLGS FROM ( SELECT OBJ#, :B1 FLGS FROM ( SELECT DISTINCT TAB.OBJ# FROM (SELECT T.OBJ# OBJ#, T.OBJ# BO#, T.ANALYZETIME FROM SYS.TAB$ T WHERE BITAND(T.FLAGS,16) = 16 UNION ALL SELECT T.OBJ# OBJ#, T.BO# BO#, T.ANALYZETIME FROM SYS.TABPART$ T WHERE BITAND(T.FLAGS,2) = 2 UNION ALL SELECT T.OBJ# OBJ#, T.BO# BO#, T.ANALYZETIME FROM SYS.TABCOMPART$ T WHERE BITAND(T.FLAGS,2) = 2 UNION ALL SELECT T.OBJ# OBJ#, TCP.BO# BO#, T.ANALYZETIME FROM SYS.TABSUBPART$ T, SYS.TABCOMPART$ TCP WHERE BITAND(T.FLAGS,2) = 2 AND T.POBJ# = TCP.OBJ# ) TAB, SYS.OBJ$ O, SYS.COL$ C WHERE TAB.BO# = O.OBJ# AND TAB.ANALYZETIME < O.MTIME AND C.OBJ# = TAB.BO# AND C.TYPE# IN ( 1, 2, 12, 23, 69, 96, 100, 101, 178, 179, 180, 181, 182, 183, 231) AND BITAND(C.PROPERTY, 16384+32768) = 0 AND NOT (BITAND(C.PROPERTY, 32+65536+131072) = 32+65536 AND C.DEFAULT$ IS NULL ) AND NOT EXISTS (SELECT NULL FROM SYS.HIST_HEAD$ HH WHERE HH.OBJ# = TAB.OBJ# AND HH.INTCOL# = C.INTCOL#)) UNION ALL SELECT DISTINCT TAB.OBJ# OBJ#, :B3 FLGS FROM (SELECT T.OBJ# OBJ#, T.OBJ# BO#, T.ANALYZETIME, T.ROWCNT FROM SYS.TAB$ T WHERE BITAND(T.FLAGS,16) = 16 UNION ALL SELECT T.OBJ# OBJ#, T.BO# BO#, T.ANALYZETIME, T.ROWCNT FROM SYS.TABPART$ T WHERE BITAND(T.FLAGS,2) = 2 UNION ALL SELECT T.OBJ# OBJ#, T.BO# BO#, T.ANALYZETIME, T.ROWCNT FROM SYS.TABCOMPART$ T WHERE BITAND(T.FLAGS,2) = 2 UNION ALL SELECT T.OBJ# OBJ#, TCP.BO# BO#, T.ANALYZETIME, T.ROWCNT FROM SYS.TABSUBPART$ T, SYS.TABCOMPART$ TCP WHERE BITAND(T.FLAGS,2) = 2 AND T.POBJ# = TCP.OBJ# ) TAB, SYS.OBJ$ O, SYS.USER$ U, SYS.COL$ C, SYS.OPTSTAT_USER_PREFS$ P1 WHERE TAB.BO# = O.OBJ# AND C.OBJ# = TAB.BO# AND O.OWNER# = U.USER# AND TAB.OBJ# = P1.OBJ#(+) AND P1.PNAME(+) = 'METHOD_OPT' AND C.TYPE# IN ( 1, 2, 12, 23, 69, 96, 100, 101, 178, 179, 180, 181, 182, 183, 231) AND BITAND(C.PROPERTY, 16384+32768) = 0 AND NOT (BITAND(C.PROPERTY, 32+65536+131072) = 32+65536 AND C.DEFAULT$ IS
  NULL ) AND DBMS_STATS.COLUMN_NEED_HIST(U.NAME, O.NAME, C.NAME, NVL(P1.VALCHAR, :B2 )) = 1 AND EXISTS (SELECT NULL FROM SYS.HIST_HEAD$ HH, SYS.COL_USAGE$ CU WHERE CU.OBJ# = TAB.BO# AND HH.OBJ# = TAB.OBJ# AND HH.INTCOL# = CU.INTCOL# AND BITAND(HH.SPARE2, 16) = 0 AND NOT (NVL(HH.ROW_CNT, 0) > 0 OR HH.NULL_CNT = TAB.ROWCNT) AND (CASE WHEN TAB.ANALYZETIME < CU.TIMESTAMP THEN (EQUALITY_PREDS + EQUIJOIN_PREDS + RANGE_PREDS + LIKE_PREDS + NONEQUIJOIN_PREDS) ELSE 0 END) > 0) UNION ALL SELECT DISTINCT CGU.OBJ# OBJ#, :B9 FLGS FROM SYS.COL_GROUP_USAGE$ CGU, OPTSTAT_USER_PREFS$ P1 WHERE BITAND(CGU.FLAGS, :B8 + :B7 ) = 0 AND BITAND(CGU.FLAGS, :B6 + :B5 ) > 0 AND CGU.OBJ# = P1.OBJ#(+) AND P1.PNAME(+)='AUTO_STAT_EXTENSIONS' AND NVL(P1.VALCHAR, :B4 ) = 'ON' ) GROUP BY OBJ# ) MCS ON (MM.OBJ# = MCS.OBJ#) WHEN MATCHED THEN UPDATE SET FLAGS = FLAGS + MCS.FLGS - BITAND(FLAGS, MCS.FLGS) WHEN NOT MATCHED THEN INSERT (OBJ#, INSERTS, UPDATES, DELETES, TIMESTAMP, FLAGS, DROP_SEGMENTS) VALUES(MCS.OBJ#, 0, 0, 0, :B10 , MCS.FLGS, 0)
   
  
• AWR shows this as top sql: 

  Top SQL with Top Events
  SQL ID FullPlanhash Planhash Sampled # of Executions % Activity Event % Event Top Row Source % RwSrc SQL Text Container Name
  cyr6acsyd4d0s 3425193910 737728066 1 100.00 CPU + Wait for CPU 85.71 HASH JOIN 32.42 MERGE /*+ dynamic_sampling(4) ... PDBxxx
  cyr6acsyd4d0s 3425193910 737728066 1 100 direct path write temp 8.67 HASH JOIN - ANTI 8.67 PDBxxx
  cyr6acsyd4d0s 3425193910 737728066 1 100 direct path read temp 5.62 HASH JOIN - ANTI 5.62 PDBxxx

    

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.