Re: Slow refresh mview
From: onedbguru <onedbguru_at_yahoo.com>
Date: Sat, 25 Dec 2010 08:38:21 -0800 (PST)
Message-ID: <dbff7443-3a55-412c-905f-cf2b176c0f5d_at_32g2000yqz.googlegroups.com>
On Dec 21, 9:57 pm, HoneyMonster <some..._at_someplace.invalid> wrote:
> On Tue, 21 Dec 2010 16:51:57 -0800, onedbguru wrote:
> > On Dec 21, 4:19 pm, HoneyMonster <some..._at_someplace.invalid> wrote:
> >> On Tue, 21 Dec 2010 13:08:19 -0800, Steve Howard wrote:
> >> > On Dec 21, 8:22 am, "bob123" <nom..._at_nowhere.com> wrote:
> >> >> Hi,
>
> >> >> I have some Mviews for query rewrite
> >> >> it takes 15-20 minutes to create them The refresh takes more than 5
> >> >> hours ...
>
> >> >> why ?
> >> >> What can I do to speed this ?
>
> >> >> Oracle 9.2.0.7
>
> >> >> Thanks in advance
>
> >> > In addition to Adrian's comments, in 10g and above you can do a "non-
> >> > atomic" refresh that truncates the materialized view (rather than
> >> > deleting the rows) under the covers. This is much faster provided
> >> > you have your query rewrite stuff set up correctly.
>
> >> IIRC 9i would do that too on a full refresh, provided you didn't make
> >> the snapshot^H^H^H mview part of a group.
>
> > It is a real shame that you are subjected to using such old technology.
> > While it may work, the amount of time you can save doing mundane DBA
> > tasks would more than pay for the upgrade to something a little more
> > current. Being that far behind will also subject you and your company
> > to jumping through lots of hoops to get current. Being that old also
> > subjects you to MANY security risks that have been corrected. Being
> > able to recover easily from failure is another huge benefit to
> > upgrading. I can't imagine what unpatched an vulnerable OS you are
> > running on... I do know that the 9i runInstaller will not even run on
> > some of the later OS versions - (Solaris, AIX, even Linux).
>
> You have responded to the wrong person.
Date: Sat, 25 Dec 2010 08:38:21 -0800 (PST)
Message-ID: <dbff7443-3a55-412c-905f-cf2b176c0f5d_at_32g2000yqz.googlegroups.com>
On Dec 21, 9:57 pm, HoneyMonster <some..._at_someplace.invalid> wrote:
> On Tue, 21 Dec 2010 16:51:57 -0800, onedbguru wrote:
> > On Dec 21, 4:19 pm, HoneyMonster <some..._at_someplace.invalid> wrote:
> >> On Tue, 21 Dec 2010 13:08:19 -0800, Steve Howard wrote:
> >> > On Dec 21, 8:22 am, "bob123" <nom..._at_nowhere.com> wrote:
> >> >> Hi,
>
> >> >> I have some Mviews for query rewrite
> >> >> it takes 15-20 minutes to create them The refresh takes more than 5
> >> >> hours ...
>
> >> >> why ?
> >> >> What can I do to speed this ?
>
> >> >> Oracle 9.2.0.7
>
> >> >> Thanks in advance
>
> >> > In addition to Adrian's comments, in 10g and above you can do a "non-
> >> > atomic" refresh that truncates the materialized view (rather than
> >> > deleting the rows) under the covers. This is much faster provided
> >> > you have your query rewrite stuff set up correctly.
>
> >> IIRC 9i would do that too on a full refresh, provided you didn't make
> >> the snapshot^H^H^H mview part of a group.
>
> > It is a real shame that you are subjected to using such old technology.
> > While it may work, the amount of time you can save doing mundane DBA
> > tasks would more than pay for the upgrade to something a little more
> > current. Being that far behind will also subject you and your company
> > to jumping through lots of hoops to get current. Being that old also
> > subjects you to MANY security risks that have been corrected. Being
> > able to recover easily from failure is another huge benefit to
> > upgrading. I can't imagine what unpatched an vulnerable OS you are
> > running on... I do know that the 9i runInstaller will not even run on
> > some of the later OS versions - (Solaris, AIX, even Linux).
>
> You have responded to the wrong person.
responded to the thread - not the person... sheesh... Received on Sat Dec 25 2010 - 10:38:21 CST