Home » RDBMS Server » Performance Tuning » Best Practices to optimize a FAST REFRESH Materialized View? (Oracle 11g)
Best Practices to optimize a FAST REFRESH Materialized View? [message #539484] Mon, 16 January 2012 06:31 Go to next message
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 #539485 is a reply to message #539484] Mon, 16 January 2012 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 67950
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ It depends on your data and dml
2/ Are you sure it makes a FAST refresh

Regards
Michel
Re: Best Practices to optimize a FAST REFRESH Materialized View? [message #539598 is a reply to message #539485] Tue, 17 January 2012 05:53 Go to previous message
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
Previous Topic: Wait events for a query
Next Topic: AWR report
Goto Forum:
  


Current Time: Sun Sep 26 02:55:19 CDT 2021