If the complaint of poor performance is current, then the
connected sessions are one of the first things to check to see which
users are impacting the system in undesirable ways. There are a
couple of different avenues to take here.
First, you can get an idea of the percentage
that each session is/has taken up with respect
to I/O. One rule of thumb is that if any
session is currently consuming 50% or more of
the total I/O, then that session and its SQL
need to be investigated further to determine
what activity it is engaged in.
If you are a DBA that is just concerned with
physical I/O, then the physpctio.sql
query will provide the information you need:
This script queries the sys.v_$statname,
sys.v_$sesstat, sys.v_$session, and
sys.v_$bgprocess views.
select
sid,
username,
round(100 *
total_user_io/total_io,2) tot_io_pct
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value)
total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
see
code depot for full script
a.statistic#=c.statistic# and
p.paddr (+) =
b.paddr and
b.sid=a.sid and
c.name in
('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)')
group by
b.sid, nvl(b.username,p.name)),
(select
sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat
a
where
a.statistic#=c.statistic# and
c.name in
('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)'))
order by
3 desc;
If you are a DBA that wants to see the total
I/O picture (in other words, both logical and
physical I/O), then use the totpctio.sql
query instead:
The complete listing of the totpctio.sql
query can be obtained from the online Code
Depot
http://www.rampant-books.com/book_2003_1_perf.htm.
This script also queries the sys.v_$statname,
sys.v_$sesstat, sys.v_$session, and
sys.v_$bgprocess views.
Regardless of which query you use, the output
might resemble something like the following:
SID
USERNAME TOT_IO_PCT
--------------------------------
9
USR1
71.26
20
SYS
15.76
5
SMON
7.11
2
DBWR 4.28
12
SYS
1.42
6
RECO
.12
7
SNP0
.01
10
SNP3
.01
11
SNP4
.01
8
SNP1
.01
1
PMON
0
3
ARCH 0
4
LGWR 0
In the above example, a DBA would be prudent
to examine the USR1 session to see what SQL
calls they are making. You can see that
the above queries are excellent weapons that you
can use to quickly pinpoint problem I/O
sessions.
The Ion tool is
the easiest way to analyze AWR disk I/O in Oracle and Ion
allows you to spot session hogs.
Mike Ault, one of the
world's most widely-read Oracle experts, has finally consented to release his
complete collection of more than 450 Oracle scripts.
Mike has priced his
collection of 465 scripts at $39.95, less than a dime per script.
You can download them immediately at this link:
http://www.rampant-books.com/download_adv_mon_tuning.htm
If you're a DBA who's looking for real world
Oracle tuning techniques, Oracle scripts, and
advice on how to get to the heart of critical
Oracle performance problems, then you've come to
the right place.
Oracle Performance Troubleshooting: With
Dictionary Internals SQL & Tuning Scripts
was written by one the world's most widely-read DBAs and
Oracle internals experts. Robin Schumacher
focuses his incredible knowledge of the Oracle
data dictionary into a superb book that shows
how to quickly troubleshoot and correct Oracle
performance problems.
Plus! The online code depot is available
immediately!
http://www.rampant-books.com/book_2003_1_perf.htm
Regards,
Don Burleson
www.dba-oracle.com
www.remote-dba.net
