RE: System stats

A small diversion but relevant to the discussion.  

The problem I have seen over and over (20+ years) is that even if someone makes a change they often don't have the data (in an accessible/tangible form) to validate the effect of the change unless it is quite obvious (and then still there are problems possible here).  

I always kept performance history in a format that allows me to see SQL, avg elap time, # executes, and total time by hour, day, month, going back at least 1-2 years (and it doesn't use much space).  

This can all be displayed in a text table. Sorted by greatest consumer. It's very simple. You can also do this with just about anything you want. I used to do this with PeopleSoft batch job executions and a bunch of other stuff.  

You can also do things like %change month over month which is very helpful in spotting changes without the need for 3rd party charting/tooling.  

The nice thing about this is when you make a change, let it run a day, week, month, you will usually see fairly quickly what the impact is on individual SQL's and then over-all. It is measurable.  

And even managers understand it when you show it to them.  

I am sure to some degree this information is accessible from other sources but not sure it is quite as accessible or readily available as this particular design.  

And I know more than average on this particular list don't make these mistakes but this list sort of self selects for above avg DBA's because avg don't join lists like Oracle-L for the most part.  

I interviewed countless DBA's over the years and as far as I recall none had an answer for above.  

So something to think about. I would like to work on getting above out to the world but can't do it at the moment. When I do release it will be part of ArcShell (free/apache2 license).  

Thanks,

Ethan Post

https://github.com/arclogicsoftware/arcshell    

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Neil Chandler
Sent: Tuesday, March 26, 2019 9:35 AM
To: Chris Taylor <christopherdtaylor1994_at_gmail.com> Cc: gogala.mladen_at_gmail.com; ORACLE-L <oracle-l_at_freelists.org> Subject: Re: System stats  

In the majority of places I have worked - 5 clients last year - the SANs were overloaded in 4 of them. They are too frequently sized for capacity and not throughput/response time. The response time was inevitably variable and System Stats would not have been helpful on the systems they have. In one of the clients, some of the critical DB's have dedicated storage but changing the system stats would have had little to no effect on those systems due to other measures having been put in place (including using a low optimizer index cost adj on one system, meaning lots of index use. Just not necessarily the right indexes.)  

The optimizer tries to be all things to all people, and there's lots of parameters to try to twist it into the shape that you want. The problem is frequently the abuse of those parameters - especially the global ones - via googling a problem, believing a silver bullet blog, and the lack of time to prove the solution so we just throw the fix into the system. It can be enlightening to strip the more extreme parameters back to their defaults and see how the system copes.  

As an aside, did you run your systems with the default parameters, discover notable problems and then use the 2 sets of system stats to correct those problems, or did you put them in from the start and everything was good?  

There's a case to be made for using system stats, but I just don't think that is something that should be used frequently.    

Neil.  

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