Re: Urgent: performance problem

Marco Ribeiro wrote in message <36793B30.6B3A486B_at_bart.inescn.pt>...
>We've got a database over which we execute several procedures. Each one
>of these procedures calculates a ratio an inserts the value in a table
>(pretty simple).
>
>At first these procedures ran very fast and overall performance was good
>but as time passed the performance degraded until it is now reduced to a
>crawl.

Always, always look first to see what the processes are trying to do as a starting point. Check out v$session_wait (to see what a process is currently waiting for) and v$session_event (to see on what events the process is spending most of it's time on). This should give you a fair indication IMHO as to what to look next at. For example, if the process is spending a lot of time on db sequential reads for example, that's an indication of a full table scan. Then you start to dig deeper as you have an idea as to what to dig for - either rollback, i/o, locks etc. problems.

The secret behind performance tuning IMHO is to start at the top and work your way down. Take one piece at a time, analyze it for problems, discard it and move onto the next one. Big bang approaches that some people suggest, I can't really agree with. Before running this and that performance checking set of scripts, rather just do a couple of simplistic checks first (hey, we need to use the v$ tables for something! ;-) and then go and kick the performance problem in the nuts. Hard. And repeatedly.

regards,
Billy Received on Tue Dec 22 1998 - 01:38:38 CST