Solved

IO wait tuning 11gR2

Posted on 2012-03-15
7
924 Views
Last Modified: 2012-03-28
hello experts,

today we noticed that IO waits on the linux server is high. i have two database are running on the box.

i have verified that database awr reports seems to be fine. now i want to trouble shoot from server level.
i want to find which process is cause in IO.

here is the sar output :


09:40:01 AM       CPU     %user     %nice   %system   %iowait    %steal     %idle
09:50:01 AM       all      1.44      0.00      0.52     13.79      0.00     84.25
10:00:01 AM       all      1.46      0.00      0.53     13.08      0.00     84.93
10:10:01 AM       all      1.51      0.00      0.55     13.82      0.00     84.12
10:20:01 AM       all      1.49      0.00      0.54     13.93      0.00     84.04
10:30:01 AM       all      1.52      0.00      0.54     14.31      0.00     83.62
10:40:01 AM       all      1.50      0.00      0.52     13.80      0.00     84.18
10:50:01 AM       all      1.56      0.00      0.55     14.47      0.00     83.41
11:00:01 AM       all      1.56      0.00      0.54     14.53      0.00     83.37
11:10:01 AM       all      1.60      0.00      0.56     15.60      0.00     82.24
11:20:01 AM       all      1.55      0.00      0.55     15.30      0.00     82.60
11:30:01 AM       all      1.59      0.00      0.56     15.24      0.00     82.61
11:40:01 AM       all      1.84      0.00      0.59     17.46      0.00     80.10
11:50:01 AM       all      1.75      0.00      0.57     19.61      0.00     78.08
12:00:01 PM       all      1.87      0.00      0.61     20.04      0.00     77.48
0
Comment
Question by:sk0227
  • 3
  • 2
  • 2
7 Comments
 
LVL 5

Expert Comment

by:Bajwa
ID: 37726659
Looking at these SAR report it looks like that system is using up more than user.  Check what else is running on them machine apart from the database.
0
 

Author Comment

by:sk0227
ID: 37726664
Thanks for quick response.

how do i check?
could you please let me know.
0
 

Author Comment

by:sk0227
ID: 37726671
oops, my bad,

out put is  not formatted correctly
09:40:01 AM       CPU     %user     %nice   %system   %iowait    %steal     %idle
09:50:01 AM       all      1.44      0.00      0.52     13.79      0.00     84.25
10:00:01 AM       all      1.46      0.00      0.53     13.08      0.00     84.93
10:10:01 AM       all      1.51      0.00      0.55     13.82      0.00     84.12
10:20:01 AM       all      1.49      0.00      0.54     13.93      0.00     84.04
10:30:01 AM       all      1.52      0.00      0.54     14.31      0.00     83.62
10:40:01 AM       all      1.50      0.00      0.52     13.80      0.00     84.18
10:50:01 AM       all      1.56      0.00      0.55     14.47      0.00     83.41
11:00:01 AM       all      1.56      0.00      0.54     14.53      0.00     83.37
11:10:01 AM       all      1.60      0.00      0.56     15.60      0.00     82.24
11:20:01 AM       all      1.55      0.00      0.55     15.30      0.00     82.60
11:30:01 AM       all      1.59      0.00      0.56     15.24      0.00     82.61
11:40:01 AM       all      1.84      0.00      0.59     17.46      0.00     80.10
11:50:01 AM       all      1.75      0.00      0.57     19.61      0.00     78.08
12:00:01 PM       all      1.87      0.00      0.61     20.04      0.00     77.48

Open in new window

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 6

Accepted Solution

by:
torakeshb earned 250 total points
ID: 37727976
iotop is a good tool to find the process that takes the most IO
0
 
LVL 6

Expert Comment

by:torakeshb
ID: 37727980
You can download iotop from http://guichaz.free.fr/iotop/
0
 
LVL 5

Assisted Solution

by:Bajwa
Bajwa earned 250 total points
ID: 37729163
This is little better.  I/O wait tells me that You are hitting disk more than you should

1.  Check if Full table scans are happening (db file scattered read) in v$session_wait
logon as a DBA user and Run this query periodically (check the increasing wait event)
IF RAC then
select event,count(*) from gv$session_wait group by event order by 2
/

If not a RAC DB then
select event,count(*) from v$session_wait group by event order by 2
/

If you see 'db file scattered read' constantly going up? then most likely it is Full Table Scans.

---

If this is true then we can figure out the oracle session and/or transaction that is doing it.
0
 

Author Comment

by:sk0227
ID: 37731445
Hello Experts,

Thanks for the suggestion.
we are using ASM as a storage technology. we noticed that one of the disk /dev/sdg is continuously  busy (more than 50%).nmon tool is reported this information. most of the time it is doing read and doing few writes.

here my problem is i am running to instance on this host. i need to find which database is
doing more reads on the disk.

on one database i did see two queries is doing full table scan. one table has 50k record and
another table as 500k records.

i am looking for following information.

what are the oracle process are using /running on /dev/sdg disk.

could you please guide me.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Postfix issues with spam/auth attempts under NAT 9 82
How to get maximum transfer speed over LAN 4 80
Choosing CentOS 16 79
MULTIPLE DATE QUERY 15 77
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

776 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