Re: Performance queries
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: bill@xxxxxxxxxxxx
- Date: Wed, 24 Aug 2011 11:12:46 +0100
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
a) if it supports 9i
b) what the state of it currently is and
c) 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 <bill@xxxxxxxxxxxx> wrote:
>
> No GC, and server is 100% utilized the majority of the day
> Bill
> On Aug 23, 2011, at 7:30 PM, Dennis Williams wrote:
>
> 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
>
> On Monday, August 22, 2011, Bill Zakrzewski <bill@xxxxxxxxxxxx> wrote:
> > All -
> >
> > 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.
> >
> > TIA,
> > Bill--
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
