Re: Re: System stats

System stats just as OICA/OIC should mainly (not exclusivly) balance costing of access like index scan versus full table scan. What makes you think that system stats is the root cause of your issue as opposed to e.g. wrong cardinality estimate? Unfortunatly there is no panacea and no substitute for a thorough root cause analysis. ----Ursprüngliche Nachricht----
Von : carlospena999_at_gmail.com
Datum : 26/03/2019 - 00:01 (MN)
An : oracle-l_at_freelists.org
Betreff : 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