IO Wait too high

[Raminder]

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

[slimdave]

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.

[marist89]

Also, I would look at your %reads vs. %writes and your RAID level.

[Raminder]

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?

[tamilselvan]

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

[abhaysk]

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?