Agreeing completely with what Kellyn said, and I'd like to illustrate one aspect of her point a bit...
An example might be a typical Order-Entry type schema, where there is a CUSTOMER table, which has "child" records in an ORDER table, which itself has "child" records in an ORDER_LINE table. Additionally, there is a PRODUCT table which also has "child" records in the ORDER_LINE table. In order to improve reporting, an MV has been created which joins all four of these tables together, with MV logs created to capture all changes to any of these tables.
Here is the key characteristic that fuels performance problems during refresh: the PRODUCT table is the smallest and least volatile, and the ORDER_LINE table is the largest and most volatile.
Because of this, when the DBMS_MVIEW.REFRESH procedure is executed, it scans each of the four MLOG$ (materialized view log) tables, looking for captured row-changes. For each row in the MLOG$ table, a ROWID or primary-key tuple is used to reference the changed row in the source table. Then, from there, the rest of the data for materializing the new data state in the MV is generated by joining from that changed row to the other tables comprising the MV.
If we're looking at a changed row in the ORDER_LINE table, joining to the other three tables is easy: from that row in the ORDER_LINE table, we join "upward" along the foreign key to the ORDER table and then again "upward" to the CUSTOMER table, and likewise we join "upward" to the PRODUCT table. All of these joins perform UNIQUE SCANs on the primary key indexes of each of these tables, so refreshing the MV from changes to the largest and most volatile ORDER_LINE table is very fast and efficient, due to it's role in the schema as a "child" entity to everything else.
Not so with the other three tables. Let's use an an example that PRODUCT table, and what is described here holds true for the CUSTOMER and ORDER tables as well. When the MLOG$PRODUCT table has rows, we easily access the corresponding row in the PRODUCT table by ROWID or primary-key. But from there, things go bad immediately, because the next step is to join to the ORDER_LINE table along the PRODUCT_KEY relationship. As previously mentioned, the PRODUCT table in this example is very small and the ORDER_LINE table is very big, so on average there might be thousands, tens of thousands, hundreds of thousands, or millions of ORDER_LINE rows sharing the same PRODUCT_KEY value. This makes for a very nasty and poorly-performing RANGE SCAN on that PRODUCT_KEY index on the ORDER_LINE table, which means that a single row-change captured on the PRODUCT table takes a lot longer to refresh to the MV than the thousands of row-changes captured on the ORDER_LINE table, possibly causing millions of rows being refreshed in the MV. And the same might be true for row-changes captured on the CUSTOMER and ORDER table as well.
So, the question must be asked: why is the PRODUCT table included in the MV at all? Is its inclusion really helping the primary mission of the MV, which is to improve query performance? If the MV just consisted of the CUSTOMER, ORDER, and ORDER_LINE tables, then a run-time query hitting the MV will still have to join to the actual "live" PRODUCT table, and is that a bad thing? Removing the PRODUCT table from the MV would not negatively impact queries, but will very positively impact the performance and efficiency of MV refreshes, as described above.
The upshot: prune MV definition to ensure it doesn't include references to tables whose inclusion doesn't really help the MV's mission of improving query performance.
To answer part of Anthony's original question: you can query the view DBA_MVIEW_LOGS to reveal the MASTER table of the materialized view log, the "source table" on which it monitors. Then, query DBA_MVIEW_DETAIL_RELATIONS and search on the columns DETAILOBJ_OWNER and DETAILOBJ_NAME where DETAILOBJ_TYPE = 'TABLE'.
Hope this helps... Thanks! -Tim On 8/18/2011 2:45 PM, Kellyn Pot'vin wrote:
Just another recommendation- look at the actual SQL for the mview refresh. Is the SQL so complex that it could be delaying the refresh times and causing part of the issue? I have always pushed to simplify the queries as much as possible utilized for the refreshes and keep complex code in the reporting side against the mviews vs. the source. Often a little tuning can go a long way when you are experiencing problems with mview refreshes/extensive mview log sizes.Thanks, Kellyn Pot'Vin Sr. Database Administrator and Developer dbakevlar.com ------------------------------------------------------------------------ *From:* Upendra N <nupendra@xxxxxxxxxxx> *To:* anthony.ballo@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx *Sent:* Wednesday, August 17, 2011 7:41 PM *Subject:* RE: Growing MLOG$ >> PS. I tried running these scripts but don't see my table listed:If you don't have an Materialized view created on the source table, you should drop the MV log. Otherwise you'll see MLOG$ keeps growing, even if you manually truncate them it will grow again when you have more new transactions modify the table. You can recreate the MV LOG when you are ready to create the first Materialized view.Hope this helps -Upendra > From: anthony.ballo@xxxxxxxxxxx > To: oracle-l@xxxxxxxxxxxxx > Date: Wed, 17 Aug 2011 16:44:39 -0700 > Subject: Growing MLOG$ > > We have a MLOG$ table that grows to 100m+ rows in a matter of days. > Currently, our DBA is truncating the table. Today it has generated 33m > rows. > > From my research, rows in these logs should be deleted when a refresh > occurs on a MV and that a growing MLOG$ is the sign of a MV not > refreshing. (basically true?) > > So, I was wondering on how to best troubleshoot and find the MV that> refers to this log. We also have Materialized Views on other systems (via> DBLink) that references this table. >> On a side note: We also have a high amount (IMO) of REDO creation which I> think this MLOG$ possibly plays a role in. I've been seeing 35-50 log> switches per hour (peak 108) constantly in the 24hr day. We have 18 groups> of log files and some are 104857600 bytes (100mb) - others are 209715200 > (200mb). > > > > Thanks, > > > Anthony > > > Oracle 10.2.0.4/Linux > > > > > PS. I tried running these scripts but don't see my table listed: > > > Select > a.OWNER,a.MASTER,a.MVIEW_LAST_REFRESH_TIME,m.OWNER,m.NAME,m.MVIEW_SITE > From all_base_table_mviews a,ALL_REGISTERED_MVIEWS m > Where m.MVIEW_ID=a.MVIEW_ID > And a.MASTER='PRODUCTS' > > select MOWNER,MASTER,SNAPSHOT,SNAPID,SNAPTIME,SSCN,USER# from sys.slog$ > where mowner='PRODUCTS' and master='PRODUCTS'; > > SELECT r.NAME snapname, snapid, NVL(r.snapshot_site, 'not registered') > snapsite, snaptime FROM sys.slog$ s, dba_registered_snapshots r > WHERE s.snapid=r.snapshot_id(+) AND mowner LIKE UPPER('&owner') AND > MASTER LIKE UPPER('&table_name'); > > SELECT * FROM DBA_REGISTERED_MVIEWS > > -- > //www.freelists.org/webpage/oracle-l > >
