I agree with Mladen here. It's incredibly annoying that Oracle's official
stance is to leave the vales at the default now.
That just tells me that Oracle Corp has lost focus on how the optimizer
should perform and has become unwieldy.
Mathematically, it makes sense to always gather system stats to aid the
optimizer (where always is do it once during load).
The fact they're saying don't do that is a clear indicator of a problem in
the design/implementation of the calculations.
Just my $0.015
Chris
On Mon, Mar 25, 2019, 8:11 PM Mladen Gogala <gogala.mladen@xxxxxxxxx> wrote:
System statistics is calibration. I measures your CPU speed and your IO
speed. That's it. Generally speaking, your performance can benefit greatly
from the information about the particular system it is running. There is no
need to re-run it, unless there is a change in your environment: new
motherboard, new memory, new storage or new PHB. Here is the data that is
collected when system statistics is gathered:
SQL> select * from aux_stats$;
SNAME PNAME PVAL1 PVAL2
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 03-25-2019 19:40
SYSSTATS_INFO DSTOP 03-25-2019 19:45
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 3254.90196078431
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 16.012
SYSSTATS_MAIN MREADTIM 29.361
SYSSTATS_MAIN CPUSPEED 4061
SYSSTATS_MAIN MBRC 11
SYSSTATS_MAIN MAXTHR 86223872
SYSSTATS_MAIN SLAVETHR 206848
13 rows selected.
As you can see, it describes your system: CPU speed, IO seek time and
transfer speed, single block read time, multiple blocks read time and the
measured optimal value for multi-block read count. Jonathan Lewis has
written quite a few very instructive articles about particular values in
this table.
Having said that, it is completely ridiculous to measure system statistics
without any activity. You want the values which would correspond to your
typical workload and that cannot be measured on an idle system. As for the
object statistics, there is a serious question whether it is better to
collect statistics regularly of use dynamic sampling, level 11 which would
also udate the statistics tables. Unfortunately, not being a DBA any longer
means that I cannot test. This suggestion should be tested, ideally in a
development or test systems. You probably don't want to be the most
interesting DBA in the world and do your testing in production. There is an
old parody of Dos Equis commercial which says "I don't test often, but when
I do, I do it in production". Testing in production never ends well, no
matter how interesting the DBA.
Regards
On 3/25/19 12:56 AM, Cee Pee wrote:
List,
I was reading up on system stats and came across this link:
https://blogs.oracle.com/optimizer/should-you-gather-system-statistics
Here are some of the things the author says:
1. "if you are at a decision point and you need to choose whether to
gather them or not, then in most cases you should use the defaults and *not
*gather system statistics."
Doesnt setting systems help a lot these days esp with faster IO devices.
Do the listers collect system stats in your environments, test. prod, etc?
2. "there is at least *some *management or procedural overhead required
to maintain them"
'Maintaining' stats? I thought once we set the system stats we leave it
out there forever without touching it?
Thanks all,
CP
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217