Tamil

Many thanks for your response.

If the system is well configured (I leave it you for the definition of well configured), you will see less db_file_sequential_read duration.
This is what I had always thought too. However, I began to question my beliefs when the paper from HP stated that async IO with raw is always the optimal choice yet in their own benchmarks db_file_sequential_read was at it's greatest in that very configuration. It is this apparant contradiction that I am trying to understand.

My reason for obsessing over this matter is that the db I've inherited (8.1.7.4 on HPUX 11i 64bit using raw logical volumes) is not utilising async IO (the driver isn't in the kernel). Now the paper would seem to imply that if I configure async IO I'll immediately benefit from improved IO performance. Given, however, that db_file_sequential_read represents the most significant response time component for any query that I've ever looked at on the system I'm worried that async IO will actually degrade performance.

I fully appreciate that reducing the number of LIOs required by a query should also reduce the total amount of time taken waiting on db_file_sequential_read (take care of the logical IOs and the physical ones will take care of themselves as Tom Kyte would say) but this isn't relevent when I'm considering simply enabling async IO for the database.

I have to admit that I hadn't fully considered your point about db_file_sequential_read being related to buffer cache misses when I've been trying to understand all of this. Of course the buffer cache was the same size when testing each config (raw with async, raw without async, cooked with various vxfs mount options), but there could be some factor that led to less blocks being read from cache during the bench marking of raw with async IO.

It's a shame that the authors at HP didn't include their email addresses at the end of the paper, otherwise I'd get in touch and ask them what they thought had reduced cache hits. I do find it curious that they included the statistic when it implies a reduced response time though.

Of course the best course of action would be to test the effect of async io in my environment. Unfortunately, however, I don't really have the ability to simulate a production load in our test environment.