Check list for performance problems

From: Steve Perry <sperry_at_sprynet.com>
Date: Fri, 08 Jan 1999 09:03:44 -0800
Message-ID: <36963A70.ADDE295A@sprynet.com>

I'm in need of a check list that I can run through (for me and others in my group) when we get called on performance problems. Since the problems go from overworked servers to bad sql, I want to create a check list I can run through quickly that will allow me to focus on the biggest problems.

First, I make sure I can get to the database.
Next, I check the server (unix - aix and solaris)
Next, check the alert log, arch destination, and see if any trace files generated
Next: I run iostat, vmstat, monitor to see how hard the server is working (maybe "ps" to see most cpu and memory)
I don't know how I can check network utilization???
Get the hardware info (# of cpus, memory...)
next, I go into database and check cache hit ratios (db buffers, library, dictionary)
, file i/o
, open transactions
, log history to see how fast log switches occur
, sql in memory
, which sql is being used the most and the number of i/o per execution
, locks being held (blockers and being blocked)
, check for busy buffers
, sorts going to disk or memory
, wait events
, number of users logged in
, see what parms are set in v$parameter
, see if any scheduled jobs are running
and run other scripts I have that show info about the database.

How do others go about troubleshooting performance problems?
We have a centralized environment where we support all oracle databases, but we aren't neccessarily familiar with all systems.

I'd like to create a decision tree, so I have a consistent straight forward way of handling this type of situation.
I know there are a lot of variables, but I'm sure years of experience have taught people to look in certain area first.

Thanks,
Steve Received on Fri Jan 08 1999 - 11:03:44 CST