Enormous wait times!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Enormous wait times!

  1. #1
    Join Date
    Feb 2002
    Location
    Brussels-Belgium
    Posts
    8

    Enormous wait times!

    Hi everyone,

    When I ran the following query on a database that has been up for one month I was shocked:

    select sum(time_waited)/360000 "hours waited since startup"
    from v$system_event
    where event not like 'SQL*Net%' and event not like '%timer' and event not like '%ipc%';

    The query returned a total wait time around 70 hours! Almost 65 hours come from the 'db file sequential read' event.

    Do these figures look normal or does this database have a serious I/O problem? (Or am I missing some essential point?)

    Thanks!
    Dorian

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Not possible to tell really without knowing how much work was actually done in this time period. Is there an apparant performance problem in a particular application operation, or at particular times of the day/week/month?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Code:
    WW04_PRO_DBA>  select INSTANCE_NAME, STARTUP_TIME, Sysdate - STARTUP_TIME "Num Of Days Since It Started"
      2  from v$instance;
    
    INSTANCE_NAME    STARTUP_T Num Of Days Since It Started
    ---------------- --------- ----------------------------
    ww04             14-FEB-04         23.95717592592592593
    
    1 row selected.
    
    Elapsed: 00:00:00.07
    WW04_PRO_DBA> ed
    Wrote file afiedt.buf
    
      1  select
      2    event,
      3    round((time_waited)/360000/24) "days waited since startup"
      4  from
      5    v$system_event
      6  where
      7    event like 'db%' and
      8*   round((time_waited)/360000/24) > 0
    WW04_PRO_DBA> /
    
    EVENT                                    days waited since startup
    ---------------------------------------- -------------------------
    db file sequential read                                         24
    db file scattered read                                          10
    
    2 rows selected.
    
    Elapsed: 00:00:00.07
    WW04_PRO_DBA>
    We have hell lotta Batch Jobs .. and hell lotta Reporting Web Pages that acces data .. and u can see the effect .. 24 days ( nearly 576 hours ) of wait time in only 23.7 days !!

    But still we dont have performance issues ..

    Abhay.
    Last edited by abhaysk; 03-09-2004 at 11:58 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Mar 2004
    Posts
    6
    You said:
    > The query returned a total wait time around 70 hours! Almost 65 hours come from the 'db file sequential read' event
    > on a database that has been up for one month

    Okay. You can't really determine if this is this good or is this bad without more information.

    In the case of db file sequential read event waits, this refers to waiting for disk I/O when doing indexed accessing.

    There are several things to look for, though.
    (1) Run IOSTAT to see if you have unbalanced drive access - see if most of your activity is going through one I/O controller to a limited number of disks? If so, which datafiles are on those disks? Which tables are in those datafiles?
    (2) Run Statspack (15 minute durations) for peak periods and look for SQL doing high physical read activity (or perhaps you have a lot of write activity).
    (3) Is the DBBC set up for good pool usage, and is it big enough? Not having the DBBC configured well will drive more accesses from disk rather than from RAM.

    Etcetera.

  5. #5
    Join Date
    Feb 2002
    Location
    Brussels-Belgium
    Posts
    8
    ok, thanks.
    this database has no apparent performance problems, that was why I was so surprised.
    In fact we could say that even if some Oracle process is waiting for something it does not mean that other processes can't be going on. I overlooked that.

    thanks,
    Dorian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width