Solved

IO wait tuning 11gR2

Posted on 2012-03-15
7
938 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
[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
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 7

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 7

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Fine Tune your automatic Updates for Ubuntu / Debian
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

730 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