Solved

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

Posted on 2009-04-06
3
1,482 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
0
Comment
Question by:dan_glos
  • 2
3 Comments
 
LVL 8

Expert Comment

by:LindaC
ID: 24102872
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

0
 

Author Comment

by:dan_glos
ID: 24105004
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
0
 
LVL 8

Accepted Solution

by:
LindaC earned 500 total points
ID: 24121924
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.


http://www.experts-exchange.com/OS/Unix/AIX/Q_24306934.html
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now