how check the reason of IOWait(R) (041)

[Former Member]

Hi,

I'm checking my system blocks and crash, and monitoring with x_cons i see many times:

T392 11 -1 User 0* IOWait(R)(041) 0 0 6793246(s)

T445 12 -1 User 0* IO Wait (R) 0 6 2 10520348(r)

how i can check what IO that thread is waiting for?

regards

Clóvis

[lbreddemann · Accepted solution]

Hi Clóvis,

since these are User tasks (that is SQL sessions) these IOWAITS occur, because the pages required to process the current SQL command weren't found in the data cache.

The difference between both kind of IOWAITS here is, that IOWAIT(R)(041) simply means, that the specific page is already currently beeing read by another process.

So, if these two are the only processes doing IO right now, it might be that T445 actually reads the page from disk, while T392 waits for it to complete.

More important than what page is read here is what statement is executed in both sessions. (this statement can be different though).

You can know that by enabling the COMMAND monitor before the statements are started or running a kind of trace for the SQL commands issued from the application side.

By checking the statements and their execution plans you might find options to reduce the number of pages that need to be touched.

Hope this points you to the right direction.

regards,

Lars

[Former Member · Accepted solution]

Hi, Lars

you are right, I found same SQL statment in both sessions that read a big slice of data, i changed the SQL and problems goes out.

thanks

Clóvis