Re: System stats

As far as the workload, I used 2 workload stats and swapped between them - one for the day where the business hours and the off-business hours had their own personalities (for lack of a better word).

As far as the SAN goes, if enough systems are hitting the SAN enough to cause the IO rate/throughput to become affected, then its *probably* time for a new SAN.

And I agree about the optimizer being too complex - To me it seems, the new optimizer features that come about every release (or more) have become like "bolt ons" and just seems like its going the wrong direction.

Chris

On Tue, Mar 26, 2019 at 5:06 AM Neil Chandler <neil_chandler_at_hotmail.com> wrote:

> Chris,
>
> So what if you have a SAN and you’re not the only system using it. How
> representative are the stats you have just gathered? The accuracy will
> vary.
>
> What if the workload isn’t typical. What if your workload varies over the
> day? CPU’s are complex there days - especially in relation to hyper
> threading/SMT processing.
>
> If you have shared infrastructure, the defaults are your best bet for
> consistency. If you are a *pure* data warehouse (and those are rare) and
> you have dedicated resources, there is an argument for them.
>
> Also, think about Oracles official stance about new features.
>
> 1. New thing announced
> 2. You MUST use it. In fact it can be hard to switch off.
> 3. Real-world reality. It has problems
> 4. Oracle still blindly recommend and try to fix it
> 5. Years later with lots of feedback, Oracle still generally don’t change
> their minds. They have on this one.
>
> 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>
> 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>
> 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
>>
>>

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