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.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
Sent: 25 March 2019 07:50
To: Oracle-L Freelists; carlospena999@xxxxxxxxx
Subject: Re: System stats
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 -
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
Regards.
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Cee Pee <carlospena999@xxxxxxxxx>
Sent: 25 March 2019 04:56:13
To: Oracle-L Freelists
Subject: System stats
List,
I was reading up on system stats and came across this link:
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,
CP
--
//www.freelists.org/webpage/oracle-l
