Performance base line

Hello to all DBAs

Changing parameters could turn into a time consuming task if the DBA is to assess the effect of the change in every particular case. But since the change is expected to have a general influence on performance of the DB as a whole, one could draw conclusions about the effect of the change from statistical data. This is where a kind of "performance base line" deserves attention. So if performance "in general" has improved, the change is considered to be a good one without spending a lot of time making tests of applications.

My question is, how do you set up and maintain a "performance base line", which is mentioned in many "best practices" documents, by the way. I just did not find any instructions about how to do that. I mean, how to do it without having to call the users John, Clark, Scott... and ask them to pay attention to execution time of their batch jobs. Did you notice that users never mention improvements? They will complain if things get mistuned for sure! But their feedback can't be used as an objective base line. I need *metrics*.

Isn't it possible to identify recurrent SQLs in the SGA and track their execution times or resource consumption or full table scan rates or degree of parallelism etc. without non-DBA participation? In a more or less automatic way? I am willing to spend considerable time preparing for that, but I would never do it again and again in a cumbersome way. I just want to setup a procedure once, and then just monitor its results. Just point me to any sources, if you wish.

Sorry, did not find the answer in the books.. If books speak so often about performance base lines ("the best benchmark is your own appliation"), *someone* should have done that already, if it is not just theory. How?

Thanks
Rick Denoire Received on Mon Sep 15 2003 - 05:58:19 CDT