Autonomous MView Log Shrinkage

With version 12.2 there seems to have a some change in behavior of Materialised View Logs which caught us by surprise; I haven’t seen it documented elsewhere, so let me demonstrate.

My test instance is un-patched 12.2.0.1 running on Oracle Linux.

For the setup, I create a table, a mview log on that table, a mview that can use the mview log and a stored procedure that can insert and delete rows from the table (thus populating the mview log).

SQL> CREATE TABLE detail (
  2      id        NUMBER
  3          GENERATED BY DEFAULT ON NULL AS IDENTITY
  4      PRIMARY KEY,
  5      padding   VARCHAR2(255)
  6  );

Table DETAIL created.

SQL> CREATE MATERIALIZED VIEW LOG ON detail WITH
  2      ROWID,
  3      SEQUENCE ( id,
  4                 padding )
  5      INCLUDING NEW VALUES;

Materialized view log DETAIL created.

SQL> CREATE MATERIALIZED VIEW summary AS
  2      SELECT
  3          COUNT(*)
  4      FROM
  5          detail;

Materialized view SUMMARY created.

SQL> CREATE PROCEDURE insert_and_delete AS
  2  BEGIN
  3      INSERT /*+append*/ INTO detail ( padding )
  4          SELECT
  5              lpad('X',255,'X')
  6          FROM
  7              dual
  8          CONNECT BY
  9              level  comment to avoid WordPress format issue
 10              ;  
 11  
 12      COMMIT; 
 13      DELETE FROM detail;  
 14  
 15      COMMIT;
 16  END;
 17  /
 Procedure INSERT_AND_DELETE compiled 
SQL>

Then I call the stored procedure to populate the mview log, and then perform a fast refresh.

SQL> EXEC insert_and_delete;

PL/SQL procedure successfully completed.

SQL> EXEC dbms_mview.refresh('summary','f');

PL/SQL procedure successfully completed.

SQL>

Observe from the alert log that because the entries in the mview log have been deleted as part of the fast refresh, Oracle determines that it is appropriate to enable row movement on the mview log table and perform a “shrink space” operation.

MVRF: kkzlShrinkMVLog: recommendations: Enable row movement of the table PATRICK.MLOG$_DETAIL and perform shrink, estimated savings is 38789120 bytes.
MVRF: kkzlShrinkMVLog: executed: alter table "PATRICK"."MLOG$_DETAIL" enable row movement
MVRF: kkzlShrinkMVLog: executed: alter table "PATRICK"."MLOG$_DETAIL" shrink space

Re-running the test, it is not necessary to re-enable row movement (the table retains this setting), so the “shrink space” action only is executed.

SQL> EXEC insert_and_delete;

PL/SQL procedure successfully completed.

SQL> EXEC dbms_mview.refresh('summary','f');

PL/SQL procedure successfully completed.

SQL>
MVRF: kkzlShrinkMVLog: recommendations: Perform shrink, estimated savings is 38789120 bytes.
MVRF: kkzlShrinkMVLog: executed: alter table "PATRICK"."MLOG$_DETAIL" shrink space

This behavior caused us some problems on our live system, because other sessions were blocked trying to refresh the mview while the shrink space was running.

Oracle Support Doc ID 2320441.1 describes this behavior, and suggests that an underscore parameter, _mv_refresh_shrink_log can be used to disable the shrink space.

I re-run my test-case with this set at session level

SQL> ALTER SESSION SET "_mv_refresh_shrink_log" = false;

Session altered.

SQL> EXEC insert_and_delete;

PL/SQL procedure successfully completed.

SQL> EXEC dbms_mview.refresh('summary','f');

PL/SQL procedure successfully completed.

SQL>

Monitoring the alert log, it can be seen that the “shrink space” operation no longer takes place.

If you apply this change you may want to keep an eye on your mview log sizes, and shrink manually if necessary.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s