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 CC048100382ED8 for ; Tue, 2 Feb 2021 15:24:50 +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 DA79445495; Tue, 2 Feb 2021 14:24:49 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id B9BA53F97B; Tue, 2 Feb 2021 14:24:49 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612275889; bh=xCyvPyaupY/5UqGRBLR+B12NAlP6JLaBeQ6au7D8LWk=; h=From:Sender:Sender:From; b=T/unVimfp7DePTDWGFqrN688ligAj+Ox/rNVZbj2ZiOqaPQnfEkj/4y2q/vxYUBBf 475BkZzWX60p4fXdGSRlQSRtp5/ACb98lCCfYftlHVQcljS48roBoVjG59y1bQ+/mt vNZbUm1vV2um8HTs1ZmENNr1kaE3r5iCsadlaOD8= 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 zttQrf4KNEMj; Tue, 2 Feb 2021 14:24:49 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 849663F96E; Tue, 2 Feb 2021 14:24:39 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612275880; bh=xCyvPyaupY/5UqGRBLR+B12NAlP6JLaBeQ6au7D8LWk=; h=From:Sender:Sender:From; b=ZTOrEjGfcXeF6d0Esord/Tlouvx/x+B3jJrdKAwuxpgFRJr00kUWRGE7oXL9YkK6r jVRaeY9Jj/4+J2l63fwWuDLQEfckFRECTcK4SGMZ2JXJY+MhB3CugR+xOdE3b0baOJ zV0XimKeOFo9Ygwju5wMAxLVUaklimAgDoDxU18M= Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 02 Feb 2021 14:24:37 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id C6CCF3F978 for ; Tue, 2 Feb 2021 14:24:36 +0000 (UTC) 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 molCCE_bcQX1 for ; Tue, 2 Feb 2021 14:24:36 +0000 (UTC) Received: from gw2.tidalhosting.net (gateway.tidalhosting.net [155.130.128.124]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id 159CA3F85E for ; Tue, 2 Feb 2021 14:24:01 +0000 (UTC) Received: from mrtr.tidalhosting.net (UnknownHost [10.0.150.3]) by gw2.tidalhosting.net with SMTP (version=TLS\Tls12 cipher=Aes256 bits=256); Tue, 2 Feb 2021 09:23:54 -0500 Received: from mx1.tidalhosting.net (unknown [10.0.100.9]) by mrtr.tidalhosting.net (Postfix) with ESMTPS id 4BC313049D7C; Tue, 2 Feb 2021 09:23:49 -0500 (EST) Received: from mwf4500 (c-73-238-99-41.hsd1.nh.comcast.net [73.238.99.41]) by mx1.tidalhosting.net with SMTP (version=Tls cipher=Aes256 bits=256); Tue, 2 Feb 2021 09:23:32 -0500 From: "Mark W. Farnham" To: , References: In-Reply-To: Subject: RE: [External] : Re: Question on gathering System Statistics Date: Tue, 2 Feb 2021 09:23:28 -0500 Message-ID: <2de601d6f96e$f9eac600$edc05200$@rsiz.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_2DE7_01D6F945.1115A860" Content-Language: en-us X-Exim-Id: 2de601d6f96e$f9eac600$edc05200$ X-archive-position: 78823 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: mwf@rsiz.com Precedence: normal Reply-To: mwf@rsiz.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 ------=_NextPart_000_2DE7_01D6F945.1115A860 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable 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 =E2=80=9Cwithin tolerance=E2=80=9D could be checked on soft = parses. =20 For the reasons you mentioned in the thread there are logical reasons to = collect the system statistics. =20 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. =20 You may have specific cases where careful collection (or punching to = specific values) tends to create better plans for that particular case.=20 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=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. =20 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. =20 mwf =20 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 =20 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 =20 --=20 Mladen Gogala Database Consultant https://dbwhisperer.wordpress.com ------=_NextPart_000_2DE7_01D6F945.1115A860 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

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 =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 cases either 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 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

 <= /o:p>

-- 
Mladen =
Gogala
Database Consultant
https://dbwhisperer.wordpress.=
com
------=_NextPart_000_2DE7_01D6F945.1115A860-- -- http://www.freelists.org/webpage/oracle-l