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?
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.
Baselines, fixed windows and moving windows are described in oracle11
performance tuning manual.

On Wed, 29 Aug 2007 05:25:38 -0400, Mladen Gogala <mgogala@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?

That info is probably located in 'Oracle 11g, a family of products'
so no need to ask here.
Apart from that, who is using 11g?

--
Sybrand Bakker
Senior Oracle DBA

In article <2ifad3davqo3jiard9revtjf97sc33llks@4ax.com>,
sybrandb@hccnet.nl says...
> Apart from that, who is using 11g?
>
>

Apparently, I am using it. I'm not using it for production, but it is
installed and investigated when the time allows. Does it make me fat? Is
it illegal or immoral? It should have been named 10.3 as there aren't as
many new things as expected but it is still worth investigating.

On Aug 29, 5:25 am, Mladen Gogala <mgog...@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.

On Aug 29, 7:56 am, hpuxrac <johnbhur...@sbcglobal.net> wrote:
> On Aug 29, 5:25 am, Mladen Gogala <mgog...@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 --

In article <1188388608.487786.253110@w3g2000hsg.googlegroups. com>,
johnbhurley@sbcglobal.net says...
> Hacking up some triggers and putting some low level traces on is
> another way since you are just testing.
>
>
No need for triggers. DBMS_SCHEDULER.RUN_JOB has two arguments: job name
and "run in current session". Simply, set up 10046 trace, level 12 and
run the job with the second argument set to TRUE. Of course, nothing
will show up, as the package text is encrypted, so the arguments to the
DBMS_STATS will not be visible.

In article <1188394506.879960.34390@o80g2000hse.googlegroups. com>,
Mark.Powell@eds.com says...
> A trace of the new statistics update process might show you if a call
> to dbms_stats is made.
>
>

It didn't show me that, but it did show me that the "counting" SQL is
being executed. Also, in addition to the normal .trc file, the dump
directory now contains an additional, much smaller file called .trm:
[oracle@oracle12 trace]$ ls -l *BSLN*
-rw-r----- 1 oracle oinstall 460056 Aug 29 11:51 11G_ora_18564_BSLN.trc
-rw-r----- 1 oracle oinstall 281 Aug 29 11:51 11G_ora_18564_BSLN.trm
[oracle@oracle12 trace]$

This file is readable by tkprof and produces an output like this:
TKPROF: Release 11.1.0.6.0 - Production on Wed Aug 29 12:03:31 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Trace file: 11G_ora_18564_BSLN.trm
Sort options: default

************************************************** **********************
********
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
************************************************** **********************
********
Trace file: 11G_ora_18564_BSLN.trm
Trace file compatibility: 10.01.00
Sort options: default

0 session in tracefile.
0 user SQL statements in trace file.
0 internal SQL statements in trace file.
0 SQL statements in trace file.
0 unique SQL statements in trace file.
23 lines in trace file.
0 elapsed seconds in trace file.

It looks like some kind of advanced session/module tracking mechanism
for tkprof/trcsess.

The normal .trc file produces the normal output:
KPROF: Release 11.1.0.6.0 - Production on Wed Aug 29 12:06:27 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Trace file: 11G_ora_18564_BSLN.trc
Sort options: default

************************************************** **********************
********
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
************************************************** **********************
********

SQL ID : 6743x3tw15hc6
BEGIN dbms_scheduler.run_job('BSLN_MAINTAIN_STATS_JOB',T RUE); END;


BTW, it gives you SQL ID with every SQL in the trace file. Oracle10 did
not do that.