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 07E0410034079A for ; Tue, 2 Feb 2021 16:49:17 +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 76FE344716; Tue, 2 Feb 2021 15:49:15 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 6C5C63F85E; Tue, 2 Feb 2021 15:49:15 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612280955; bh=BRw6DsUl/PIQkUFZUyNrniSHuwghb0sS/n/ofctHaOw=; h=From:Sender:Sender:From; b=EB+SdlVFkeyv2HMqHPNeEdvOU26+LB2JYxIzgqvBwvQ74jjUkegiPXmTwb1hT3xdk 4TPADnGGBK35UI2PIN0uck+Yf82P+6c/fpErKfaorzNglSq8nA/WbSauZJV043baSv YpoBDqEq/aS8hD0rAa1NM5YQf9apFubbbHYBGVy0= 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 YNIjcy98klkN; Tue, 2 Feb 2021 15:49:15 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 36AB83F96E; Tue, 2 Feb 2021 15:49:13 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612280954; bh=BRw6DsUl/PIQkUFZUyNrniSHuwghb0sS/n/ofctHaOw=; h=From:Sender:Sender:From; b=al+PYkDZ5P3yJaIj/RFge/f4snDeZA24mV0/YH5Fa4C8GBw1MYCtiB7rN7v+sKyl0 y0KYGy8t660D+iykU4Be9kd++qGD51xxjXUZnGVaNYCWqEv1ZUxjrYSezt/DMVn0DA G+2vtPtzbct0Amr9aYbefZZ8kmI0IKKklL4RfzPI= Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 02 Feb 2021 15:49:12 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 11A693F85E for ; Tue, 2 Feb 2021 15:49:12 +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=HC9kTDWx; 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 eRzjuqzw12NL for ; Tue, 2 Feb 2021 15:49:12 +0000 (UTC) Received: from mail-ej1-f47.google.com (mail-ej1-f47.google.com [209.85.218.47]) (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 000963F844 for ; Tue, 2 Feb 2021 15:49:11 +0000 (UTC) Received: by mail-ej1-f47.google.com with SMTP id r12so30675957ejb.9 for ; Tue, 02 Feb 2021 07:49:11 -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:cc; bh=m63okyeO1+AhKHYaL2V+rpUiGzGNB38DAB+VEYjV+Cw=; b=iSYd4aGc2VoVNCfrTUUiSdAdCPWqSlbL0622lx/xkejITISBjfwKl/Nu/NjVvc1Mas 2m4/csydOjve0k0ls62+ZSfx91ypBV7FJLbqIEuWhfPCoZPwDNCepIuFJLXRnfAGqvVR VfSaSR6wsGkdli2HfCuLHggQjUb7SNyyQqkMiiVT9DpXhlVruA0Rqf4/jja+AXEgjjJ3 9mmRob39bWWUzfz285ua2oAwu3zz5bn6OEr1HT/UEyJjheNYBESvcGEnIpLTWkSMso2L 6OquwEJgZ1MRt7XbJtzopiP5/5OGP/qef7fhVPRdpC0akYLUCESJi6DrW2ACb0p0LBJB vFsg== X-Gm-Message-State: AOAM531GqugJLI5lzMyg0XcxKxSOwywb+7hUcW14Y4CdD5ifmIsfOCA4 XfVboDQsJZEN2KlhWg8T3h5pnA6FeCtYuxDAnaU= X-Google-Smtp-Source: ABdhPJzxtvCUA3Sgx+bhZ/uDdHPvtWTpVjKam6md5jMzoEB1Vw8+yR+h3hIE0+tg56u1WJDs+0OcViRv+Wp71nF6Oik= X-Received: by 2002:a17:906:3883:: with SMTP id q3mr22472631ejd.160.1612280951034; Tue, 02 Feb 2021 07:49:11 -0800 (PST) MIME-Version: 1.0 References: <2de601d6f96e$f9eac600$edc05200$@rsiz.com> In-Reply-To: <2de601d6f96e$f9eac600$edc05200$@rsiz.com> From: Andrew Kerber Date: Tue, 2 Feb 2021 09:48:57 -0600 Message-ID: Subject: Re: [External] : Re: Question on gathering System Statistics To: "Mark W. Farnham" Cc: Mladen Gogala , ORACLE-L Content-Type: multipart/alternative; boundary="0000000000005d57e205ba5c698a" X-archive-position: 78830 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: andrew.kerber@gmail.com Precedence: normal Reply-To: andrew.kerber@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 --0000000000005d57e205ba5c698a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 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 wou= ld > 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 plan= s > so that special attention is needed only for a small number of cases eith= er > because 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 > fastest possible is required. > > > > SO, FOR NOW, don=E2=80=99t collect the system statistics unless you can s= how 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 > > --=20 Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.' --0000000000005d57e205ba5c698a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I have been following this thread with interest.=C2=A0 And= here is my 2 cents worth.=C2=A0 May be worth less than that actually in th= is discussion. I have collected system stats on AIX, multiple linux systems= , HPUX, etc from the time Oracle first suggested collecting them.=C2=A0 I h= ave 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 measur= able difference in performance. I haven't seen a lack of system statist= ics cause things to run slower, and I haven't seen gathering system sta= ts improve performance.=C2=A0

On Tue, Feb 2, 2021 at 8:24 AM Mark W. Fa= rnham <mwf@rsiz.com> wrote:
<= div class=3D"gmail-m_-1070219520828395756WordSection1">

In an ideal world the CBO would be able = to sample from a rolling snapshot of current system statistics collected li= ghtly 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 =E2=80=9Cwithin tolerance=E2=80=9D could be checke= d on soft parses.

=C2=A0

For the reasons you mentioned in the threa= d there are logical reasons to collect the system statistics.=

= =C2=A0

BUT having seen a wide range of the result of the currently actually im= plemented CBO versus queries with canned defaults versus locally collected = statistics I=E2=80=99d go with Maria.

=C2=A0

You may have specific = cases where careful collection (or punching to specific values) tends to cr= eate 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 attenti= on is needed only for a small number of cases either because it falls throu= gh a crack in the CBO=E2=80=99s armor or because it is so important or size= able that only precise attention down to the bare metal fastest possible is= required.

=C2=A0

SO, FOR NOW, don=E2=80=99t collect the system sta= tistics unless you can show in your lab for a particular case that it impro= ves things net-net by a big enough margin to care.

=

=C2=A0=

mwf

<= u>=C2=A0

F= rom: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelis= ts.org] On Behalf Of Mladen Gogala
Sent: Tuesday, Febr= uary 02, 2021 1:58 AM
To: oracle-l@freelists.org
Subject: Re: [Ext= ernal] : Re: Question on gathering System Statistics

=C2=A0

Any explana= tion? This looks a bit counter-intuitive to me. Would it be possible to per= suade to come here and explain the recommendation?

Rega= rds

On 2/1/21 12:09 PM, Jeff S= mith wrote:

Maria confirms
=E2=80=9CThat is correct. Its best n= ot to gather system stats=E2=80=9D

=C2=A0

--
Mladen Gogala
Database Consultant
ht=
tps://dbwhisperer.wordpress.com


--
Andrew W. Kerber

'If at first you dont succeed, dont tak= e up skydiving.'
--0000000000005d57e205ba5c698a-- -- http://www.freelists.org/webpage/oracle-l