Re: Oracle11 & statistics calculations

On Aug 29, 7:56 am, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On Aug 29, 5:25 am, Mladen Gogala <mgog..._at_yahoo.com> wrote:
>
> > Oracle11 has some new additions to the mechanism of statistics
> > calculation. The old GATHER_STATS_JOB is gone, it is replaced by
> > BSLN_MAINTAIN_STATS_JOB which, unfortunately, doesn't run DBMS_STATS, it
> > uses an undocumented package DBSNMP.BSLN_INTERNAL which maintains
> > baselines for the AWR. I have to questions:
> > 1) Is AWR in version 11G still a licensed product that has to be paid
> > for? Do we still have to spend money on statspack on steroids?
>
> There's some new doc on licensing and some changes in the options.
> The short answer is I believe yes if you want to use it you should be
> paying for it.
>
> > 2) My assumption is that BSLN_INTERNAL still calls
> > DBMS_STATS.GATHER_DATABASE_STATS(options=>'GATHER AUTO'), just as
> > GATHER_STATS_JOB used to do but I cannot prove it. Any ideas as
> > how to check that? If my assumption is correct, then the oracle11
> > job should be disabled, just the way oracle10 job was disabled
> > immediately after the installation was complete.
>
> There's probably a bunch of different ways to prove it but one
> approach that is supported would involve opening a service request
> with oracle on your question.
>
> Hacking up some triggers and putting some low level traces on is
> another way since you are just testing.
>
>
>
> > Baselines, fixed windows and moving windows are described in oracle11
> > performance tuning manual.- Hide quoted text -
>
> - Show quoted text -

Yes, the AWR is still an extra cost item that is on by default. There is however a new database parameter, control_management_pack_access, that stops collecting AWR stats but does not stop collecting stats related to free features such as segment space management so mmon still runs.

A trace of the new statistics update process might show you if a call to dbms_stats is made.

HTH -- Mark D Powell -- Received on Wed Aug 29 2007 - 08:35:06 CDT