IO Wait too high
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: IO Wait too high

  1. #1
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547

    IO Wait too high

    Oracle7 Server Release 7.3.4.1.0 - Production (yes, it still exists !!) on Solaris

    The IO wait on this box is very high.
    Top view was consistently showing an IO wait around 90% and above.

    We made certain changes, like

    a) Doubled redolog sizes from 10M to 20M since we found log switches occuring too frequently.
    b) Increased DB_BLOCK_BUFFERS from 40,000 to 179,200 (dbblock size is 2K)

    There was no respite even after above changes and IO wait remained consistently high. (It came down to 0 when we shutdown oracle to make the init changes)

    On top of it, backups started failing because of
    ORA-00600: internal error code, arguments: [2103], [0], [0], [1], [900], [], [], []
    SMON: following errors trapped and ignored:
    ORA-01575: timeout waiting for space management resource

    We went into some details and made these changes:

    a) Dropped and recreated Temp tablespace (since it was badly fragmented, had pctincrease 50 and different size segments).
    b) Increased Sort Area Size from 1 to 4 M
    c) Increased DBWRs from 1 to 6 (system has 6 CPUs, don't know why they were keeping it one)
    d) Increased db latches from 3 to 18 (but later found that oracle autoadjusted it to 12 after starting, any clues why??).
    e) db_file_multiblock_read_count from 8 to 32

    After we made the above changes, backups are going fine and IO wait has come down a bit, but it is still abnormal at 45 to 75 %, with idle CPU hovering near to 0-4%.

    Redologs are still filling at 6 to 9 minutes (although I cant find many insert/update/delete statements going on).
    log_buffer 1024000
    log_checkpoint_interval 10000
    log_checkpoint_timeout 0

    Any clues will be highly appreciated.

    Thanks

    Raminder

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I would be looking for general imbalances in the i/o across disks -- not at an aggregate daily or hourly level, but at as fine a granularity of time that you can measure -- for a time when the i/o seem to be v. high.

    Also, I'd look for particular SQL's that are reponsible for the i/o load, and look at tuning them.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Also, I would look at your %reads vs. %writes and your RAID level.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Following is the result of a query on IO efficiency (lifted from Jeff's website !)

    SELECT
    f.tablespace_name ts
    , substr(f.file_name,20,20) fn
    , v.phyrds rds
    , v.phyblkrd blk_rds
    , v.phywrts wrts
    , v.phyblkwrt blk_wrts
    , v.phyrds + v.phywrts rw
    , v.phyblkrd + v.phyblkwrt blk_rw
    , DECODE(v.phyblkrd, 0, null,
    ROUND(100*(v.phyrds + v.phywrts)/
    (v.phyblkrd + v.phyblkwrt), 2 )) eff
    FROM
    dba_data_files f
    , v$filestat v
    WHERE
    f.file_id = v.file#
    ORDER BY
    rds
    /





    Tablespa Filename Reads Blk Rds Wrts Blk Wrts Rds+Wrts Blk Rds+Wrts Effeciency
    -------- -------------------- -------- -------- -------- -------- -------- ------------ ----------------------------------------
    TOOLS /index2/tools01.dbf 0 0 0 0 0 0
    TEMP /system/temp01.dbf 0 0 0 0 0 0
    TEMP /system/temp02.dbf 0 0 0 0 0 0
    TEMP /system/temp04.dbf 0 0 0 0 0 0
    TEMP /system/temp03.dbf 19 576 222 7023 241 7599 3.17
    SYSTEM /system/system02.dbf 1091 1175 0 0 1091 1175 92.85
    RBS /rbs/rbs02.dbf 1154 1154 86943 86943 88097 88097 100
    RBS /rbs/rbs03.dbf 1286 1286 118155 118155 119441 119441 100
    DAPSINDX /index2/dapsindx01.d 1563 1563 8 8 1571 1571 100
    RBS /rbs/rbs01.dbf 1691 1691 107237 107237 108928 108928 100
    DAPINDX /index1/dapindx04.db 3002 3002 3113 3113 6115 6115 100
    DAPS /data2/daps01.dbf 10763 38040 2714 2714 13477 40754 33.07
    SYSTEM /system/system01.dbf 20912 22927 382 382 21294 23309 91.36
    DAPINDX /index1/dapindx01.db 754046 754046 76425 76425 830471 830471 100
    DAPINDX /index1/dapindx03.db 762414 762415 71687 71687 834101 834102 100
    DAP /data1/dap02.dbf 833693 9928368 20943 20943 854636 9949311 8.59
    DAPINDX /index1/dapindx02.db 1110067 1110067 55782 55782 1165849 1165849 100
    DAP /data1/dap01.dbf 8019086 ######## 155542 155542 8174628 ######## 14.15

    18 rows selected.


    BTW, how does one find the RAID level?

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Look at the last 4 datafiles statistics, particulary READS and BLOCK READS columns values, they are almost same. It implies that most of the time single bloc read has been happening in your system. I think your system is OLTP. If it is true, set db_file_multiblock_read_count from 8, NOT 32. Some people say this parameter is taken into account during the full table scan or fast full index scan. I agree that point also.
    Reduce the value and watch your system for a week.

    Also, check the disks and disk controllers - heavily used datafiles can be distributed across all availble controllers.


    Tamil

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: IO Wait too high

    Originally posted by Raminder
    d) Increased db latches from 3 to 18 (but later found that oracle autoadjusted it to 12 after starting, any clues why??).

    Raminder
    What are the values spcified for KEEP & RECYCLE?
    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"

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