IO wait tuning 11gR2

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
sk0227Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BajwaCommented:
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
sk0227Author Commented:
Thanks for quick response.

how do i check?
could you please let me know.
0
sk0227Author Commented:
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

torakeshbCommented:
iotop is a good tool to find the process that takes the most IO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
torakeshbCommented:
You can download iotop from http://guichaz.free.fr/iotop/
0
BajwaCommented:
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
sk0227Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux

From novice to tech pro — start learning today.