Re: MView fast refresh taking long time
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Thu, 3 Dec 2009 07:11:54 -0800 (PST)
Message-ID: <d99046e8-1a6e-49d9-9366-06561a5994b7_at_j9g2000vbp.googlegroups.com>
On Dec 2, 10:07 am, "stephen O'D" <stephen.odonn..._at_gmail.com> wrote:
> 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.
Date: Thu, 3 Dec 2009 07:11:54 -0800 (PST)
Message-ID: <d99046e8-1a6e-49d9-9366-06561a5994b7_at_j9g2000vbp.googlegroups.com>
On Dec 2, 10:07 am, "stephen O'D" <stephen.odonn..._at_gmail.com> wrote:
> 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.
Stephen, Have you tried updating the statistics on the source table (and mview logs) just prior to the refresh?
If you have support then maybe one of the following articles will be of some help:
Consolidation Of Issues With Slow Materailized View Refresh [ID 735360.1]
Fast Refresh of Mview Taking 20 Min. Due to Hints & Full Table Scans of 20gb Table [ID 733054.1]
Fast Refresh of Top Tier Nested Materialized View is Very Slow [ID 358125.1]
Five hours seems an alful long time for updating only 3 million rows. Have you considered trying to perform the fast refresh more frequently to keep the amount of data that needs refreshing smaller?
HTH -- Mark D Powell -- Received on Thu Dec 03 2009 - 09:11:54 CST