Slow refresh mview

#1

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

#2

In message <4d10aa12$0$1588$426a74cc (AT) news (DOT) free.fr>, bob123
<nomail (AT) nowhere (DOT) com> writes

Quote:

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

Do you have materialized view logs on the underlying tables ?, what sort
of refresh are you doing (fast or complete) ?

Adrian
--
To Reply :
replace "bulleid" with "adrian" - all mail to bulleid is rejected
Sorry for the rigmarole, If I want spam, I'll go to the shops
Every time someone says "I don't believe in trolls", another one dies.

#3

On Dec 21, 8:22*am, "bob123" <nom... (AT) nowhere (DOT) com> wrote:

Quote:

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.

#4

On Tue, 21 Dec 2010 13:08:19 -0800, Steve Howard wrote:

Quote:

On Dec 21, 8:22Â*am, "bob123" <nom... (AT) nowhere (DOT) 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.

#5

On Dec 21, 4:19*pm, HoneyMonster <some... (AT) someplace (DOT) invalid> wrote:

Quote:

On Tue, 21 Dec 2010 13:08:19 -0800, Steve Howard wrote:
On Dec 21, 8:22*am, "bob123" <nom... (AT) nowhere (DOT) 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).

#6

On Tue, 21 Dec 2010 16:51:57 -0800, onedbguru wrote:

Quote:

On Dec 21, 4:19Â*pm, HoneyMonster <some... (AT) someplace (DOT) invalid> wrote:
On Tue, 21 Dec 2010 13:08:19 -0800, Steve Howard wrote:
On Dec 21, 8:22Â*am, "bob123" <nom... (AT) nowhere (DOT) 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.

#7

Quote:

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.

OK thanks
I 9iR2 what atomic_refresh I have to set
to get a truncate instead of a delete ?

#8

Quote:

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.

OK thanks in 9iR2
What option for atomic_refresh I have to set
to get a truncate instead of a delete ?

#9

On Dec 21, 9:57*pm, HoneyMonster <some... (AT) someplace (DOT) invalid> wrote:

Quote:

On Tue, 21 Dec 2010 16:51:57 -0800, onedbguru wrote:
On Dec 21, 4:19*pm, HoneyMonster <some... (AT) someplace (DOT) invalid> wrote:
On Tue, 21 Dec 2010 13:08:19 -0800, Steve Howard wrote:
On Dec 21, 8:22*am, "bob123" <nom... (AT) nowhere (DOT) 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...