Re: System stats

Think about this one... how long has it been an option. Oracle have changed their stance based upon years of feedback and reported problems.

Oh, and the optimiser is WAY too complex, and the (extremely smart) people coding it need to get out (into the real world) more.

Regards

Neil.
sent from my phone

On 26 Mar 2019, at 01:08, Chris Taylor <christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com>> wrote:

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_at_gmail.com<mailto:gogala.mladen_at_gmail.com>> 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: