This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Oct 21, 2013 1:30 AM by Nikolay Savvinov RSS

AWR report

$phinx19 Newbie
Currently Being Moderated

Hi All,

 

Is there some kind of a bench mark for the below mentioned Timed event observed in AWR report;--

 

 

Log file sync

db file sequemtial read

db file scattered read

 

Regards,

Sphinx

  • 1. Re: AWR report
    EdStevens Guru
    Currently Being Moderated

    $phinx19 wrote:

     

    Hi All,

     

    Is there some kind of a bench mark for the below mentioned Timed event observed in AWR report;--

     

     

    Log file sync

    db file sequemtial read

    db file scattered read

     

    Regards,

    Sphinx

    All events represent a point where the database had to wait for something ... hence the name 'wait events'.

    Are you looking for some magic number, below which you can say 'no problem' and above which you say 'urgent! this must be solved!'?

     

    I have 3 ropes.  One is braided nylon, one is twisted hemp, the third is polypropylene.  Can you give me a benchmark on how long they should be?

  • 2. Re: AWR report
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    You could be asking for "good" values for the *average* wait time  (e.g. "how many milliseconds is a 'db file sequential read' wait acceptable ?  1ms or 2ms would generally mean a fetch from OS filesystem cache, 4ms to 10ms could mean disk i/o  -- what would be unacceptable disk i/o performance ? 12ms ?)

     

    Hemant K Chitale

  • 3. Re: AWR report
    $phinx19 Newbie
    Currently Being Moderated

    Yes, indeed. is there any thing like that.

    Means how we decide, if the wait events are bad. I mean, to decide whats bad we should know whats good.

     

    Regards,

    Sphinx

  • 4. Re: AWR report
    sybrand_b Guru
    Currently Being Moderated

    There is nothing like that, as it would depend of the capacity of the underlying hardware.

    Generally speaking there is a problem when the database is saturating the disks with IO requests beyond the capacity the disks can handle.

     

    ----------

    Sybrand Bakker

    Senior Oracle DBA

  • 5. Re: AWR report
    $phinx19 Newbie
    Currently Being Moderated

    Ok, Thanks.

     

    So here is one of my AWR report snap during erroneous period. What am I suppose to infer from it then?

     

    Top 5 Timed Foreground Events

     

     

    EventWaitsTime(s)Avg wait (ms)% DB timeWait Class
    DB CPU 1,163 28.86
    SQL*Net message from dblink78,85015323.81Network
    db file sequential read102,20512012.98User I/O
    row cache lock79,6036311.56Concurrency
    log file sync26,6185021.25Commit
  • 6. Re: AWR report
    sybrand_b Guru
    Currently Being Moderated

    Obviously, disk is not a problem.  During the snapshot, for which you didn't post how long it lasted, Oracle spent less than 5 percent of the time waiting for disk.

    The top 5 events account for roughly 40 percent of the time, so there is some other 60 percent nobody knows what is going on, and why this is an 'erroneous period'.

    Also, as usual, you didn't mention 4 digit database version and platform, nor the number of CPUs, so really nothing can be derived from this, other than you DON'T have an IO problem.

     

    ---------------

    Sybrand Bakker

    Senior Oracle DBA

  • 7. Re: AWR report
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    Disk I/O wait times are not an issue going by the Avg wait times reported.  Also, they do not take any sigifcant time.  Your database is CPU bound.  Either you do not have enough CPU cores on the server AND/OR  you have SQLs that are doing too many logical I/Os (reading the same blocks from the buffer cache repeatedly).

     

    Hemant K Chitale

  • 8. Re: AWR report
    $phinx19 Newbie
    Currently Being Moderated

    Thanks Sybrand

    As usual you are taking my case. My mistake I failed to mention some of the important information.

     

    Thanks Hemant,

    May I know, how you came to the conclusion that my database is CPU bound?

     

    Regards,

    Sphinx

  • 9. Re: AWR report
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    A database that is consuming much more time on CPU than on I/O is CPU bound.  You reduce dependency on CPU by tuning.  If you can't tune the SQL and schema, you reduce the load or add more CPU cores.

     

    Hemant K Chitale

  • 10. Re: AWR report
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    the numbers you posted could mean anything from "no problem at all" to "a total disaster", depending on the context. The context is determined by:

     

    1) the length of the interval the report was taken for

    2) the number of CPUs on the system

    3) amount of CPU activity on the server by sources other than the database in question

     

    But most importantly, you need to tell us what the problem was. If it was just one query, or one session, or one small group of sessions (pertaining to

    a certain application or module) that was slow, then AWR isn't the right tool for the problem, and you may not be able to find any answers there.

    ASH could be way more helpful (see ASH basics | Oracle Diagnostician for some useful ASH queries).

     

    If the problem you're trying to investigate is global, then AWR might be the useful, but in any case numbers in there are meaningless without context,

    so post the following sections of your report:

     

    1) header (platform, version, RAC/standalone, duration of report etc.)

    2) load profile

    3) OS stats

    4) time model statistics

     

    You can also find some information about reading AWR reports with examples in my blog:

     

    AWR | Oracle Diagnostician

     

    Best regards,

      Nikolay

  • 11. Re: AWR report
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    >one of my AWR report snap during erroneous period

    Can you compare that with an AWR report for a "normal" period of the same duration.

     

    Hemant K Chitale


  • 12. Re: AWR report
    $phinx19 Newbie
    Currently Being Moderated

    Thanks all, give me some time do what you have asked for.

  • 13. Re: AWR report
    $phinx19 Newbie
    Currently Being Moderated

    Hi Nikolay,

     

    Here are some of the snaps of AWR that you have asked for.

     

     

    DB NameDB IdInstanceInst numStartup TimeReleaseRAC
    FCRBRDB23706387376fcrbrdb22206-Oct-13 06:1011.2.0.3.0YES

     

     

     

    Host NamePlatformCPUsCoresSocketsMemory (GB)
    cbbrdb22AIX-Based Systems (64-bit)164 40.00

     

     

    Snap IdSnap TimeSessionsCursors/Session
    Begin Snap:1196017-Oct-13 11:00:3811331.1
    End Snap:1196117-Oct-13 11:30:0211651.0
    Elapsed: 29.40 (mins)
    DB Time: 83.35 (mins)

     

     

    Load Profile

     

    Per SecondPer TransactionPer ExecPer Call
    DB Time(s):2.80.10.010.01
    DB CPU(s):0.70.00.000.00
    Redo size:42,127.9980.1
    Logical reads:4,184.097.3
    Block changes:197.74.6
    Physical reads:1,023.623.8
    Physical writes:40.50.9
    User calls:361.88.4
    Parses:272.76.3
    Hard parses:73.51.7
    W/A MB processed:4.10.1
    Logons:13.50.3
    Executes:506.611.8
    Rollbacks:27.50.6
    Transactions:43.0

     

    Instance Efficiency Percentages (Target 100%)

     

    Buffer Nowait %:99.99Redo NoWait %:100.00
    Buffer Hit %:76.38In-memory Sort %:100.00
    Library Hit %:94.63Soft Parse %:73.06
    Execute to Parse %:46.17Latch Hit %:99.93
    Parse CPU to Parse Elapsd %:34.30% Non-Parse CPU:80.99

     

     

    Time Model Statistics

    • Total time in database user-calls (DB Time): 5001.2s
    • Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
    • Ordered by % or DB time desc, Statistic name
    Statistic NameTime (s)% of DB Time
    sql execute elapsed time2,829.5256.58
    parse time elapsed2,145.3342.90
    hard parse (sharing criteria) elapsed time1,741.8934.83
    hard parse elapsed time1,524.9030.49
    DB CPU1,312.2726.24
    connection management call elapsed time762.1215.24
    failed parse elapsed time249.815.00
    PL/SQL compilation elapsed time144.772.89
    PL/SQL execution elapsed time73.421.47
    hard parse (bind mismatch) elapsed time0.960.02
    repeated bind elapsed time0.290.01
    sequence load elapsed time0.040.00
    DB time5,001.16
    background elapsed time443.61
    background cpu time159.20

    Regards,

    Sphinx

  • 14. Re: AWR report
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    DB CPU usage @ 0.7seconds per second is very low usage for 16 cores (it works out to 4.375%).  You can look at the Operating System Statistics section.

     

    Hemant K Chitale


1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points