It *sounds* like you are saying that the server is on CPU all the time
(though 100% utilized could mean other things). To troubleshoot this I start
with an OS tool to grab the top cpu consuming O/S processes (or threads in
I then run the following query to grab the active SQL
s.sid, s.serial#, p.spid ospid, q.sql_text, s.sql_child_number
v$process p, v$session s, v$sqlarea q
p.addr = s.paddr
and p.spid = &os_process_id;
It may be that there isn't active SQL but this is rare in my experience (and
usually turns out to be session waiting on a PL/SQL loop anyway. You'll also
want to run statspack and take a look at the top sql there - though in 9i
unfortunately there isn't an 'ordered by elapsed time' section. Statspack
will also give you top 5 timed events as well.
Bear in mind that once you've identified your top sessions - either via
TOP/process monitor etc - or via the application or some other means ("how
come it's always Sue in accounts ringing to complain about performance?" )
then 9i is just as susceptible as any other release since 7.3 to the
Method-R approach.(Identify sessions, gather diagnostic data, profile,
resolve issue and predict performance increase). method-r.com or the other
trace file profilers out there will be useful here.
Finally I've had some success with Tanel Poder's session snapper on 9i
(though I've also had failures on 9i as well - tool ran but not in a timely
fashion), you can grab that tool athttp://tech.e2sn.com/oracle-scripts-and-tools/session-snapper
I know that Kyle Hailey was working on a simulated Active Session History
tool (www.ashmasters.com) but I'm not sure
if it supports 9i
what the state of it currently is and
which of Kyle's sites are current.
This is a plea for Kyle to come out of the woodwork really and let us know
what he has made available non-commercially and where!
On Wed, Aug 24, 2011 at 3:59 AM, Bill Zakrzewski wrote:
No GC, and server is 100% utilized the majority of the day
On Aug 23, 2011, at 7:30 PM, Dennis Williams wrote:
If you have G.C., that is probably a good start.
Also, your app likely has some critical queries that must perform well.
On Monday, August 22, 2011, Bill Zakrzewski wrote:
Does anyone have some good performance queries they would be willing to
share for an Oracle 9i database?
ie. Top SQL statements, Top Sessions, etc.