?
Solved

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

Posted on 2009-04-06
3
Medium Priority
?
1,619 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 1500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

770 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