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?
Download Note #169706.1 from the link below, and check if you have all the requirements for your oracle version.
With the lower font version you can see older oracle versions too.
Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Unravel a Tricky Query
If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them. …