Why is there high load (IO wait) for Oracle processes but low disk IO?
Hi,
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
12GB RAM
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?
Thanks
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
12GB RAM
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?
Thanks
ASKER
Hi
I have the following:
cat /etc/security/limits.conf
oracle soft nofile 8192
oracle hard nofile 63488
[oracle@server ~]$ ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 8192
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 208895
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
I have the following:
cat /etc/security/limits.conf
oracle soft nofile 8192
oracle hard nofile 63488
[oracle@server ~]$ ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 8192
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 208895
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536