Re: Performance base line

Originally posted by Rick Denoire

> 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

What I do is take statspack snapshots during the day - scheduled through dbms_job at 1/2 hour intervals. This allows me to look at particular intervals to see what was going on. At the end of the day, I extract statistics for the entire day (I use the period between the 8am and 5pm snapshots) and load that data into excel spreadsheets. I can then plot individual statistics, e.g. the time spent waiting on sequential reads or scattered reads. In my experience you need to do that over several months to be able to tell what is normal and what is not. You probably have 3 trends superimposed:

• a general uptrend due to growth in data volume, possibly interrupted by sharp downs if/when data is archived/purged
• a pattern of ups and downs due to varying use by the business (e.g. higher volume in a financials system prior to releasing the monthly/quarterly financial reports)
• increases/decreases due to changes to the OS, database, statistics, application, etc.

It is this last piece that you want to isolate, but in order to do that you need to be able to subtract the contributions of the first two.

--
Posted via http://dbforums.com