Re: Performance base line

It depends on the Oracle version to some degree. Oracle 9 has elapsed time in v$sql and v$sqlarea while Oracle 8 does not. But to a large degree statspack records counts which in isolation isn't very helpful. To quote Cary Millsap "You can't tell how long something took by counting how many times it happened"; but in comparison with historic data you can draw conclusions. If you compare metrics for a day or days where performance is poor - as reported/complained by the users - with the metrics at some other time when performance was good/acceptable and some numbers are significantly higher, there is a good chance that those higher counts have something to do with the slower performance. I have used that aberration from the normal pattern at times to spot problems. The problem is that it can take a few days to establish a symptomatic deviation from the normal trend as opposed to a blip caused by a onetime  event.

Originally posted by Rick Denoire

> W.Breitling <member28455_at_dbforums.com> wrote:

>

>

> >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.

>

> OK, very instructive. I hope that the numbers not only reflect changes

> in the amount of activity, but also the time related throughput, i.e.,

> the effectiveness. I will see by myself. Did you use a special perl

> script of something?

>

> Thanks

Rick Denoire

--
Posted via http://dbforums.com