Reclaim unused space from MLOG$ segments after MVIEW Refresh

We are using fast-refreshable MVIEWs in some of our database for reporting purposes.
They are works fine most of the time.
However, periodically MLOG size is increased due to different reasons.
Such as: bulk data loading, other abnormal application activities, or delays with MVIEW refresh.
After that, even if you refresh MVIEW, HWM doesn't reset.
You need to reset it manually, using commands such as ALTER TABLE SHRINK/MOVE and so on.
Without resetting the HWM MVIEW refresh performance can be poor.

Good news: there are Patch 11072728 available for some platforms that can reset the HWM without manual intervention.
This patch was described in MOS note Space Not Reclaimed from MLOG$ Segments After MVIEW Refresh (Doc ID 1941137.1)
According to the note, the fix for bug 11072728 will be provided in upcoming 12.2 release.
I have already requested that patch for Solaris SPARC64 on top of 11.2.0.4.4/11.2.0.4.6.

Today I have decided to look closer on Patch 11072728 in one of non-production database.
I want to be sure that patch didn't harm production instance.

Reading the bug readme, I have found that fix for bug is not enabled by default.
You need to set underscope parameter _bug11072728_mv_refresh_truncate_log to 1 to enable it.
Worth to be noted, is that we can change this parameter on the session and on the system level:

SQL> select isses_modifiable,
  2         issys_modifiable
  3    from v$parameter
  4   where name = '_bug11072728_mv_refresh_truncate_log';

ISSES_MODIFIABLE ISSYS_MODIFIABLE
---------------- ----------------
TRUE             IMMEDIATE

Next script I have used to setup test schema:

SQL> grant connect to tc identified by tc;

Grant succeeded.

SQL> grant alter session to tc;

Grant succeeded.

SQL> grant create materialized view to tc;

Grant succeeded.

SQL> grant create table to tc;

Grant succeeded.

SQL> grant unlimited tablespace to tc;

Grant succeeded.

SQL> 
SQL> conn tc/tc
Connected.
SQL> 
SQL> create table t
  2  as
  3  select *
  4    from all_users;

Table created.

SQL> 
SQL> alter table t add constraint t_pk primary key(username);

Table altered.

SQL> 
SQL> create materialized view log on t with primary key;

Materialized view log created.

SQL> 
SQL> create materialized view mv_t
  2  refresh fast
  3  as
  4  select *
  5    from t;

Materialized view created.
    

In the above script I have created table, mat view log on it, and materialized view.
We can see that mat view log is empty, segment has 8 blocks allocated:

SQL> select count(*) from mlog$_t;

  COUNT(*)
----------
         0

SQL> 
SQL> select blocks
  2    from user_segments
  3   where segment_name = 'MLOG$_T';

    BLOCKS
----------
         8

I update table in loop, 100 times in total:

SQL> begin
  2    for i in 1..100
  3    loop
  4      update t
  5         set created = created
  6       where username <> 'SYS';
  7      commit;
  8    end loop;
  9  end;
 10  /

Check that mat view log is not empty, segment was extended:

SQL> select count(*) from mlog$_t;

  COUNT(*)
----------
     19400

SQL> 
SQL> select blocks
  2    from user_segments
  3   where segment_name = 'MLOG$_T';

    BLOCKS
----------
       104

Let's refresh materialized view now:

SQL> exec dbms_mview.refresh( 'mv_t', method=>'f')

PL/SQL procedure successfully completed.

If the patch 11072728 was not applied or the parameter "_bug11072728_mv_refresh_truncate_log" not set to 1, then the HWM is not reset and the segment space allocated is the same:

SQL> select count(*) from mlog$_t;

  COUNT(*)
----------
         0

SQL> 
SQL> select blocks
  2    from user_segments
  3   where segment_name = 'MLOG$_T';

    BLOCKS
----------
       104

In the production system with a high DML activity materialized view logs can grows to a much higher size.
And the materialized view refresh performance degrades.
As I said previously, before the patch 11072728 you need to reset the HWM manually.
With the patch 11072829 you dont need to do it anymore.
Just the set parameter "_bug11072728_mv_refresh_truncate_log" to 1:

SQL> exec dbms_mview.refresh( 'mv_t', method=>'f')

PL/SQL procedure successfully completed.

SQL> 
SQL> select count(*) from mlog$_t;

  COUNT(*)
----------
         0

SQL> 
SQL> select blocks
  2    from user_segments
  3   where segment_name = 'MLOG$_T';

    BLOCKS
----------
         8

You can see that segment allocated space was decreased.
How does it works under the hood?
I have setup sql tracing and event 10704 (enqueue trace) to investigate it:

SQL> alter session set events 'sql_trace bind=true:10704 level 10';

Session altered.

SQL> 
SQL> exec dbms_mview.refresh( 'mv_t', method=>'f')

PL/SQL procedure successfully completed.

I used below command to filter the irrelevant lines from output:
egrep "ksqgtl \*|ksqrcl: [^r]|^truncate|select count\(\*\) from .*MLOG" orcl_ora_18632.trc

• ksqgtl - get lock function
• ksqrcl - release lock function
• We want to show the truncate command and the select count(*) from MLOG.

Below is the output of egrep command, in which some lines was skipped for brevity:

select count(*) from "TC"."MLOG$_T"
ksqgtl *** TM-001854e4-00000000 mode=6 flags=0x401 timeout=0 ***
select count(*) from "TC"."MLOG$_T"
ksqgtl *** TM-001854e6-00000000 mode=6 flags=0x401 timeout=0 ***
truncate table "TC"."MLOG$_T"

These commands was executed when the materialized view update already done.
Looks like that new algorithm with MLOG truncate works in following way:

1. execute old refresh code
2. check count of rows in MLOG
3. if zero, lock master table in exclusive mode nowait (TM lock with timeout=0)
4. check count of rows in MLOG again (because there are can be DML between step 2 and 3)
5. lock MLOG in exclusive mode nowait (TM lock with timeout=0)
6. truncate MLOG

At least, an open transaction will prevent the truncation of MLOG.
And that indirectly confirms my assumptions about internal workings of new refresh algorithm. Let's update the 1 row in table in session 1:

SQL> -- session 1
SQL> update t
  2     set created=created
  3   where username='SYS';

1 row updated.

Now we will check space of MLOG before refresh, perform actual refresh and check the space again (in other session):

SQL> -- session 2
SQL> select blocks
  2    from user_segments
  3   where segment_name = 'MLOG$_T';

    BLOCKS
----------
       104

SQL> exec dbms_mview.refresh( 'mv_t', method=>'f')

PL/SQL procedure successfully completed.
SQL> 
SQL> select count(*) from mlog$_t;

  COUNT(*)
----------
         0

SQL> 
SQL> select blocks
  2    from user_segments
  3   where segment_name = 'MLOG$_T';

    BLOCKS
----------
       104

And the truncate of MLOG was not executed.
Looks like, it's safe to use a patch 11072728 in the production environment.
If I will face any issue with that patch in the production, I will update this blog post.