| Best Practices to optimize a FAST REFRESH Materialized View? [message #539484] |
Mon, 16 January 2012 06:31  |
tsteinmaurer
Messages: 12
Registered: October 2008
|
Junior Member |
|
|
Hello,
I have a base table with ~20 mio. records with two FAST REFRESH Materialized Views based on that table using various aggregate functions in their view definition.
The problem is, when e.g. one record changes in the base table, I see two records in the MV log table MLOG$, but invoking the fast refresh mechanism by using using:
dbms_snapshot.refresh('mv1', 'f');
dbms_snapshot.refresh('mv2', 'f');
Is still running after ~20 minutes now.
Any ideas on how to improve performance in that area?
Thanks!
|
|
|
|
|
|
| Re: Best Practices to optimize a FAST REFRESH Materialized View? [message #539598 is a reply to message #539485] |
Tue, 17 January 2012 05:53  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
A "Fast" refresh is not automatically fast just because of the name. You have to do some work to make it so. Other than an appropriately structured query, you will need:
- Appropriate indexes on the base table
- Appropriate indexes on the Materialized View
- Appropriate indexes on the Materialized View Log
If you run a SQL Trace during a typical refresh, you will be able to see the underlying (recursive) SQL on these 3 tables performed by the refresh. Find out why they are slow. Assuming they are slow because they read a lot of data, one of three things will be true:
- A lot of the rows read by the underlying queries are unnecessary and can be avoided with targeted indexes.
- A lot of the rows read by the underlying queries are unnecessary and cannot be indexed. Bad luck.
- A lot of rows need to be processed because of high-cardinality joins in the MV query. Bad luck.
Hopefully the 1st one will be true.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
