Re: MV refresh
- From: Roger Xu <wellmetus@xxxxxxxxx>
- To: Michael Fontana <michael.fontana@xxxxxxxxxxx>
- Date: Wed, 2 Sep 2009 13:14:34 -0500
As Brandon pointed out, in 10g, a complete refresh of a single MV does a
delete and insert on the MV table. With indexes on, it comsumes more time
and resource. They asked me to create 11 indexes. After carefully analysis,
they were reduced to 6 indexes but instead of 5 minutes, it takes 15 minutes
to refresh the same view now. I will have to drop indexes and recreate them
after the refresh. Thanks.
On Wed, Sep 2, 2009 at 12:06 PM, Michael Fontana <
michael.fontana@xxxxxxxxxxx> wrote:
> Roger,
>
> Once again, defining the goal is the key.
>
> If you're doing COMPLETE refreshes all the time, it might make sense to
> drop and recreate the indexes if you get better execution times, but I doubt
> it would be THAT much better. Test out the theory.
>
> I once worked on a system where people automatically put the same indices
> on the MV as were on the source table, when, in fact, the reporting
> environment where the MV was running had decidedly different queries running
> against it. So they were not applicable. I would be really surprised if
> all of the exact same indexes are required in both environments.
>
> Are they, really? Or is someone simply assuming they must be?
>
> ----- Original Message -----
> From: "Roger Xu" <wellmetus@xxxxxxxxx>
> To: oracle-l@xxxxxxxxxxxxx
> Sent: Wednesday, September 2, 2009 11:50:23 AM GMT -06:00 US/Canada Central
> Subject: Re: MV refresh
>
>
> Hello again,
>
> I created a MV and create some indexes on the MV. Now the same refresh
> takes much longer.
> Is there an easy way to defer the index building until the MV refresh is
> done?
>
> Otherwise, I would have to:
>
> 1) drop indexes;
> 2) execute DBMS_MVIEW.refresh('PAYMENT','C');
> 3) create indexes;
>
> Also, what is the right way to collect statistics after MV refresh?
>
> Thanks,
>
> Roger Xu
>
>
> :
>
>
>
>
>
>
>
>
>
> On Thu, Aug 27, 2009 at 3:58 PM, Allen, Brandon <
> Brandon.Allen@xxxxxxxxxxx > wrote:
>
>
>
> Not positive off the top of my head, but I think it’s possible for that to
> happen if you don’t use the atomic refresh setting because then it does a
> truncate first, which is a DDL transaction, followed by the inserts, however
> if you use the atomic refresh, then it will keep the whole refresh together
> as a single transaction, which means it uses DELETE instead of TRUNCATE, so
> it’s slower and generates significantly more redo. If I recall correctly
> there was also a change related to this functionality between 9i and 10g,
> but I forget the details – just search for atomic refresh on Metalink and
> I’m sure you’ll find it. Regarding capturing the error message, just
> redirect the output of your refresh script to a log file and then run a
> check on that log file to see if there are errors or not.
>
>
>
> Regards,
>
> Brandon
>
>
>
> On Thu, Aug 27, 2009 at 3:50 PM, Roger Xu < wellmetus@xxxxxxxxx > wrote:
>
>
>
> Hi MV gurus,
>
> Is it possible that when a MV is being refreshed but gets some kind of
> error and ends up an empty MV in 10g?
>
> exec DBMS_MVIEW.refresh('MYMV','C'); - here is how we refresh.
>
> The application team wants me to write a procedure to refresh MV and
> capture the error message if it fails and restore the MV to the previous
> state. How do I capture the error? I know I can show errors in SQL*PLUS.
>
> Thanks,
>
> Roger Xu
>
> --
>
>
>
>
>
>
>
>
> Privileged/Confidential Information may be contained in this message or
> attachments hereto. Please advise immediately if you or your employer do not
> consent to Internet email for messages of this kind. Opinions, conclusions
> and other information in this message that do not relate to the official
> business of this company shall be understood as neither given nor endorsed
> by it.
>
>
>
>
>
>
>
>
>
>
>
>
>
> Click here to learn more about Dr Pepper Snapple Group’s commitment to
> corporate social responsibility.
>
> Please be conscious of the environment and print this email only if
> absolutely necessary.
>
> This e-mail (including any attachments) is confidential and may contain
> privileged information of Dr Pepper Snapple Group, Inc. and/or its
> subsidiaries ("Dr Pepper Snapple Group"). If you are not the intended
> recipient or receive it in error, you may not use, distribute, disclose or
> copy any of the information contained within it and it may be unlawful to do
> so. If you are not the intended recipient, please notify us immediately by
> returning this e-mail to us at mailerror@xxxxxxxx and destroy all copies.
> Any views expressed by individuals within this e-mail do not necessarily
> reflect the views of Dr Pepper Snapple Group. This e-mail does not
> constitute a binding offer, acceptance, amendment, waiver or other
> agreement, unless the intent that an e-mail will constitute such is clearly
> stated in the body of the email. Recipients are advised to subject this
> e-mail and attachments to their own virus checking, in keeping with good
> computing practice. Please note that e-mail received by Dr Pepper Snapple
> Group may be monitored in accordance with applicable law.
>
> --
>
>
>
>
>
>
> Michael Fontana
>
> Sr. Technical Consultant
>
> Enkitec M: 214.912.3709
>
> enkitec
>
> oracle_certified_partner
>
>
>
>
>
Other related posts:
