Re: [External] : Re: Question on gathering System Statistics
Date: Tue, 2 Feb 2021 14:24:37 +0000
Message-ID: <DB7PR10MB2090BE6C93F3797B03FEA88C85B59_at_DB7PR10MB2090.EURPRD10.PROD.OUTLOOK.COM>
For me the basic explanation is they are just too complicated, and few people 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 virtual cpu which may or may not be dedicated to the server. Your workload varies across the day. The variables are just too many to make gathering system stats sensible.
Having the defaults means a good known and tested balance between multiblock and single block access for the optimizer calculations, and a known 1MB-per-multiblock-read for the actuals.
I have seen some real insanity in workload gathered stats (e.g. MBRC=0, or SREADTIM=71000 MREADTIM=10ish) which caused the defensive coding in system stats to kick in, as well as making the DBA's set awful init.ora parameters to counteract the bad system stats because they simply didn't understand 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 good 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 for 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-part-2-plan-costing
Neil Chandler
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Mladen Gogala <gogala.mladen_at_gmail.com> Sent: 02 February 2021 06:58
To: oracle-l_at_freelists.org <oracle-l_at_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
“That is correct. Its best not to gather system stats”
--
Mladen Gogala
Database Consultant
https://dbwhisperer.wordpress.com<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdbwhisperer.wordpress.com%2F&data=04%7C01%7C%7C447dc4580fdb490f21a408d8c747f53b%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637478459181507169%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=vuOKPDkjI2nN6qSNfr7CdtQtIedz9fFPXtSgfhcdSZ4%3D&reserved=0>
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 02 2021 - 15:24:37 CET