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 0BDA2100382ED8 for ; Tue, 2 Feb 2021 15:24:56 +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 46727454E4; Tue, 2 Feb 2021 14:24:55 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 3BBD73F976; Tue, 2 Feb 2021 14:24:55 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612275895; bh=BXiWFU+WS9AoCMjlF7Zmu6q44WAeNgsxyRlsFRX5bmA=; h=From:Sender:Sender:From; b=sixXY43GBkJ0M172rgePyvFccR1QOGvo6px1XCbzjZtNc4kmnHW4W7gP7eFoP5MkN VFh6lt51rGr5m/d9lj/yclhDh4jRNnpZP0xpem9EFnoGPIJTJTh7ZKjsuzDhJHSWtA xTJZFPQTn7YYZFTsec2h+Jg6SM9Md8bllu9MP4GA= 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 7sp0NhFWUhFG; Tue, 2 Feb 2021 14:24:55 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id BB4573F978; Tue, 2 Feb 2021 14:24:43 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612275884; bh=BXiWFU+WS9AoCMjlF7Zmu6q44WAeNgsxyRlsFRX5bmA=; h=From:Sender:Sender:From; b=eOuo1DomK/ZNJdCfwbUNPozGCiC9DNR8TkpRP4xsaNhmj6X7mE/7pYbPghJ/KWqtn ZEanMgBYhGC1Cmp0tBCoWSJIKKgjuRJXgSHsNC4BsvBX6trI92f63bxjVk2LmN0CR/ pulvWbjrJ6yf7hIRLVtil71ynD7nP90Vr2OAwbHQ= Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 02 Feb 2021 14:24:41 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 81A713F971 for ; Tue, 2 Feb 2021 14:24:41 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=hotmail.com header.i=@hotmail.com header.a=rsa-sha256 header.s=selector1 header.b=CAlU9i0H; 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 cJ-BRX5gzrbs for ; Tue, 2 Feb 2021 14:24:41 +0000 (UTC) Received: from EUR01-VE1-obe.outbound.protection.outlook.com (mail-oln040092066011.outbound.protection.outlook.com [40.92.66.11]) (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 000C53F972 for ; Tue, 2 Feb 2021 14:24:39 +0000 (UTC) ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=ck3Q6iZ3+IiKeDLyYRWwHknDERlhQBNiv61dhYWywaRknLehAMhES40Cl0pHZ34NVKS19vbq3iQXuMRViy3bsIn77ZSYGDi0IDNnTk6HpNpJIE2+SkxKP2mbIo9D/MoRUAuoq6QwXe0UR/optL0Yb/duFXic6FnpHi41VpU+Lu4r14z0aOpedJzlhAp04sgeUuQWtsxx+0kGBvyHkQV+te67bBmO8FKi8pBKWNVnvlnTnw6jcmNzKJfnOfLGOYRxqVGEvrwl9D4rMlzZfdvRaG5tVLxx2btclpztJLrSsXl8wvVMr7ZQULV9Ca1zbGPEl2uGDRfuWB1RL7bLQbJmOg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=0bapci4j3MrxbahCGE+9ZwY/kU4/BO5wP0gYW8b9MAg=; b=eI9nvQoG5c+DRkokowhWLSGnXSdqoATFE1SYya5yfzrhRZO5Q/RJUiA1VWI0WyknEMG+woJty7W1qmiRPxR3vASsuzYuOyq3hQREInLKDH5oRT960mmsPVrQ2us3tUk1eBkgNg460wC0TsMQ+j4s84mUQitzgAv1qX3GnGcDNjap9Ff85zWT28WQjjRPFu6FPJyMcnxIaVD0rIPbbhKeoqhSFvweNBl/xWJkMushEy88hoqgzjEnnnS1Op4icmE9v4UaAYJ3EoQpkkbg5r3wwu/4P/qQd8b06bUwkFvIW85NnrPYNd7e+o0DgnPDb0cKOb5ZLa7G7L1OLZlE06LEUQ== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none Received: from DB5EUR01FT011.eop-EUR01.prod.protection.outlook.com (2a01:111:e400:7e1a::4f) by DB5EUR01HT119.eop-EUR01.prod.protection.outlook.com (2a01:111:e400:7e1a::188) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.3784.11; Tue, 2 Feb 2021 14:24:38 +0000 Received: from DB7PR10MB2090.EURPRD10.PROD.OUTLOOK.COM (2a01:111:e400:7e1a::4c) by DB5EUR01FT011.mail.protection.outlook.com (2a01:111:e400:7e1a::259) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.3784.11 via Frontend Transport; Tue, 2 Feb 2021 14:24:38 +0000 Received: from DB7PR10MB2090.EURPRD10.PROD.OUTLOOK.COM ([fe80::597c:fd30:7905:7f5d]) by DB7PR10MB2090.EURPRD10.PROD.OUTLOOK.COM ([fe80::597c:fd30:7905:7f5d%6]) with mapi id 15.20.3805.019; Tue, 2 Feb 2021 14:24:37 +0000 From: Neil Chandler To: "oracle-l@freelists.org" , "gogala.mladen@gmail.com" Subject: Re: [External] : Re: Question on gathering System Statistics Thread-Topic: [External] : Re: Question on gathering System Statistics Date: Tue, 2 Feb 2021 14:24:37 +0000 Message-ID: References: , In-Reply-To: Accept-Language: en-GB, en-US Content-Language: en-GB X-MS-Has-Attach: X-MS-TNEF-Correlator: x-incomingtopheadermarker: OriginalChecksum:C64C4B8A8650B96FF13D3A404C1CA56BF39C5D194F1FE2254F9779C94A76429B;UpperCasedChecksum:77B07167E2CF91466036B0924A95109A44631A2104A5CEEB62D540B9BF215FAE;SizeAsReceived:7325;Count:44 x-ms-exchange-messagesentrepresentingtype: 1 x-tmn: [6Hl+4OTNDir6Wt8RZQYaRMi8KXWIFSrK] x-ms-publictraffictype: Email x-incomingheadercount: 44 x-eopattributedmessage: 0 x-ms-office365-filtering-correlation-id: 047e18ad-09b8-4250-0955-08d8c7864597 x-ms-traffictypediagnostic: DB5EUR01HT119: x-microsoft-antispam: BCL:0; x-ms-exchange-transport-forked: True Content-Type: multipart/alternative; boundary="_000_DB7PR10MB2090BE6C93F3797B03FEA88C85B59DB7PR10MB2090EURP_" MIME-Version: 1.0 X-OriginatorOrg: hotmail.com X-MS-Exchange-CrossTenant-AuthAs: Anonymous X-MS-Exchange-CrossTenant-AuthSource: DB5EUR01FT011.eop-EUR01.prod.protection.outlook.com X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-Network-Message-Id: 047e18ad-09b8-4250-0955-08d8c7864597 X-MS-Exchange-CrossTenant-originalarrivaltime: 02 Feb 2021 14:24:37.9196 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Internet X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-Transport-CrossTenantHeadersStamped: DB5EUR01HT119 X-archive-position: 78824 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: neil_chandler@hotmail.com Precedence: normal Reply-To: neil_chandler@hotmail.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 --_000_DB7PR10MB2090BE6C93F3797B03FEA88C85B59DB7PR10MB2090EURP_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable For me the basic explanation is they are just too complicated, and few peop= le understand them, so leave them alone. Any form of shared infrastructure (and it's all shared now, unless you have= a dedicated single DB exadata) means you have unknowns galore. your SAN is= being used by other apps. your CPU really isn't a CPU. It's a bit of a vir= tual cpu which may or may not be dedicated to the server. Your workload var= ies across the day. The variables are just too many to make gathering syste= m stats sensible. Having the defaults means a good known and tested balance between multibloc= k and single block access for the optimizer calculations, and a known 1MB-p= er-multiblock-read for the actuals. I have seen some real insanity in workload gathered stats (e.g. MBRC=3D0, o= r SREADTIM=3D71000 MREADTIM=3D10ish) which caused the defensive coding in s= ystem stats to kick in, as well as making the DBA's set awful init.ora para= meters to counteract the bad system stats because they simply didn't unders= tand them. "We ran them because Oracle recommended it" The recommendation from Nigel Bayliss - the Optimizer PM - is don't gather = them. If Nigel, Maria, and Jonathan say (mostly) don't, you need a real goo= d tested proof to go against the defaults. Shamelss Plug: If you want more info about system stats, by coincidence I'm talking about = them tomorrow as part of an optimizer costing talk, 0800 GMT / 13:30 IST fo= r the All India Oracle User Group. You can register and watch for free: htt= ps://www.aioug.org/events/what-the-heck-is-the-oracle-optimizer-doing-part-= 2-plan-costing Neil Chandler ________________________________ From: oracle-l-bounce@freelists.org on beha= lf of Mladen Gogala Sent: 02 February 2021 06:58 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 poss= ible to persuade to come here and explain the recommendation? Regards On 2/1/21 12:09 PM, Jeff Smith wrote: Maria confirms =93That is correct. Its best not to gather system stats=94 -- Mladen Gogala Database Consultant https://dbwhisperer.wordpress.com --_000_DB7PR10MB2090BE6C93F3797B03FEA88C85B59DB7PR10MB2090EURP_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable
For me the basic explanation is they are just too complicated, and few peop= le understand them, so leave them alone.

Any form of shared infrastructure (and it's all shared now, unless you have= a dedicated single DB exadata) means you have unknowns galore. your SAN is= being used by other apps. your CPU really isn't a CPU. It's a bit of a vir= tual cpu which may or may not be dedicated to the server. Your workload varies across the day. The variable= s are just too many to make gathering system stats sensible.

Having the defaults means a good known and tested balance between mu= ltiblock and single block access for the optimizer calculations, and a know= n 1MB-per-multiblock-read for the actuals.

I have seen some real insanity in workload gathered stats (e.g. MBRC=3D0, o= r SREADTIM=3D71000 MREADTIM=3D10ish) which caused the defensive coding in s= ystem stats to kick in, as well as making the DBA's set awful init.ora para= meters to counteract the bad system stats because they simply didn't understand them. "We ran them because Orac= le recommended it"

The recommendation from Nigel Bayliss - the Optimizer PM - is don't gather = them. If Nigel, Maria, and Jonathan say (mostly) don't, you need a real goo= d tested proof to go against the defaults.

Shamelss Plug:
If you want more info about system stats, by coincidence I'm talking about = them tomorrow as part of an optimizer costing talk, 0800 GMT / 13:30 IST fo= r the All India Oracle User Group. You can register and watch for free: https://www.aioug.org/events/what-the-heck-is-the-oracle-optimizer-doing-pa= rt-2-plan-costing

Neil Chandler


From: oracle-l-bounce@fre= elists.org <oracle-l-bounce@freelists.org> on behalf of Mladen Gogala= <gogala.mladen@gmail.com>
Sent: 02 February 2021 06:58
To: oracle-l@freelists.org <oracle-l@freelists.org>
Subject: Re: [External] : Re: Question on gathering System Statistic= s
 

Any explanation? This looks a bit counter-intuitive to me. Would it be p= ossible to persuade to come here and explain the recommendation?

Regards

On 2/1/21 12:09 PM, Jeff Smith wrote:

Maria confirms

=93That is correct. Its best not to gath= er system stats=94

 

-- =0A=
Mladen Gogala=0A=
Database Consultant=0A=
=
https://dbwhisperer.wordpress.com
--_000_DB7PR10MB2090BE6C93F3797B03FEA88C85B59DB7PR10MB2090EURP_-- -- http://www.freelists.org/webpage/oracle-l