Oracle11 & statistics calculations
in [Oracle]
|
Prev: what characterset to use?
Next: ORA-04031: unable to allocate 2097152 bytes of shared memory("shared pool","unknown object","hash-join subh","QERHJ Hash Table Entries")
From: Mladen Gogala on 29 Aug 2007 05:25 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.
From: sybrandb on 29 Aug 2007 05:38 On Wed, 29 Aug 2007 05:25:38 -0400, Mladen Gogala <mgogala(a)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
From: Mladen Gogala on 29 Aug 2007 06:09 In article <2ifad3davqo3jiard9revtjf97sc33llks(a)4ax.com>, sybrandb(a)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.
From: hpuxrac on 29 Aug 2007 07:56 On Aug 29, 5:25 am, Mladen Gogala <mgog...(a)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.
From: Mark D Powell on 29 Aug 2007 09:35
On Aug 29, 7:56 am, hpuxrac <johnbhur...(a)sbcglobal.net> wrote: > On Aug 29, 5:25 am, Mladen Gogala <mgog...(a)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 -- |