| "Fast refresh" MV too slow [message #476450] |
Thu, 23 September 2010 06:29  |
ss1_3
Messages: 4
Registered: December 2009
|
Junior Member |
|
|
Hi,
We have a MV which fetches data from around 27 tables containing 26 joins out of which 25 are outer joins. Some tables in the query are being referred multiple times through different alias names and hence the actual no of physical tables used is 18. This MV takes about 50 mins to refresh through complete refresh mechanism. We decided to make it fast refresh and thus made these configurations:
- Created MV logs based on rowid for each of the base tables.
- Recreated MV using FAST refresh,with primary key option enabled
- Pulled rowid for all these tables in the select column statement.
Even after making all the recommendations suggested by Oracle for fast refresh MV's we are still getting refresh time of around 65 mins(refresh time increased!!!).We already have indexes built on all the join columns of the base tables. What else do we need to do to make this a "fast" refresh MV ?
Thanks
|
|
|
|
|
|
| Re: "Fast refresh" MV too slow [message #476733 is a reply to message #476457] |
Fri, 24 September 2010 18:03  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 23 September 2010 22:53Quote:What else do we need to do to make this a "fast" refresh MV ?
Nothing, joining 27 tables with 25 outer joins can only lead to bad peformances. There can't be NO fast refresh for this.
Regards
Michel
Hogwash.
If those 27 tables are all heavily updated, I'd agree. But we don't know the distribution of data or the way in which the data is changed. What if only a couple of those tables change?
Run SQL Trace when refreshing, and use TKPROF to obtain readable output from the trace. The TKPROF output will contain the internal SQL Oracle used to refresh the MV.
Find the bad SQL and start tuning. Of course, you cannot change the text of the SQL, but you can add indexes to base table and MV Logs, and also gather statistics on base tables, MV Logs, and their indexes when the CBO is making bad choices.
If you like, past the SQL, plan and statistics from TKPROF here and one (or more) of us might take a look.
Ross Leishman
[Updated on: Fri, 24 September 2010 18:03] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
