FAQ
Greetings

Oracle 9i AIX 5L we are using SAN Stroage

I see quite a few SQL in the STATSPACK report with high Elapsed time,

I notice the following:

Disk is 100% bussy - topas report
sar -d report - Service time is less than 20 msec, disks show 99 to
100% busy
STATSPACK Report for file io shows 3 digit numbers for Av rd (ms)

STATSPACK Report TOP SQL Shows high Elapsed time.
Some of the SQL are doing FTS
What other stats should I look at to narrow down the issue

Should I go behind Tuning the Queries or Storage, spreading datafiles across
multiple disks.

Apprecaite your thoughts

Search Discussions

  • Alexander Fatkulin at Dec 4, 2007 at 6:37 pm
    trace your quires using 10046 level trace with waits and see how much
    time you do really spend on I/O
    Should I go behind Tuning the Queries or Storage, spreading datafiles across
    multiple disks.

    Apprecaite your thoughts






    --
    Regards & Thanks
    BN
    --
    Alex Fatkulin,
    The Pythian Group,
    http://www.pythian.com/blogs/author/alexf
    --
    http://www.freelists.org/webpage/oracle-l
  • Guerrero, Citlali (GE, Corporate, consultant) at Dec 4, 2007 at 7:22 pm
    Hi All,

    I'll like to unsuscribe from this list, I try it before but it
    didn't work, any one can tell me how can I do it?

    thanks in advanced

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Alexander Fatkulin
    Sent: Martes, 04 de Diciembre de 2007 12:38 p.m.
    To: bnsarma_at_gmail.com
    Cc: oracle-l_at_freelists.org
    Subject: Re: Performance issue - Not sure if its disk or some thing -
    How can I narrow down this

    trace your quires using 10046 level trace with waits and see how much
    time you do really spend on I/O
    Should I go behind Tuning the Queries or Storage, spreading datafiles
    across multiple disks.

    Apprecaite your thoughts






    --
    Regards & Thanks
    BN
    --
    Alex Fatkulin,
    The Pythian Group,
    http://www.pythian.com/blogs/author/alexf
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Arul Ramachandran at Dec 4, 2007 at 6:50 pm
    My 2 cents -

    Identify if there were any system configuration changes made recently, if
    any changes were done to disk storage config recently. If yes, then
    investigate this - this could be a potential cause.

    Pick the top few sqls and their hash value from statspack based on "Gets
    per Exec" ( you can also compare statspack for a previous day/similar time
    when the system was healthy )

    You can either use this to get the plan from v$sql_plan,
    v$sql_plan_statistics_all or you can run the sql to generate tkprof on 10046
    trace/level 8 or 12 to check for row source operations and cpu, elapsed and
    the wait events. (if the cpu and elapsed are way off, look at where the sql
    is waiting)

    What I am trying to get is, if the execution plan of the few top sqls are
    efficient or not.

    If the execution plan is not efficient or they have changed, investigate
    this

    If the execution plan is efficient, then see from the tkprof where the
    sql is waiting on.

    Thanks,
    Arul
    On Dec 4, 2007 9:13 AM, BN wrote:

    Greetings

    Oracle 9i AIX 5L we are using SAN Stroage

    I see quite a few SQL in the STATSPACK report with high Elapsed time,

    I notice the following:

    1. Disk is 100% bussy - topas report
    2. sar -d report - Service time is less than 20 msec, disks show 99 to
    100% busy
    3. STATSPACK Report for file io shows 3 digit numbers for Av rd (ms)

    4. STATSPACK Report TOP SQL Shows high Elapsed time.
    Some of the SQL are doing FTS
    What other stats should I look at to narrow down the issue

    Should I go behind Tuning the Queries or Storage, spreading datafiles
    across multiple disks.

    Apprecaite your thoughts






    --
    Regards & Thanks
    BN
    --
    Arul

    --
    http://www.freelists.org/webpage/oracle-l
  • John Kanagaraj at Dec 4, 2007 at 11:52 pm
    BN,

    Keep in mind that STATSPACK has a deficiency in the way Top SQL is
    collected. The package scans V$SQL using a query that has a fixed
    number of Buffer gets, Disk-reads, etc. that is previously configured.
    If you have pinned packages/cursors and have not restarted the Db in a
    while, you will collect SQL that is executed a large number of times
    for functional 9and valid reasons) that are really NOT the issue. (I
    deal with this topic in detail in my papers and in my book)
    2. Pick the top few sqls and their hash value from statspack based on "Gets
    per Exec" ( you can also compare statspack for a previous day/similar time
    when the system was healthy )
    As for your disk issue, I wouldn't really depend on 'sar -d'. On
    modern systems, there are just too many layers inbetween Oracle and
    the final disk (see previous post). If you want to, I would rather
    look at the Tablespace/File level I/O as recorded in STATSPACK and
    look for oddities therein.

    Finally, of course, you will need to look at Business critical SQL and
    trace them with 10046 to really determine the issue.

    --
    John Kanagaraj <><
    DB Soft Inc
    http://www.linkedin.com/in/johnkanagaraj
    http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!)
    ** The opinions and facts contained in this message are entirely mine
    and do not reflect those of my employer or customers **
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 4, '07 at 5:13p
activeDec 4, '07 at 11:52p
posts5
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase