Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id CF06D1002E56AA for ; Tue, 2 Feb 2021 18:06:45 +0100 (CET) Received: from turing.freelists.org (ip-10-0-0-164.ec2.internal [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id F192240F4C; Tue, 2 Feb 2021 17:06:40 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id E650B3F85E; Tue, 2 Feb 2021 17:06:40 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612285600; bh=f6qzajWdkGTo++PI/K0Z2rzv8OLGNWQcO0su/PWmggE=; h=From:Sender:Sender:From; b=ufUSy+z4HJ4xYCcj+2ZUWwSQ7N3xlG+SmYzo74+SfmsYniFrOd0h0ot+UsV0nosxQ mJhcgYSWDL15/sZql19b0244SyLAL23ufDyRNPo5Xmt8hY1av++OMf/DMK0QcJN6uI C+L/fmNd2o63+zmba/Hp9W/eoljEAA/mvQLZhsQI= X-Virus-Scanned: by FreeLists at turing2.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id puhb0RAcySmI; Tue, 2 Feb 2021 17:06:40 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 7FA903F96B; Tue, 2 Feb 2021 17:06:38 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612285599; bh=f6qzajWdkGTo++PI/K0Z2rzv8OLGNWQcO0su/PWmggE=; h=From:Sender:Sender:From; b=GP4M3ggHwRVUEf3MV9OmJrY05M+jziSPcPDEpH5lhE0mxoLNpGMJb010NbzUTD4dS O5VjEGXSMOT3NFkmtEKCLEDEIa1l4zFZu4MId9sJC9LvBmh6euy9FGeSkL3JO7JNDE Z0Nbvx+3OHaRK6wa0jiqybCo4a6qcKGIa5Tgx0JQ= Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 02 Feb 2021 17:06:37 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id EF8073F85E for ; Tue, 2 Feb 2021 17:06:36 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.a=rsa-sha256 header.s=20161025 header.b=CcTl+sgp; dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id XrBwcULCRBAF for ; Tue, 2 Feb 2021 17:06:36 +0000 (UTC) Received: from mail-qk1-f177.google.com (mail-qk1-f177.google.com [209.85.222.177]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id CE54C3F844 for ; Tue, 2 Feb 2021 17:06:36 +0000 (UTC) Received: by mail-qk1-f177.google.com with SMTP id k193so20468811qke.6 for ; Tue, 02 Feb 2021 09:06:36 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to; bh=8LalNlGZVSY3Xnt6R36T0NBPlm76tjbhsp4z1et2xhU=; b=OVUwI8DWu3cJd3DX8A7qCtc8myJ9X93TsqsI/cZf+seTnV4Ma+iDDewrUL8tgrhHlI RBq0rYDsIvmdbRF23BjLJBe7G3KPndEuPo7L61Nst4RIVOrXVcgy0pjV0iTkhIGSV1jV i27b42DYKdhhov5ivDtPkdb2yRSgKV4OVMhOjDrRHP0VmaHUWZY56wC/HWHpq08YRchR +O0zLHXhBJLn/cexstAPJ7nqV92WSPWsEgzctdF+Y6r+Y/0iWzkkGF3uKpFiw0JetHIY WdqVbouOQDmaoQqgpUxLS5H110YlCvd2KtDFmPg/4mClTXayU0ahyaKOzTQkOQEtvczE kpuw== X-Gm-Message-State: AOAM532QgAU7YTmJkuVP8ZiWhvgrqTQNxsvMS5wboXPCtjpyA2aeLsti tLRyxX0FosDT+arFORJYpLPeKT2LgNd0JMBJ7wr/xHlKXXo= X-Google-Smtp-Source: ABdhPJwq26bcrOsgHOUvkzGe8Zfee7I6IVe96VtCyLxJNHlfbLbY1WerAz1WcSzCGsacBnQlsCrRexUNcVyXGo6xjLg= X-Received: by 2002:a05:620a:12dc:: with SMTP id e28mr21580730qkl.151.1612285596401; Tue, 02 Feb 2021 09:06:36 -0800 (PST) MIME-Version: 1.0 References: <2de601d6f96e$f9eac600$edc05200$@rsiz.com> <03C0F72B-4FA9-4F73-967D-9D10C3CD724C@yahoo.com> In-Reply-To: <03C0F72B-4FA9-4F73-967D-9D10C3CD724C@yahoo.com> From: Jonathan Lewis Date: Tue, 2 Feb 2021 17:06:24 +0000 Message-ID: Subject: Re: [External] : Re: Question on gathering System Statistics To: ORACLE-L Content-Type: multipart/alternative; boundary="0000000000004017bd05ba5d7e6a" X-archive-position: 78834 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: jlewisoracle@gmail.com Precedence: normal Reply-To: jlewisoracle@gmail.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --0000000000004017bd05ba5d7e6a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 =3D 10ms, throughput 4096 bytes per ms. (Oracle i= s till in the 1970's!) Single block read time =3D 12ms (=3D 10 + 2) 128 block read time =3D 266 ms ( =3D 10 + 256) If I tell the truth e.g. seek time =3D 1 ms, throughput =3D 100MB/s Single block read time =3D 1.078 ms 128 block read time =3D 11ms So perfect stats =3D> multiblock read is 10 times as fast as single block r= ead No stats =3D> 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 =3D 128" and "throughput =3D 200MB/s". (On my VM it behaves a little strang= ely 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 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 =E2=80=9CEXADATA=E2=80=9D option on and on an Exadata used prim= arily for OLTP, it > is known to just delete system stats. Having system stats collected with > =E2=80=9CEXADATA=E2=80=9D on an OLTP, will cause smartscans to be favored= and that can > cause issues for a very busy OLTP system. > --- > > Thanks, > > > Shane Borden > sborden76@yahoo.com > > > On Feb 2, 2021, at 10:48 AM, Andrew Kerber > 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 r= un > slower, and I haven't seen gathering system stats improve performance. > > On Tue, Feb 2, 2021 at 8:24 AM Mark W. Farnham wrote: > >> In an ideal world the CBO would be able to sample from a rolling snapsho= t >> of current system statistics collected lightly against the current >> background load without being a significant load themselves and plans wo= uld >> be adjusted dynamically for that plan execution and perhaps some sort of >> =E2=80=9Cwithin tolerance=E2=80=9D 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=E2=80=99d 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 cas= es >> either because it falls through a crack in the CBO=E2=80=99s armor or be= cause it is >> so important or sizeable that only precise attention down to the bare me= tal >> fastest possible is required. >> >> >> >> SO, FOR NOW, don=E2=80=99t 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@freelists.org [mailto: >> oracle-l-bounce@freelists.org] *On Behalf Of *Mladen Gogala >> *Sent:* Tuesday, February 02, 2021 1:58 AM >> *To:* oracle-l@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 >> >> =E2=80=9CThat is correct. Its best not to gather system stats=E2=80=9D >> >> >> >> -- >> >> Mladen Gogala >> >> Database Consultant >> >> https://dbwhisperer.wordpress.com >> >> > > -- > Andrew W. Kerber > > 'If at first you dont succeed, dont take up skydiving.' > > > --0000000000004017bd05ba5d7e6a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

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,
Other= wise consider the following arithmetic:

Default st= ats: seek time =3D 10ms, throughput 4096 bytes per ms.=C2=A0 (Oracle is til= l in the 1970's!)
Single block read time =3D 12ms=C2=A0 (=3D = 10 + 2)
128 block read time =3D 266 ms ( =3D 10 + 256)
<= div>
If I tell the truth e.g. seek time =3D 1 ms, throughput = =3D 100MB/s
Single block read time =3D 1.078 ms
12= 8 block read time =3D 11ms

So perfect stats =3D>= ; multiblock read is 10 times as fast as single block read
No sta= ts =3D> multiblock read is 22 times as fast as single block read

So the error is roughly a factor of 2.=C2=A0 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 b= e, and that's why the "'EXADATA' "gather" set th= e stats to "MBRC =3D 128" and "throughput =3D 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 ba= sis 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 syste= m statistic called MBRC) is 8.=C2=A0 If I use 8 instead of 128 in the arith= metic we get the following ratios for singe to multi in the optimizer calcs= :

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

E= rror factor is even less.

Regards
Jonath= an Lewis




On Tue, 2 Feb 20= 21 at 16:37, Shane Borden <dmarc-noreply@freelists.org> wrote:
I co= ncur.=C2=A0 The only time I have noticed a big difference is on an Exadata = platform that is used for OLTP vs DW.=C2=A0 On a DW you collect system stat= s using the =E2=80=9CEXADATA=E2=80=9D option on and on an Exadata used prim= arily for OLTP, it is known to just delete system stats.=C2=A0 Having syste= m stats collected with =E2=80=9CEXADATA=E2=80=9D on an OLTP, will cause sma= rtscans to be favored and that can cause issues for a very busy OLTP system= .
---

Thanks,


Shane Borden
sborden76@yahoo.com


On Feb 2, 2021, at 10:48 AM, Andrew= Kerber <an= drew.kerber@gmail.com> wrote:

I have = been following this thread with interest.=C2=A0 And here is my 2 cents wort= h.=C2=A0 May be worth less than that actually in this discussion. I have co= llected system stats on AIX, multiple linux systems, HPUX, etc from the tim= e Oracle first suggested collecting them.=C2=A0 I have yet to see them make= any significant difference in performance.=C2=A0 I am not saying it never = happens, but I have yet to see them make any measurable difference in perfo= rmance. I haven't seen a lack of system statistics cause things to run = slower, and I haven't seen gathering system stats improve performance.= =C2=A0

On Tue, Feb 2, 2021 at 8:24 AM Mark W. Farnham <mwf@rsiz.com> wrote:
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">
<= p class=3D"MsoNormal">In an ideal world the= CBO would be able to sample from a rolling snapshot of current system stat= istics collected lightly against the current background load without being = a significant load themselves and plans would be adjusted dynamically for t= hat plan execution and perhaps some sort of =E2=80=9Cwithin tolerance=E2=80= =9D could be checked on soft parses.

=C2=A0

For the reasons you men= tioned in the thread there are logical reasons to collect the system statis= tics.

=C2=A0

BUT having seen a wide range of the result of the curr= ently actually implemented CBO versus queries with canned defaults versus l= ocally collected statistics I=E2=80=99d go with Maria.=

=C2=A0=

You m= ay have specific cases where careful collection (or punching to specific va= lues) tends to create better plans for that particular case.

The GO= AL is to get good plans, and to get good enough plans for most plans so tha= t special attention is needed only for a small number of cases either becau= se it falls through a crack in the CBO=E2=80=99s armor or because it is so = important or sizeable that only precise attention down to the bare metal fa= stest possible is required.

= =C2=A0

SO, FOR NOW, don=E2=80=99t colle= ct the system statistics unless you can show in your lab for a particular c= ase that it improves things net-net by a big enough margin to care.<= u>

= =C2=A0

mwf

=C2=A0

From: oracle-l-bounce@freelists.org [mailto:= oracle-l= -bounce@freelists.org] On Behalf Of Mladen Gogala
Sent: Tuesday, February 02, 2021 1:58 AM
To: oracle-l@freelists.org
Subjec= t: Re: [External] : Re: Question on gathering System Statistics<= u>

=C2=A0

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 1= 2:09 PM, Jeff Smith wrote:

Maria confirms

=E2=80= =9CThat is correct. Its best not to g= ather system stats=E2=80=9D

= =C2=A0

-- 
Mlade=
n Gogala
Database Consultant
https://= dbwhisperer.wordpress.com
<= /div>

--
Andrew W. Kerber

= 'If at first you dont succeed, dont take up skydiving.'

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