Re: System stats

Interesting. There have been lots of advices related to system stats and the OICA/OIC parameters. The advices tend to ask us to leave the OICA/OIC parameters at default and calculate system stats instead. For those advising to leave system stats at default, is it ok to adjust OICA/OIC parameters? Does leaving the system stats at default have an(y) effect on the OIC/OICA?

On Mon, Mar 25, 2019 at 8:23 AM Neil Chandler <neil_chandler_at_hotmail.com> wrote:

> 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_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
>
>
> 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_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
>
> 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l