When running oracle select queries against a lot of records (7 - 30 million approx) I notice something strange happen to the server load.
When I run an iostat on the oracle partition during the SQL query, the IO read from this partition is around 3MB to 10MB. When I take a physical look at the disks on the server there is hardly any activity (a flash here, a flash there). When the query is cancelled or finished the disks spring back into life flashing like mad and the IO goes back up.
When I run top on the box I find an oracle process has high Wait % of approx 80%+ and the process is in uninterruptable sleep.
These queries used to take around 15 mins to complete but now I give up after many hours of waiting and I cancel them.
I have an Oracle 10G box running on RHEL 4 with the following spec
4 core Xeon
OS on a RAID 1
Oracle on a fast RAID 5
CPU averages <10%
When I am doing disk IO testing copying files, dd, , fio etc I see read bursts of way over 80MB and writes getting close to that as well. So I am fairly sure this is an oracle issue.
Does anyone know what the issue is or how to solve?