Re: oracle performance

In article <1162027422.196530.274480_at_k70g2000cwa.googlegroups.com>,  "rawass" <wassim_najjar_at_cis.com.lb> wrote:

> Dear All,
>
> I need to know what are in general the steps that I have to follow in
> order to diagnose the reasons behind a performance problem on oracle
> dtabases? Please if you can detail each step in your answer.
>
> Best Regards

Huge book have written on this this topic. Replying in detail would require a new book :)

Any way, I'll keep it short.

1. install STATSPACK on the database
2. identify specific periods where the database is running slow
3. identify specific programs that run slow
4. setup statspack to run at the specific times where the database is slow or manually take a snapshot before running one of the programs in question.
5. Run a statspack report comparing before and after snapshots and analyze the report.

Most likely you'll spot one or more SQL's that need tuning. Start there. Once those have been tuned check performace. If still unacceptable return to 1) ... repeat until done.

NOTE of caution:

   Before you start, decide when to stop. And get acceptance for that stop-clause from your users. Something like "Once you can operate the CCCCCCC programs without long waits, we'll quit". Or "Once program XYZ completes in XYZ minutes, we are done."

Otherwise it's easy to keep tuning for too long. The law of diminishing returns apply here.

On more note:

   There may come a time when no SQL's need tuning, that's when hardware tuning starts ... and that's a whole other can of worms :)

Hope this helps
Michael Willer
Java&Oracle developer Received on Sat Oct 28 2006 - 07:51:08 CDT