Wednesday, September 19, 2012

Growing MLOG$ Tables with EBS 12

There are several snapshot tables (materialized views) in EBS that are set to Fast Refresh on Demand that, if not accessed, will grow to very large sizes. It's good to take a look at the what schemas these tables might reside in using the following SQL:

  SELECT owner, object_name
    FROM dba_objects
   WHERE object_name LIKE 'MLOG$%' AND object_type = 'TABLE'
ORDER BY owner, object_name

Added Tip: Or even better, run this to see which MLOG tables you should address first:


  SELECT *
    FROM (  SELECT owner, table_name, TO_NUMBER (EXTRACTVALUE (xmltype (DBMS_XMLGEN.getxml ('select count(*) c from ' || owner || '.' || table_name)), '/ROWSET/ROW/C')) COUNT
              FROM all_tables
             WHERE table_name LIKE 'MLOG$%'
          ORDER BY owner, table_name)
ORDER BY COUNT DESC

If you stats are current, you can quickly get an idea of the impact of this on your system. In my case, I had several snapshot logs (MLOG$ tables) that were over 1m rows and as high as 17m rows.

Using Toad, it might be as simple as navigating to the underlying materialized view and right-click and doing a Refresh (Complete, Atomic = False).  To find out what MV this log is tied to, you can use this:

SELECT a.OWNER,
       a.MASTER,
       a.MVIEW_LAST_REFRESH_TIME,
       m.OWNER,
       m.NAME,
       m.MVIEW_SITE
  FROM all_base_table_mviews a, ALL_REGISTERED_MVIEWS m
 WHERE m.MVIEW_ID = a.MVIEW_ID AND a.MASTER = 'MTL_MATERIAL_TRANSACTIONS_TEMP'



To find out what a.MASTER is looking for, simply browse the DDL for the MLOG$ object. You will see a comment made:

COMMENT ON TABLE INV.MLOG$_MTL_MATERIAL_TRANSAC IS 'snapshot log for master table INV.MTL_MATERIAL_TRANSACTIONS_TEMP';



When refreshing, you may encounter the following error(s) however:

BEGIN
  DBMS_SNAPSHOT.REFRESH(
    LIST                 => 'INV.MTL_SYS_ITEMS_SN'
   ,METHOD               => 'C'
   ,PUSH_DEFERRED_RPC    => TRUE
   ,REFRESH_AFTER_ERRORS => FALSE
   ,PURGE_OPTION         => 1
   ,PARALLELISM          => 0
   ,ATOMIC_REFRESH       => FALSE
   ,NESTED               => FALSE);
END;
Error at line 2
ORA-12008: error in materialized view refresh path
ORA-08002: sequence MRP_AP_REFRESH_S.CURRVAL is not yet defined in this session
ORA-06512: at "APPS.MRP_SN_SYS_ITEMS_T1", line 7
ORA-04088: error during execution of trigger 'APPS.MRP_SN_SYS_ITEMS_T1'
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2429
ORA-06512: at line 2

At this point, it is just as easy to drop and recreate the MV. If you had very large MLOG$ tables, you will have to do the following to return space back to the tablespace:

11:24:00 GOLD1> alter materialized view log on INV.MTL_MATERIAL_TRANSACTIONS_TEMP enable row movement;

Materialized view log altered.

11:30:33 GOLD1> alter materialized view log on INV.MTL_MATERIAL_TRANSACTIONS_TEMP shrink space compact;

Materialized view log altered.

11:30:53 GOLD1> alter materialized view log on INV.MTL_MATERIAL_TRANSACTIONS_TEMP shrink space;

Materialized view log altered.

11:31:53 GOLD1> alter materialized view log on INV.MTL_MATERIAL_TRANSACTIONS_TEMP disable row movement;

Materialized view log altered.

11:32:28 GOLD1> alter materialized view log on INV.MTL_MATERIAL_TRANSACTIONS_TEMP deallocate unused;

Materialized view log altered.


That should take care of this Maintenance task. Moving forward, you may want to schedule a regular job to address these by using combinations of :

DBMS_MVIEW.REFRESH('INV.MTL_MTRX_TMP_SN', 'C', atomic_refresh=>FALSE);

and dropping/creating the MV's that cannot be refreshed.

No comments:

Post a Comment