Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 5 Next »

Materialized View Troubleshooting

The UC Davis Sakai isntance, Smartsite, uses materialzied views from the Banner Student Information System to provide course data. An initial set of mviews were created for the Sakai 2.1.x branch that worked well with the provider mechanism. Sakai 2.4.x uses the new Course Management system, which radically changes the data requirements from Banner. The initial set of mviews that were created to feed the Course Management system encountered problems when refreshing. This page will document the efforts to revise the materialized view definitions.

Goal

Create a new set of Materialized Views supporting FAST REFRESH on all MViews. Note: There is a similar project underway to optimize the performance of queries on the Course Management Mviews when queried by Sakai. These two projects have been isolated from each other for now to prevent Course Management development being blocked by the troubleshooting work.

Troubleshooting Plan

Using the Sakai_Test schema on the Smartsite Development database, we will simulate the entire MView structure, including the source Banner tables. This will be done via the creation of manually refreshed Mviews of Banner tables with the Banner indexes re-created on the resultant tables. These tables can then have view logs created on them, from which a new set of Materialized Views will be created. The goal of the new MView creation process is the support of FAST REFRESH on all MViews.

  1. Create manual refresh MViews representing the source Banner tables, henceforth referred to as the Banner Replicant MViews.
  2. Re-create indexes from Banner source tables to the Banner Replicant MViews.
  3. Create Materialized View Logs on the Banner Replicant MViews.
    • The 10g documentation seems to prefer using rowid-based view logs and view creations for join MViews. We will be trying this method first.
  4. Create and test new Course Management MViews based on the Banner Replicant MViews and Logs.
    • Testing will be performed via the DBMS_SNAPSHOT.Explain_MView procedure. This procedure analyzes an MView definition much like explain_plan does for a query, but provides important information on refresh status. We would have preferred to use DBMS_ADVISOR.Tune_Mview, but do not have a license for it.

Testing

The mviews will be tested using the SYS.DBMS_SNAPSHOT.EXPLAIN_MVIEW(<Mview Name>); procedure. This procedure relies on the existence of a table called mv_capabilities_table in the mview schema. This table can be created via an admin oracle script at "oracle\product\ora10\rdbms\admin\utlxmv.sql".

Design Issues

Progress

  1. Banner Replicant MViews created with re-created Banner indexes.
  • No labels