Re: GATHER_STATS_JOB behavior changed between 10g and 11g
Date: Thu, 27 Aug 2009 12:41:59 +0100
Message-ID: <5e317f310908270441y2690ad15k540fdbdd767ed434_at_mail.gmail.com>
Hi Chen,
Reading MetaLink about this issue, I would say the answer is "no", a couple of bugs have been logged around this and the one specifically regarding the automatic job (5411956) is labelled "Not Feasible to fix, to Filer"! The only fix appears to be to refresh the MVs at a different time. My only other thought would be to get the source for the automatic stats job from your 10G version and re-create the required package or function on the 11G database. But as I am sure you realise this would be a tricky route to follow, even if you could make it work.
Regards
Pete
On Thu, Aug 27, 2009 at 1:30 AM, Chen Shapira <cshapi_at_gmail.com> wrote:
> Hi Oracle-Listers,
>
> We have this system where materialized views are scheduled to rebuild
> within the same maintenance window in which Oracle runs the automatic
> GATHER_STATS_JOB.
> Not the best idea, but in 10.2.0.2 it did not cause any issues.
>
> After upgrade to 11g, GATHER_STATS_JOB causes "ORA-00054: resource
> busy and acquire with NOWAIT specified or timeout expired" messages in
> our alert log.
> It runs much faster though :)
>
> I can completely understand the new behavior - why waste time (and
> possibly hang the stats gathering job) when you can just skip
> resources. However, we'd rather have the old behavior back.
>
> Anyone knows how to get it back?
>
> Thanks,
> Chen Shapira
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- Regards Pete -- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 27 2009 - 06:41:59 CDT