...
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. re-create all of the materialized views. The goal of the new MView creation process is the support of FAST REFRESH on all 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.
...
as many Mviews as possible.
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". We would have preferred to use DBMS_ADVISOR.Tune_Mview, but do not have a license for it.
Performance Goals / Requirements
While our ultimate goal is to have all of the data in Course Management be an up-to-the-second copy of Banner data, we are aware that goal will not be reachable in most cases. The following table will list each of the mviews, an optimistic frequency in which the data in the source banner table is updated, and an acceptable time period in which the data might be allowed to stagnate.
Materialized View | Banner Update Frequency | Acceptable Staleness |
---|---|---|
BANNER_ACADEMICSESSION | Quarterly | One Month |
BANNER_COURSESET | Quarterly | One Month |
BANNER_CANONICALCOURSE | Yearly | One Month |
BANNER_CANON_COURSESETS | Quarterly | One Month |
BANNER_COURSEOFFERING | Monthly | One Week |
BANNER_OFFERING_COURSESETS | Monthly | One Week |
BANNER_CM_SECTION | Weekly | One Day |
BANNER_ENROLLMENTSET | Weekly | One Day |
BANNER_INSTRUCTORS | Weekly | One Hour |
BANNER_ENROLLMENT | Daily | One Hour |
BANNER_MEETING | Weekly | One Day |
Design Issues
- In 10G, there
Progress
...
- According to the Oracle 10G documentation, most of the mviews we are creating are "Materialized Views with Joins Only" and should be eligible for fast refresh. If necessary, I will try reformulating the mview and view definitions to use rowid based logs and mviews with rowid columns based on information from this page: http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14223/basicmv.htm#i1006803
- First roadblock: Canonical Courses. We are mapping this data from the SCBCRSE table, which has term information. In order to generalize it to canonical courses, a distinct clause is necessary. Distinct clauses will not work with mviews based on ROWID mview logs, and SCBCRSE doesn't have a primary key. So, in its current formulation, this mview will never fast refresh. That being said, we probably only need to run this mview once a month or so, and it stores less than ten thousand rows.
Status
Materialized View | Fast Refreshable? | Reason | Refresh Interval |
---|---|---|---|
BANNER_ACADEMICSESSION | Yes |
| 1 Day |
BANNER_COURSESET | No | UNION Clauses used that assemble non-identical data | 30 Days |
BANNER_CANONICALCOURSE | No | Distinct Clause in Query, SCBCRSE has no primary key | 30 Days |
BANNER_CANON_COURSESETS | No | Distinct Clause in Query, SCBCRSE has no primary key | 30 Days |
BANNER_COURSEOFFERING | No | Distinct Clause in Query, SCBCRSE has no primary key | 7 Days |
BANNER_OFFERING_COURSESETS | No | UNION Clauses used that assemble non-identical data | 7 Days |
BANNER_CM_SECTION | Yes |
| 60 Minutes |
BANNER_ENROLLMENTSET | Yes |
| 60 Minutes |
BANNER_INSTRUCTORS |
|
|
|
BANNER_ENROLLMENT | Yes |
| 15 Minutes |
BANNER_MEETING | No | Distinct Clause in Query, SSRMEET has no primary key | 1 Day |