Replicaton Tuning Tool

The primary tools used to perform the FAST REFRESH analysis were:

1. Oracle provided DBMS_MVIEW.explain_mview procedure
2. MV_CAPABILITIES_TABLE table

The Oracle provided DBMS_MVIEW.explain_mview procedure was used to analyze each of the existing materialized views FAST REFRESH capabilities and write the results to the MV_CAPABILITIES_TABLE:

1. Lists the general capabilities of materialized view or materialized view query
2. Specifies whether each capability is possible (Y or N)
3. If a capability is not possible the reason (why) is written to the msgtxt column of the MV_CAPABILITIES_TABLE

The MV_CAPABILITIES TABLE is created in the owner’s schema by running the following Oracle 9i script:

SQL> @ $ORACLE_HOME/rdbms/admin/utlxmv.sql

Example:

After creating utlxmv.sql script execute the below command.

SQL> exec dbms_mview.explain_mview( 'GENERALISSUEARNO');


SQL> SELECT CAPABILITY_NAME,POSSIBLE,SUBSTR(MSGTXT,1,60) MSGTXT FROM MV_CAPABILITIES_TABLE WHERE CAPABILITY_NAME LIKE '%FAST%';

CAPABILITY_NAME P MSGTXT
------------------------------ - ------------------------------------------------------------
REFRESH_FAST Y
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML Y
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the mater
REFRESH_FAST_PCT N the materialized view is upgraded from a previous version th

6 rows selected.