Re: System stats
Date: Mon, 25 Mar 2019 13:23:32 +0000
Message-ID: <DB7PR10MB2090E5D3632B41E0ED521473855E0_at_DB7PR10MB2090.EURPRD10.PROD.OUTLOOK.COM>
UNLESS you're on Exadata, leave them to default.
If you have non-shared resources dedicated solely to that one database - no shared storage or virtualised CPU - and you have a single typical workload then it might be worth considering, but I still wouldn't gather them.
I've never had a problem with Oracle where I have thought "you know, tweaking the system system statistics will fix all of this", but I generally only see a few clients per year, not the vast number of systems with edge-case problems that some consultants see.
I also wouldn't set them manually. If I really thought I needed to have the system stats set manually, I'd probably make things worse as I'm not experienced with manual adjustment. I'd call Jonathan Lewis and ask him to do it. :-)
You might also consider what Oracle are habitually testing against. Do they do a lot of regressions against many different shapes of system statistics, or against the defaults? They don't, as a matter of course, regression test against 32K block sizes (almost all of their testing is done against 8k and 16k blocksizes) so there's little chance they are looking at this type of variation on their shared testing infrastrcuture.
Neil.
My advice has hardly changed since I wrote "CBO - Fundamentals".
If you can give Oracle a realistic idea of what your hardware does under normal load, fake it in using dbms_stats.set_system_stats(). (This is essentially what Oracle does with the EXADATA option - the optimizer has no useful information about smart scans, so the 'exadata' option simply tells it that tablscans are "very fast".)
As it is, many sites make a nonsense of the system stats by setting the parameter db_file_multiblock_read_count to 128 anyway, which has a far bigger impact on the optimizer than tweaking the stats.
On top of that, some sites are now using the resource manager "calibrate_io" procedure to measure the speed of their hardware, and that adds another dimension to how the optimizer does its arithmetic. (Though it's only supposed to be important to automatic degree of parallelism.) And someone's bound to remind us what the latest "how big is your hardware" mechanism is - because there's another one that I've forgotten about.
Bottom line -
Regards.
List,
https://blogs.oracle.com/optimizer/should-you-gather-system-statistics
Here are some of the things the author says:
1. "if you are at a decision point and you need to choose whether to gather them or not, then in most cases you should use the defaults and not gather system statistics."
Doesnt setting systems help a lot these days esp with faster IO devices. Do the listers collect system stats in your environments, test. prod, etc?
2. "there is at least some management or procedural overhead required to maintain them"
'Maintaining' stats? I thought once we set the system stats we leave it out there forever without touching it?
Thanks all,
--
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Sent: 25 March 2019 07:50
To: Oracle-L Freelists; carlospena999_at_gmail.com
Subject: Re: System stats
a) leave them to default
or
b) set them to something realistic
but
c) if you're running Exadata you need to set them to indicate very fast tablescans
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Cee Pee <carlospena999_at_gmail.com>
Sent: 25 March 2019 04:56:13
To: Oracle-L Freelists
Subject: System stats
I was reading up on system stats and came across this link:
CP
--
http://www.freelists.org/webpage/oracle-l
http://www.freelists.org/webpage/oracle-l
Received on Mon Mar 25 2019 - 14:23:32 CET