MView fast refresh taking long time

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: Wed, 2 Dec 2009 07:07:06 -0800 (PST)
Message-ID: <695fd617-7975-45a7-bfb3-0b906c6f5054_at_g26g2000yqe.googlegroups.com>



I have a large table that is replicated from Oracle 10.2.0.4 to and Oracle 9i database using MView replication over the network. The master table is about 50GB, 160M rows and there are about 2 - 3M new or updates rows per day.

The master table has a materialized view log created using rowid.

The full refresh of the view works and takes about 5 hours, which we can live with.

However the fast refresh is struggling to keep up. Oracle seems to require two queries against the mlog and master table to do the refresh, the first looks like this:

SELECT          /*+ */
       DISTINCT "A1"."M_ROW$$"
           FROM "GENEVA_ADMIN"."MLOG$_BILLSUMMARY" "A1"
          WHERE "A1"."M_ROW$$" <> ALL (SELECT "A2".ROWID
                                         FROM
"GENEVA_ADMIN"."BILLSUMMARY" "A2"
                                        WHERE "A2".ROWID = "A1"."M_ROW$
$")
            AND "A1"."SNAPTIME$$" > :1
            AND "A1"."DMLTYPE$$" <> 'I'

The current plan is:


| Id  | Operation                     | Name                  |
---------------------------------------------------------------

| 0 | SELECT STATEMENT | |
| 1 | HASH UNIQUE | |
| 2 | FILTER | |
| 3 | TABLE ACCESS BY INDEX ROWID| MLOG$_BILLSUMMARY |
| 4 | INDEX RANGE SCAN | MLOG$_BILLSUMMARY_AK1 |
| 5 | TABLE ACCESS BY USER ROWID | BILLSUMMARY |

When there are 3M rows changed, this query literally runs forever - its basically useless. However, if I rewrite it slightly and tell it to full scan the master table and mlog table, it completes in 20 minutes.

The problem is that the above query is coming out of the inners of Oracle and I cannot change it. The problem is really the FILTER operation on line 2 - if I could get it to full scan both tables and hash join / anti-join, I am confident I can get it to complete quick enough, but no receipe of hints I offer will get this query to stop using the FILTER operation - maybe its not even valid. I can use hints to get it to full scan both the tables, but the FILTER operation remains, and I understand it execute long 5 for each row returned by line 3, which will be 2- 3M rows.

Has anyone got any ideas on how to trick this query into the plan I want without changing the actual query, or better, any ways of getting replication to take a more sensible plan for my tablesizes?

Thanks,

Stephen. Received on Wed Dec 02 2009 - 09:07:06 CST

Original text of this message