Re: Any suggestions on speeding this MView query up?
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: robertgfreeman@xxxxxxxxx
- Date: Thu, 8 Feb 2007 09:34:59 -0800
On 2/7/07, Robert Freeman <robertgfreeman@xxxxxxxxx> wrote:
All,
I've got the following query that is used to create a
fast refresh materialized view. I'm wondering if
anyone has any ideas on tuning it. Query and execution
plan below (I hope it formats ok).... I'm happy to
answer any questions.
I'm seeing about 45% DISK IO wait issues, and about
55% CPU times on the query when it runs as it is.
Hi Robert,
No suggestions per se for that particular query, but a suggestion to
perhaps take a different tack.
A brief look a the execution plan shows one FTS and one FFS.
I'm wondering if there are tables in that query that change infrequently,
hopefully those that are incurring the FTS and FFS.
If so, you might consider breaking this up in to 2 or more MV's so that
the fast refresh MV does not have so much work to do. Some of the tables
may not need to be involved in every refresh. Then some of the
work could be pushed off to the MV queries, perhaps by using a view.
Just a thought.
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Other related posts:
