Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Any suggestions on speeding this MView query up?

Re: Any suggestions on speeding this MView query up?

From: Jared Still <jkstill_at_gmail.com>
Date: Thu, 8 Feb 2007 09:34:59 -0800
Message-ID: <bf46380702080934s1b0a0749oaefcaa75a4f56063@mail.gmail.com>


On 2/7/07, Robert Freeman <robertgfreeman_at_yahoo.com> 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

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Feb 08 2007 - 11:34:59 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US