Re: High %wio problem

If we take your description at 100% face value, one possibility is that on that one day a monthly routine starts running that never gets to a commit. Consequently as the day passes other processes (and the process itself) have to do more and more work to get read-consistent views of the data.

Look at

    v$sess_io to see if one session is running up a     huge (logical) i/o debt

    v$rollstat to see if the reads of rollback blocks     is escalating, and waits are increasing

    v$filestat to see if physical file reads are increasing     dramatically.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Dave Weeks wrote in message <91stc3$ar8$1_at_reader-00.news.insnet.cw.net>...


>Hi all,

>

>Quick question regarding high %wio.  Every month we get a day where the

 %wio


>is so high it brings the Oracle server to a crawl so I'm wondering what the

>reasons are that can cause this (eliminated insufficient rollback segments,

>tablespace sizes, and archiving of redo logs).  Restarting Oracle cures the

>%wio back to normal.

>

>We have Oracle 8.1.5 EE running on Solaris 7 (5x9Gb disks, raid 5) and have

>a 10Gb database.

>

>Thanks in advance, Dave Weeks.

>

>And here's the output of the 'sar' utility over a typical problem day:

>

>00:00:01   %usr   %sys  %wio  %idle

>01:00:01       0       0       1      99

>02:00:02       0       0       1      99

>03:00:01       1       2       2      98

>04:00:01       0       0       6      94

>05:00:02       0       0       2      98

>06:00:01       0       0       1      98

>07:00:01       0       0       1      98

>08:00:01       0       0       1      98

>08:20:01       0       0       1      99

>08:40:01       0       0       2      97

>09:00:00       0       0       1      98

>09:20:01       0       0       1      99

>09:40:00       1       1       7      90

>10:00:01       0       0       2      98

>10:20:01       0       0       3      97

>10:40:01       1       1       4      94

>11:00:01       0       0       2      98

>11:20:01       1       1       6      92

>11:40:01       1       0       3      96

>12:00:01       0       1       5      93

>12:20:01       0       1       3      96

>12:40:01       0       0       2      98

>13:00:01       0       0       2      98

>13:20:01       0       0       2      98

>13:40:03       0       8      23      68

>14:00:25       0       7      14      79

>14:20:03       0       8      18      75

>14:40:10       1       9      20      70

>15:00:02       0       7      16      76

>15:20:04       0       7      15      78

>15:40:03       0       7      14      79

>16:00:29       0       8      17      75

>16:23:58       1      10      34      54

>16:40:06       2      16      65      18

>

>Average        0       2       9      89

>

>