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
