fast refresh of join-only MVs: _mv_refresh_use_stats and locking log stats

A devastating performance degradation of materialized view fast refreshes can happen in versions after 9i – and can be healed rather easily by simply setting the hidden parameter _mv_refresh_use_stats or, a bit surprisingly, by locking statistics on the logs. The problem can manifest at least in the currently-latest patchsets of 10g, 11gR1 and 11gR2 (10.2.0.4, 11.1.0.7 and 11.2.0.1), seems to hit a lot of people, and its root cause are the utilization of wrong hints by the Oracle refresh engine.

We will investigate the join-only MV case only, since this is the case I have investigated after a question by Christo Kutrovsky, factoring in some observations by Taral Desai and some Support notes; I have some clues that something similar may happen for other types of MVs.

The test case sets up this very common scenario for fast refreshes:

1 – two big base tables joined together by the MV;

2 – only a small fraction of rows modified (actually one deleted, two updated, one inserted);

3 – all tables and indexes with fresh statistics collected;

4 – MV logs with no statistic collected AND with not-locked statistics;

5 – indexes present on the joined columns;

6 – indexes present on the rowid columns of the MV.

Points 1 and 2 make for the ideal scenario for incremental (“fast”) refreshes to be effective; 3 is very common as well, since you normally have many other statements issued on the tables; the relevance of 4 will be clear later, but it happens very often in real life, since people might perhaps consider collecting stats on the log, but locking their statistics is usually not made, at least in my experience.

To understand the importance of points 5 and 6, please check this post of mine; note how those indexes are a necessary prerequisite for the sanity of the DEL and INS steps of the MV process. Without them, the refresh cannot be incremental since it has no physical way to read and propagate only the modified rows and those related to them, but it must scan (uselessly) most of the base tables and MV. But in other for the refresh to be incremental (“fast”), those indexes have to be actually used…

the issue

Let’s illustrate the issue focusing on the DEL step (the easier to discuss about). In the above mentioned post, we have seen that the DEL step uses a single SQL statement whose text, leaving out minor technical details and hints, is:

/* MV_REFRESH (DEL) */

delete from test_mv

where test_t1_rowid in

(

select * from

(

select chartorowid (m_row$$)

from mlog$_test_t1

where snaptime$$ > :1

) as of snapshot (:2)

)

In 9.2.0.8, we get this very healthy plan:

-------------------------------------------------

|Id|Operation |Name |

-------------------------------------------------

| 0|DELETE STATEMENT | |

| 1| DELETE | |

| 2| NESTED LOOPS | |

| 3| VIEW | |

| 4| SORT UNIQUE | |

| 5| TABLE ACCESS FULL|MLOG$_TEST_T1 |

| 6| INDEX RANGE SCAN |TEST_MV_TEST_T1_ROWID|

-------------------------------------------------

That is: get the rowid of all modified rows from the log, and use the rowid-based index to delete the “old image” of them from the MV (inserting their “new image” is the job of the INS step). This is truly incremental, since the resource usage and elapsed time are proportional to the number of rows logged in the MV log, not to the dimension of the tables.

In 10.2.0.4, 11.1.0.7 and 11.2.0.1 the plan becomes:

------------------------------------------

|Id|Operation |Name |

------------------------------------------

| 0|DELETE STATEMENT | |

| 1| DELETE |TEST_MV |

| 2| HASH JOIN RIGHT SEMI | |

| 3| TABLE ACCESS FULL |MLOG$_TEST_T1|

| 4| MAT_VIEW ACCESS FULL|TEST_MV |

------------------------------------------

Oops, the indexes are not used … hence the DEL step overhead is proportional to the size of the MV, and that can be definitely unacceptable.

That is due to the engine injecting an HASH_SJ hint in the outermost nested subquery:

... WHERE "TEST_T1_ROWID" IN (SELECT /*+ NO_MERGE HASH_SJ */ ...

This is recognized as a bug in many scenarios (start from Oracle Support note 578720.1 and follow the references to explore some of them) even if I have not found a clear and exhaustive note that documents the behaviour.

remedy one: set “_mv_refresh_use_stats”

Page 1 of 2 | Next page