Re: Performance base line

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:6g5bmvokkv2rg01cro1s4k7d8v9thqoav9_at_4ax.com...
>
> 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'm not sure you can, in the sense it is mentioned in many books. Most sites fight with fitting a production instance on hopelessly inadequate hardware, because of the TCO thing. Asking them to set up another "test bed" or "base line" or whatever is just wishfull thinking, IMO. Hence why it is so little used.

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

Exactly.

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

Of course. Do some data mining off the v$sqlarea. Most of it is there. Jonathan Lewis discussed 2 years ago here, IIRC, some metrics to use to look at this view and dig out heavy SQL statements. I've still got it somewhere, drop me a line and I'll dig it out.

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

I know a guy that was doing some work in that direction. He works for IBM GSA and was writing a package to do just that. Not sure if he ever published it. Have a look around the usual places: Jonathan's, Steve's, Connor's and Cary's sites. I'm sure Ive seen something along these lines there.

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

Indeed . Your own app. Learn its patterns of execution. Use statspack to monitor its running and try to extract some trends into a spreadsheet. Compare for example morning to afternoon and overnight periods. I/O distribution. Use OS stats during same periods to co-relate to CPU and memory usage. Once you've found the hot spots, zoom in with v$sqlarea during those periods to dig out what is heavy. Tune that. Either the SQl or the instance. Sometimes both. Go back to "Indeed" above.

You'll have to use your head, so don't expect a piece of software or utility to light up bulbs on top of it. Most likely, they will be the wrong ones.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam