?
Solved

IO wait tuning 11gR2

Posted on 2012-03-15
7
Medium Priority
?
963 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 7

Accepted Solution

by:
torakeshb earned 1000 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 1000 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
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.
Suggested Courses
Course of the Month13 days, 7 hours left to enroll

800 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