FAQ
Hi,
So I have an idle system, where I run a sql report. The report takes an hour to run. I look at the awr report (30 min interval), and see the io wait events for sequential and scattered reads. The first has an avg wait time of 7ms the second 10ms. These waits as I understand it are physical io requests - correct? The p1 and p2 parameters point to file and block numbers so I guess that makes sense. Anyway I rerun the same report, look at the new awr and now see the same wait events, only with much smaller wait times. Which means data was read from cache - if that's the case why are the same wait events issued? it seems a bit confusing that way.
Thanks

Search Discussions

  • Andrew Kerber at Jun 13, 2010 at 11:28 pm
    On the dba_hist_sysstat table, the stat name you want for a physical read is
    'physical read'

    On Sun, Jun 13, 2010 at 5:45 PM, Oracle Dba Wannabe <
    oracledbawannabe_at_yahoo.com> wrote:
    I suppose what I'm trying to say here is that if the same wait event is
    issued regardless of a physical or logical io request, how can I determine
    if the io subsystem is returning blocks at s reasonable service time.
    Assuming an idle system other than the report, would times under 5ms be
    cache hits and times over be physical. Further anything over 10ms would be a
    bad service time ? appreciate any input on this
    Thanks
    On Mon, 14 Jun 2010 06:31 SGT Oracle Dba Wannabe wrote:

    Hi,
    So I have an idle system, where I run a sql report. The report takes an
    hour to run. I look at the awr report (30 min interval), and see the io wait
    events for sequential and scattered reads. The first has an avg wait time of
    7ms the second 10ms. These waits as I understand it are physical io requests
    - correct? The p1 and p2 parameters point to file and block numbers so I
    guess that makes sense. Anyway I rerun the same report, look at the new awr
    and now see the same wait events, only with much smaller wait times. Which
    means data was read from cache - if that's the case why are the same wait
    events issued? it seems a bit confusing that way.




    --
    http://www.freelists.org/webpage/oracle-l

    --
    Andrew W. Kerber

    'If at first you dont succeed, dont take up skydiving.'

    --
    http://www.freelists.org/webpage/oracle-l
  • Andrew Kerber at Jun 14, 2010 at 1:57 am
    Your awr report will show the time for physical reads and writes.

    On Sun, Jun 13, 2010 at 5:45 PM, Oracle Dba Wannabe <
    oracledbawannabe_at_yahoo.com> wrote:
    I suppose what I'm trying to say here is that if the same wait event is
    issued regardless of a physical or logical io request, how can I determine
    if the io subsystem is returning blocks at s reasonable service time.
    Assuming an idle system other than the report, would times under 5ms be
    cache hits and times over be physical. Further anything over 10ms would be a
    bad service time ? appreciate any input on this
    Thanks
    On Mon, 14 Jun 2010 06:31 SGT Oracle Dba Wannabe wrote:

    Hi,
    So I have an idle system, where I run a sql report. The report takes an
    hour to run. I look at the awr report (30 min interval), and see the io wait
    events for sequential and scattered reads. The first has an avg wait time of
    7ms the second 10ms. These waits as I understand it are physical io requests
    - correct? The p1 and p2 parameters point to file and block numbers so I
    guess that makes sense. Anyway I rerun the same report, look at the new awr
    and now see the same wait events, only with much smaller wait times. Which
    means data was read from cache - if that's the case why are the same wait
    events issued? it seems a bit confusing that way.




    --
    http://www.freelists.org/webpage/oracle-l

    --
    Andrew W. Kerber

    'If at first you dont succeed, dont take up skydiving.'

    --
    http://www.freelists.org/webpage/oracle-l
  • Karl Arao at Jun 14, 2010 at 3:03 am
    You may also want to follow the recent blog of Charles Hooper..

    DB File Scattered Read Wait Event � What is Wrong with this Quote?
    http://hoopercharles.wordpress.com/2010/06/13/db-file-scattered-read-wait-event-what-is-wrong-with-this-quote/

    --
    Karl Arao
    karlarao.wordpress.com
    karlarao.tiddlyspot.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Martin Berger at Jun 14, 2010 at 4:34 am
    Oracle calls sequential and scattered reads 'physical' as these calls
    to get blocks leave the Oracle program. Oracle cannot know anything
    about the 'external' layers, like filesystem cache or even cache
    within a storage array or disk. You can call it a description which
    code path within Oracle was used.

    Your distinction with the limit of 5 ms is artificial and must be set
    up regarding the system your Instance is running on. It might be right
    on your system. With such a distinction you can only say something
    like 'more than x% of all answers of 5 ms or less are from a cache'.
    Keep this in mind if you compare the numbers to real counts e.g. on
    filesystem cache hit.

    You did not tell us anything about your OS, Hardware and where the
    datafiles / Blocks reside. So it's hard to tell if yur lmits of 5 and
    10 ms are reasonable.

    best regards,
    Martin
    I suppose what I'm trying to say here is that if the same wait event
    is issued regardless of a physical or logical io request, how can I
    determine if the io subsystem is returning blocks at s reasonable
    service time. Assuming an idle system other than the report, would
    times under 5ms be cache hits and times over be physical. Further
    anything over 10ms would be a bad service time ? appreciate any
    input on this
    Thanks
    On Mon, 14 Jun 2010 06:31 SGT Oracle Dba Wannabe wrote:

    Hi,
    So I have an idle system, where I run a sql report. The report
    takes an hour to run. I look at the awr report (30 min interval),
    and see the io wait events for sequential and scattered reads. The
    first has an avg wait time of 7ms the second 10ms. These waits as I
    understand it are physical io requests - correct? The p1 and p2
    parameters point to file and block numbers so I guess that makes
    sense. Anyway I rerun the same report, look at the new awr and now
    see the same wait events, only with much smaller wait times. Which
    means data was read from cache - if that's the case why are the
    same wait events issued? it seems a bit confusing that way.
    Thanks
    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Jun 14, 2010 at 5:55 am

    On Sun, Jun 13, 2010 at 11:45 PM, Oracle Dba Wannabe < oracledbawannabe_at_yahoo.com> wrote:

    I suppose what I'm trying to say here is that if the same wait event is
    issued regardless of a physical or logical io request, how can I determine
    if the io subsystem is returning blocks at s reasonable service time.
    Assuming an idle system other than the report, would times under 5ms be
    cache hits and times over be physical. Further anything over 10ms would be a
    bad service time ? appreciate any input on this
    Thanks
    Hi, the sequential and scattered read requests *always* represent physical
    i/o *requests*, that is they correspond directly to requests *outside* of
    the Oracle instance for a filesystem or volume manager disk access. Since
    there is at least one cache in between Oracle and the disk surface - and
    often 2 - these requests can be satisfied from a cache outside of Oracle and
    so complete quickly. Oracle reserves the term 'logical i/o' for all Oracle
    i/o operations, that is all *Oracle* calls to obtain one or more blocks.
    This includes, hopefully many, occasions when the request is for a buffer in
    the buffer cache.
  • Greg Rahn at Jun 14, 2010 at 2:01 pm
    Then I would suggest looking at the SQL and execution plan first to
    determine if the best plan is being used. There is no sense in
    worrying about how fast/slow IO is if the plan is not the best one.

    On Mon, Jun 14, 2010 at 5:36 AM, Oracle Dba Wannabe
    wrote:
    The report itself is actually a problem - to resolve it I understand I need to address the number of i./o's issued by it.
  • Kellyn Pedersen at Jun 14, 2010 at 7:46 pm
    You should also think about tracing this and find out if the db file sequential read waits are on a dictionary object.  The waits could be a bit misleading on what it is it's actually *waiting* for... :)

    Kellyn Pedersen
    Sr. Database Administrator
    I-Behavior Inc.
    http://www.linkedin.com/in/kellynpedersen
    www.dbakevlar.blogspot.com

    "Go away before I replace you with a very small and efficient shell script..."

    On Mon, 6/14/10, Oracle Dba Wannabe wrote:

    From: Oracle Dba Wannabe
    Subject: Re: db file sequential/scattered read - physical or logical io or both?
    To: Oracle-L_at_freelists.org
    Date: Monday, June 14, 2010, 6:36 AM

    Hi All,
    Thanks for the responses. The report itself is actually a problem - to resolve it I understand I need to address the number of i./o's issued by it. That said, I posed this question because I just wanted to try and understand how oracle issues reads. The reason the naming of the wait events confused me - was I've also understood them to mean physical reads. What didn't make sense to me was why after the report completed and I submitted it again there were still wait events for reads issued in awr - I would have thought the data was cached. I had traced each run of the session, but had just not looked into the trace file yet (my mistake) and was only looking at the awr file. Looking at the trace I see the following:

    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch    90966   2752.59    2690.45          0   43989168          0      727721
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total    90968   2752.59    2690.46          0   43989168          0      727721

    Which appears to indicate zero disk reads. However AWR, shows the following:

    Event
    Waits
    Time(s)
    Avg Wait(ms)
    % Total Call Time
    Wait Class

    db file sequential read
    1,191,745
    1,368
    1
    45.3
    User I/O
    There's nothing else running on this system other than this SQL (it was a weekend afterall). So although my trace file shows no physical read events - how do I account for the 1.1 million wait events - The load profile on awr looks like this:

    Per Second
    Per Transaction

    Logical reads:
    15,445.20

    19,592.96

    Block changes:
    38.26

    48.54

    Physical reads:
    658.30

    835.08
    So are the db file sequential reads a result of waits from reads at the storage cache? the file system cache (have filemin_cache at 3% and filemax_cache 10% total memory =120GB), the oracle buffer cache or all three? Or is it just the first 2 and does not include the buffer cache requests as a call for data in the buffer cache does not translate to a read wait event?
    A bit more detail on this system: The server is a 2 Node RAC (10.2.0.4) on HP-UX 11.31, Super Dome, the storage is XP 24k, the db cache size is approx 60GB on each node.
    Thanks once again.

    From: Greg Rahn
    To: oracledbawannabe_at_yahoo.com
    Cc: oracle-l@freelists.org
    Sent: Mon, June 14, 2010 11:23:24 AM
    Subject: Re: db file sequential/scattered read - physical or logical io or both?

    When you run the report via SQL*Plus with autotrace on, (or look at
    the session stats) what is the physical reads & consistant gets
    numbers?  Likely the filesystem cache is providing the "lift" in
    performance.

    On Sun, Jun 13, 2010 at 3:31 PM, Oracle Dba Wannabe
    wrote:
    So I have an idle system, where I run a sql report. The report takes an hour to run. I look at the awr report (30 min interval), and see the io wait events for sequential and scattered reads. The first has an avg wait time of 7ms the second 10ms. These waits as I understand it are physical io requests - correct? The p1 and p2 parameters point to file and block numbers so I guess that makes sense. Anyway I rerun the same report, look at the new awr and now see the same wait events, only with much smaller wait times. Which means data was read from cache - if that's the case why are the same wait events issued? it seems a bit confusing that way.
  • Martin Berger at Jun 15, 2010 at 5:09 am
    have you enabled tracing for the whole instance (or both) or just for
    your user of interrest?

    you are never alone on an instance, especially in recent versions :-)

    regards,
    Martin

    Am 14.06.2010 um 22:17 schrieb Oracle Dba Wannabe:
    Hi Kellyn,
    I did actually have a 10046 trace enabled for this particular run -
    there are no sequential or scattered reads issued in the raw trace
    file - at all. I'll explain what I did - something I left out
    earlier (my bad):

    1a. Bounce both RAC instances
    1b. Run the report on one node of RAC
    1c. Run the same report on the second node of RAC
    1d. Yes, run the same report again on the second node of RAC - the
    tkprof output shown below is a result of that.
    (did all that node changing stuff above, to see what kind of events
    are issued in a RAC environment)

    Why does my AWR show db file sequential read waits - when the raw
    trace file, shows no i.o related wait events (disk below in the
    tkprof output also shows zero) - Well I thought this pretty much was
    an idle system - I thought I was alone turns out I'm not, I had a
    look down the report and came across the following:

    In the SQL Ordered by Gets:

    Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed
    Time (s) SQL Id SQL Module SQL Text
    14,518,479 0 63.74 285.56 1758.08 6mcpb06rctk0x DBMS_SCHEDULER
    call dbms_space.auto_space_adv...

    In the SQL Ordered by Reads

    Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed
    Time (s) SQL Id SQL Module SQL Text
    1,094,006 0 99.99 285.56 1758.08 6mcpb06rctk0x DBMS_SCHEDULER call
    dbms_space.auto_space_adv...

    Somethings wrong with this picture isnt there?
    posed this question because I just wanted to try and understand how
    oracle issues reads. The reason the naming of the wait events
    confused me - was I've also understood them to mean physical reads.
    What didn't make sense to me was why after the report completed and
    I submitted it again there were still wait events for reads issued
    in awr - I would have thought the data was cached. I had traced each
    run of the session, but had just not looked into the trace file yet
    (my mistake) and was only looking at the awr file. Looking at the
    trace I see the following:

    call count cpu elapsed disk query
    current rows
    ------- ------ -------- ---------- ---------- ----------
    ---------- ----------
    Parse 1 0.00 0.00 0 0
    0 0
    Execute 1 0.00 0.00 0 0
    0 0
    Fetch 90966 2752.59 2690.45 0 43989168
    0 727721
    ------- ------ -------- ---------- ---------- ----------
    ---------- ----------
    total 90968 2752.59 2690.46 0 43989168
    0 727721

    Which appears to indicate zero disk reads. However AWR, shows the
    following:

    Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
    db file sequential read 1,191,745 1,368 1 45.3 User I/O

    There's nothing else running on this system other than this SQL (it
    was a weekend afterall). So although my trace file shows no physical
    read events - how do I account for the 1.1 million wait events - The
    load profile on awr looks like this:

    Per Second Per Transaction




    Logical reads: 15,445.20
    19,592.96
    Block changes: 38.26
    48.54
    Physical reads: 658.30
    835.08

    So are the db file sequential reads a result of waits from reads at
    the storage cache? the file system cache (have filemin_cache at 3%
    and filemax_cache 10% total memory =120GB), the oracle buffer cache
    or all three? Or is it just the first 2 and does not include the
    buffer cache requests as a call for data in the buffer cache does
    not translate to a read wait event?
    A bit more detail on this system: The server is a 2 Node RAC
    (10.2.0.4) on HP-UX 11.31, Super Dome, the storage is XP 24k, the db
    cache size is approx 60GB on each node.
    Thanks once again.

    From: Greg Rahn
    To: oracledbawannabe_at_yahoo.com
    Cc: oracle-l@freelists.org
    Sent: Mon, June 14, 2010 11:23:24 AM
    Subject: Re: db file sequential/scattered read - physical or logical
    io or both?

    When you run the report via SQL*Plus with autotrace on, (or look at
    the session stats) what is the physical reads & consistant gets
    numbers? Likely the filesystem cache is providing the "lift" in
    performance.

    On Sun, Jun 13, 2010 at 3:31 PM, Oracle Dba Wannabe
    wrote:
    So I have an idle system, where I run a sql report. The report
    takes an hour to run. I look at the awr report (30 min interval),
    and see the io wait events for sequential and scattered reads. The
    first has an avg wait time of 7ms the second 10ms. These waits as I
    understand it are physical io requests - correct? The p1 and p2
    parameters point to file and block numbers so I guess that makes
    sense. Anyway I rerun the same report, look at the new awr and now
    see the same wait events, only with much smaller wait times. Which
    means data was read from cache - if that's the case why are the same
    wait events issued? it seems a bit confusing that way.

    --
    Regards,
    Greg Rahn
    http://structureddata.org/
    --
    http://www.freelists.org/webpage/oracle-l




    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 13, '10 at 10:31p
activeJun 15, '10 at 5:09a
posts9
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase