Solved

IO wait tuning 11gR2

Posted on 2012-03-15
7
910 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
Comment Utility
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
Comment Utility
Thanks for quick response.

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

Author Comment

by:sk0227
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 6

Accepted Solution

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

Expert Comment

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

Assisted Solution

by:Bajwa
Bajwa earned 250 total points
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

The purpose of this article is to demonstrate how we can use conditional statements using Python.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now