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
Solved

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

Posted on 2009-04-06
3
1,518 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
Oracle DBLINKS From 11g to 8i 3 48
oracle date format checking 7 26
UTL_FILE invalid file operation 5 28
DECLARATION OF CURSOR IS INCOMPLETE OR MALFORMED 5 24
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

828 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