FAQ
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

Search Discussions

  • Dennis Williams at Aug 23, 2011 at 11:30 pm
    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 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

    --
    http://www.freelists.org/webpage/oracle-l
  • Bill Zakrzewski at Aug 24, 2011 at 2:59 am
    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 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,
  • Niall Litchfield at Aug 24, 2011 at 10:12 am
    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 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 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,
  • Wolfgang Breitling at Aug 24, 2011 at 2:39 pm
    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.

    On 2011-08-24, at 4:12 AM, Niall Litchfield wrote:

    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!
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 22, '11 at 10:18p
activeAug 24, '11 at 2:39p
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase