Solved

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

Posted on 2009-04-06
3
1,568 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

718 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