We help IT Professionals succeed at work.

Why is there high load (IO wait) for Oracle processes but low disk IO?

dan_glos
dan_glos asked
on
2,154 Views
Last Modified: 2013-12-18
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
Comment
Watch Question

Commented:
Can you verify that the --/etc/security/limits.conf  have the following numbers?

      oracle soft   nproc   2047
      oracle hard  nproc  16384
      oracle soft   nofile   1024
      oracle hard  nofile   65536

Author

Commented:
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
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.