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.

On Dec 2, 10:07=A0am, "stephen O'D" <stephen.odonn...@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. =A0The
> 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. =A0Oracle seems to
> require two queries against the mlog and master table to do the
> refresh, the first looks like this:
>
> SELECT =A0 =A0 =A0 =A0 =A0/*+ */
> =A0 =A0 =A0 =A0DISTINCT "A1"."M_ROW$$"
> =A0 =A0 =A0 =A0 =A0 =A0FROM "GENEVA_ADMIN"."MLOG$_BILLSUMMARY" "A1"
> =A0 =A0 =A0 =A0 =A0 WHERE "A1"."M_ROW$$" <> ALL (SELECT "A2".ROWID
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0FROM
> "GENEVA_ADMIN"."BILLSUMMARY" "A2"
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 WHERE "A2".ROWID =3D "A1"."M_ROW$
> $")
> =A0 =A0 =A0 =A0 =A0 =A0 AND "A1"."SNAPTIME$$" > :1
> =A0 =A0 =A0 =A0 =A0 =A0 AND "A1"."DMLTYPE$$" <> 'I'
>
> The current plan is:
>
> ---------------------------------------------------------------
> | Id =A0| Operation =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | Name =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|
> ---------------------------------------------------------------
> | =A0 0 | SELECT STATEMENT =A0 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 |
> | =A0 1 | =A0HASH UNIQUE =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
> | =A0 2 | =A0 FILTER =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
> | =A0 3 | =A0 =A0TABLE ACCESS BY INDEX ROWID| MLOG$_BILLSUMMARY =A0 =A0 |
> | =A0 4 | =A0 =A0 INDEX RANGE SCAN =A0 =A0 =A0 =A0 =A0| MLOG$_BILLSUMMARY=
_AK1 |
> | =A0 5 | =A0 =A0TABLE ACCESS BY USER ROWID | BILLSUMMARY =A0 =A0 =A0 =A0=
 =A0 |
>
> When there are 3M rows changed, this query literally runs forever -
> its basically useless. =A0However, 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. =A0The 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. =A0I 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 --

Stephen,

a couple of points:

1. Even if the query was running well: I think your query refers to
the delete part of the fast refresh and the fast refresh performs
single row operations with the obtained ROWIDs - so potentially 3
million single row operations by ROWID (for the delete and update
part), so this part of the fast refresh might require millions of
consistent gets (and potentially a lot of physical gets, too). I'm not
sure if this is reasonable performance-wise, so with that change
volume a full refresh might actually be faster - or as suggested
perform the fast refresh more often if possible.

2. The index on the MLOG (MLOG$_BILLSUMMARY_AK1) looks like a custom
index - I don't believe this is created by Oracle by default when
creating a Materialized View log.

3. The subquery cannot be unnested because the M_ROW$$ column is
defined as NULLable by default when creating the MLOG I think - if you
define it as mandatory more transformations like unnesting might be
possible - you can force them (if valid) by adding a "/*+ unnest */"
hint to the subquery for testing purposes.

4. Even with unnesting possible the range of transformations is quite
restricted due to the correlated NOT IN clause used by Oracle (the
unnested query might still use a NESTED LOOP, which might not be much
different from the FILTER performance-wise, potentially even worse due
to the subquery FILTER caching performed by the Oracle runtime
engine).

5. As Mark has already pointed out - what execution plan do you get
for the statement - are the estimates in the right ballpark?

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684

> 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 --

Problem is that when I do a full refresh it can take 5 or 6 hours.  By
then there are often several million changed rows to update and it
just never catches up.

The stats are good on the master table, but the MView logs have stats
locked to make it look as though they only have 1 row (work around for
a bug apparently) - I did try gathering stats and manually setting the
number of rows etc, but the query plan wouldn't change.

On other test environments I can do several million changes on
identical tables in 15 minutes.  I think we are suffering from too
many changes on the table causing problems getting a consistent read
on it, and a buffer cache on the replicated database that is too
small.

Tracing the sessions, they are doing lots of DB File Sequential Reads
- each one is about 10ms, which is fairly good, but not when there are
so many of them to do I guess.

On Dec 3, 10:11=A0pm, Randolf Geist <mah...@web.de> wrote:
> Stephen,
>
> a couple of points:
>
> 1. Even if the query was running well: I think your query refers to
> the delete part of the fast refresh and the fast refresh performs
> single row operations with the obtained ROWIDs - so potentially 3
> million single row operations by ROWID (for the delete and update
> part), so this part of the fast refresh might require millions of
> consistent gets (and potentially a lot of physical gets, too). I'm not
> sure if this is reasonable performance-wise, so with that change
> volume a full refresh might actually be faster - or as suggested
> perform the fast refresh more often if possible.

I traced this, and it seems to do a bulk delete and then maybe lots of
row by row inserts.  I have cooked up an inhouse version that uses the
MLOG to do just about the same thing, but it still takes a long time
(but at least it completes - the Oracle builtin version took 24 hours
before we killed it).  There are 3 indexes on the replicated table, so
I guess doing the 3M deletes and then 3M inserts is doing some serious
I/O and redo generation.


> 2. The index on the MLOG (MLOG$_BILLSUMMARY_AK1) looks like a custom
> index - I don't believe this is created by Oracle by default when
> creating a Materialized View log.

Yea, someone must have created that at some time - not sure why
either, as all the tables are only replicated once, and the snaptime
is always set to the same date, so it looks redundant to me.

> 3. The subquery cannot be unnested because the M_ROW$$ column is
> defined as NULLable by default when creating the MLOG I think - if you
> define it as mandatory more transformations like unnesting might be
> possible - you can force them (if valid) by adding a "/*+ unnest */"
> hint to the subquery for testing purposes.

You are correct, it is defined as nullable - I knew there has to be
some reason it wouldn't unnest for me.


> 5. As Mark has already pointed out - what execution plan do you get
> for the statement - are the estimates in the right ballpark?

We had problems in the past with unstable plans on the replication, so
Oracle recommended we truncate the mlogs, gather stats and lock the
stats to stabilise the plans - the master tables all have good stats,
so I guess it will push the optimizer more towards indexes than FTS,
which is generally a good thing for these queries.

Thanks for the help - I am getting nowhere fast with this, but I am
starting to think we are hitting the limits of our hardware on these
tables ...