[Oracle-l] Performance queries

Bill,

If you have G.C., that is probably a good start.
Also, your app likely has some critical queries that must perform well.

Dennis Williams

No GC, and server is 100% utilized the majority of the day

Bill

Hi Bill
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
windows).
I then run the following query to grab the active SQL

select

s.sid, s.serial#, p.spid ospid, q.sql_text, s.sql_child_number

from
v$process p, v$session s, v$sqlarea q
where
p.addr = s.paddr

and s.sql_id=q.sql_id(+)
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 at
http://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!
share for an Oracle 9i database?

Kyle had to stop working on Ashmon when he joined Embarcadero. Fortunately he could show/prove that he did not touch it once he started working for Embarcadero, else they would have claimed it as their property. You can download it from http://www.perfvision.com/ashmon.php.
Marcin Przepiorowski has made changes to Kyle's original V2 ( dated 2008-03-30 ) as V2.1 and V2.2 and posted those on the Ashmasters site http://ashmasters.com/ash-simulation/
I have made extensive modifications as well and use it at two client sites. I have not published my modifications. Firstly because I am still not done with it ( I am afraid it will be a "work in progress" forever ) and when I contemplated posting what I had done, Marcin had posted V2.2 which, if I'm not mistaken is a 180Ëš departure ( the repository polls the targets rather than the targets sending data to the repository ) and I did not want to confuse the situation by posting a 2.3 or 3.1 version which reverses that again. If anyone is interested in "my" version contact me by e-mail.
I shall present about Ashmon and my experiences with it at the UKOUG conference.

To answer Niall's question, Yes, it supports 9i although I do not have any experience with it on 9i.