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.