Re: [External] : Re: Question on gathering System Statistics

Really that Exadata (or other massive DW machine) vs. OLTP system is the most significant point for System stats. If you haven't collected system stats Oracle seems to check your CPU speed on every startup and store it as CPUSPEEDNW, Otherwise consider the following arithmetic:

Default stats: seek time = 10ms, throughput 4096 bytes per ms. (Oracle is till in the 1970's!)
Single block read time = 12ms (= 10 + 2) 128 block read time = 266 ms ( = 10 + 256)

If I tell the truth e.g. seek time = 1 ms, throughput = 100MB/s Single block read time = 1.078 ms
128 block read time = 11ms

So perfect stats => multiblock read is 10 times as fast as single block read No stats => multiblock read is 22 times as fast as single block read

So the error is roughly a factor of 2. How often is the cardinality estimate that close ?

It can help a bit if you have a pretty good idea of what most of the I/O is going to be, and that's why the "'EXADATA' "gather" set the stats to "MBRC = 128" and "throughput = 200MB/s". (On my VM it behaves a little strangely with seek time - it's either 1 or 10ms if I re-run).

I've used 128 as the basis for the multiblock read time because a lot of people still seem to set the db_file_mulitblock_read_count parameter to that value. If you don't set it the default value used by Oracle (which gets reflected in the system statistic called MBRC) is 8. If I use 8 instead of 128 in the arithmetic we get the following ratios for singe to multi in the optimizer calcs:

Default: 12 / 26 -> 2.167
Truth: 1.078 / 1.6 -> 1.48

Error factor is even less.

Regards
Jonathan Lewis

On Tue, 2 Feb 2021 at 16:37, Shane Borden <dmarc-noreply_at_freelists.org> wrote:

> I concur. The only time I have noticed a big difference is on an Exadata
> platform that is used for OLTP vs DW. On a DW you collect system stats
> using the “EXADATA” option on and on an Exadata used primarily for OLTP, it
> is known to just delete system stats. Having system stats collected with
> “EXADATA” on an OLTP, will cause smartscans to be favored and that can
> cause issues for a very busy OLTP system.
> ---
>
> Thanks,
>
>
> Shane Borden
> sborden76_at_yahoo.com
>
>
> On Feb 2, 2021, at 10:48 AM, Andrew Kerber <andrew.kerber_at_gmail.com>
> wrote:
>
> I have been following this thread with interest. And here is my 2 cents
> worth. May be worth less than that actually in this discussion. I have
> collected system stats on AIX, multiple linux systems, HPUX, etc from the
> time Oracle first suggested collecting them. I have yet to see them make
> any significant difference in performance. I am not saying it never
> happens, but I have yet to see them make any measurable difference in
> performance. I haven't seen a lack of system statistics cause things to run
> slower, and I haven't seen gathering system stats improve performance.
>
> On Tue, Feb 2, 2021 at 8:24 AM Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
>> In an ideal world the CBO would be able to sample from a rolling snapshot
>> of current system statistics collected lightly against the current
>> background load without being a significant load themselves and plans would
>> be adjusted dynamically for that plan execution and perhaps some sort of
>> “within tolerance” could be checked on soft parses.
>>
>>
>>
>> For the reasons you mentioned in the thread there are logical reasons to
>> collect the system statistics.
>>
>>
>>
>> BUT having seen a wide range of the result of the currently actually
>> implemented CBO versus queries with canned defaults versus locally
>> collected statistics I’d go with Maria.
>>
>>
>>
>> You may have specific cases where careful collection (or punching to
>> specific values) tends to create better plans for that particular case.
>>
>> The GOAL is to get good plans, and to get good enough plans for most
>> plans so that special attention is needed only for a small number of cases
>> either because it falls through a crack in the CBO’s armor or because it is
>> so important or sizeable that only precise attention down to the bare metal
>> fastest possible is required.
>>
>>
>>
>> SO, FOR NOW, don’t collect the system statistics unless you can show in
>> your lab for a particular case that it improves things net-net by a big
>> enough margin to care.
>>
>>
>>
>> mwf
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Mladen Gogala
>> *Sent:* Tuesday, February 02, 2021 1:58 AM
>> *To:* oracle-l_at_freelists.org
>> *Subject:* Re: [External] : Re: Question on gathering System Statistics
>>
>>
>>
>> Any explanation? This looks a bit counter-intuitive to me. Would it be
>> possible to persuade to come here and explain the recommendation?
>>
>> Regards
>>
>> On 2/1/21 12:09 PM, Jeff Smith wrote:
>>
>> Maria confirms
>>
>> “That is correct. Its best not to gather system stats”
>>
>>
>>
>> --
>>
>> Mladen Gogala
>>
>> Database Consultant
>>
>> https://dbwhisperer.wordpress.com
>>
>>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>
>
>

--
http://www.freelists.org/webpage/oracle-l