IO

IO

IO is a wait but it is a wait that all databases have to so at some point in time. Data has to be read of disk and reading off of disk causes IO wait time. Whether IO wait time is acceptable or not really depends on the application but we can make some general observations such as an optimize disk subsystem should be able to render reads in 10ms. If not and the system spends a lot of time waiting for IO then there is the opportunity to improve throughput. IO waits break down into groups such as IO done by sorts that overflow memory buffers and a written to disk in the temporary tables and read out again. If we see this happening we can investigate increasing memory sort area sizes. Finally we can check the db cache advice and see if there would be any benefit to change the buffer cache size. Once these areas have been checked, it really comes down to investigating the SQL and seeing if they can be optimized either through change the SQL or adding structures such as indexes or if the application logic or architecture can be optimized.

select name from V$EVENT_NAME where WAIT_CLASS=‘User I/O'