Re: Performance base line

Noons wrote:

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

If you or Jonathan send it to me I'll publish it on the web and make the URL available to everyone.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)