Re: Performance queries
Date: Wed, 24 Aug 2011 11:12:46 +0100
Message-ID: <CABe10sYOCfmUgxVoh9eeKnuLVNStjPWGORizEtj6w6Qn6NaR+g_at_mail.gmail.com>
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!
On Wed, Aug 24, 2011 at 3:59 AM, Bill Zakrzewski <bill_at_intactus.com> 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_at_intactus.com> 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--
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
-- Niall Litchfield Oracle DBA http://www.orawin.info -- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 24 2011 - 05:12:46 CDT