CASE STUDY 1

How to Handle Materialized View Refresh Performance?
----------------------------------------------------
(From the personal experiences of JP Vijay Kumar)

The issue on hand:
A materialized view created on a remote master table is taking 23 seconds for fast refresh.

begin
dbms_refresh.refresh ('"REPADMIN"."CREDIT_REFRESH"');
end ;
/
PL/SQL procedure successfully completed.

Elapsed: 00:00:23.79

The expectation for the materialized view refresh should complete within one second.

v$session_wait table output points that waits are occuring in the remote database.
As the event is "SQL*Net message from dblink".

EVENT P1 P2 P3 WT SECS STATE
-------------------- ---------- ---------- ---------- ----- ------ ----------
SQL*Net message from 1413697536 1 0 0 16 WAITING
dblink

Enabled sql_trace on the local database for a short period.
alter system set events '10046 trace name context forever,level 12';

Ran the materialized view refresh from the local database, disabled sql_trace.

alter system set events '10046 trace name context off';

Ram the tkprof on the trace file generated

tkprof <infile trace file> <outputfile jp.log> waits=yes

From the tkprof output of the trace file(output from local db),
time spent by CPU is negligible and ELAPSED time is considerable.

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 22.73 0 0 1 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 22.73 0 0 1 1

V$session_wait table out put from the remote database shown, the waits are
occuring on 'db file scattered read' event.

The trace files on the remote db pointed, 'db file scattered read' on file# 9.

"WAIT #1: nam='db file scattered read' ela= 5968 p1=9 p2=107"

This file is related to the tablespace where the master table and the
materialized
view log are created.

'db file scattered read' relates to FULL TABLE SCAN.

Surprisingly, full table scan on a small table should not take long time.

The master table is having 1038 rows.
The materialized view log is having 6610659 rows.

The size of the master table 0.18 mg.
The size of materialized view log 424 mg.

The materialized view log is not getting purged regularly. Fast refreshes on the materialized views do not purge the materialized view logs. As detailed in the oracle documentation, complete refresh on the materialized views is not purging the materialized view log.

The materialized view log was manually purged.

The materialized view log on the remote table is purged:
TRUNCATE TABLE K00.CREDIT_CHECK PURGE MATERIALIZED VIEW LOG;

After purging the remote materialized view log the materialized view refresh came down to 5 seconds.

begin
dbms_refresh.refresh ('"REPADMIN"."CREDIT_REFRESH"');
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.83

The interesting part is the size of the materialized view log is still at 424 meg.

Where as the physical row count
The master table is having 1038 rows.
The materialized view log is having 1038 rows.

The materialized view log and the master table are truncated with
"drop storage option"
truncate table K00.MLOG$_CREDIT_CHECK drop storage;
truncate table K00.CREDIT_CHECK drop storage;
TRUNCATE TABLE K00.CREDIT_CHECK PURGE MATERIALIZED VIEW LOG;

This time the size of the materialized view log got reduced:

The size of the master table 0.18 mg.
The size of materialized view log 0.06 mg.

The big dividend is the materialized view's refresh time:

begin
dbms_refresh.refresh ('"REPADMIN"."CREDIT_REFRESH"');
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.25

The effort is well paid, by tuning the query running in 23 seconds
to complete in just 25 milliseconds.
The version of Oracle worked in the problem is 9.2.07.