Our Oracle databases in Montreal are sometimes experiencing performance problems and the Oracle DBA wants to know if there are I/O Bottlenecks.
If working with a zone (a virtual operating system abstraction), first you will have to find the disk:
$ zpool status
pool: rpool
state: ONLINE
scan: none requested
config:
NAME STATE READ WRITE CKSUM
rpool ONLINE 0 0 0
c1d6 ONLINE 0 0 0 <--- keep"d6"
$ iostat -xd 5
r/s: Reads/second
w/s: Writes/second
kr/s: kilobytes read per second
kw/s: kilobytes written per second
wait: average number of transactions waiting for service (queue length)
actv: average number of transactions actively being serviced
svc_t:average response time of transactions, in milliseconds
%w: percent of time there are transactions waiting for service
%b: percent of time the disk is busy
extended device statistics
device r/s w/s kr/s kw/s wait actv svc_t %w %b
1/md1 56.9 0.0 2104.5 0.0 0.0 0.4 7.9 0 32
1/md0 56.9 0.0 2104.5 0.0 0.0 0.4 7.9 0 32
1/md6 56.9 0.0 2104.5 0.0 0.0 0.4 7.9 0 32
2/md1 0.0 10.8 0.0 86.6 0.0 0.2 16.3 0 18
2/md0 0.0 10.8 0.0 86.6 0.0 0.3 28.1 0 18
2/md210 0.0 0.2 0.0 1.6 0.0 0.0 4.2 0 0
If working with another type of virtual zone, you will have difficulties to find which disks are experiencing high I/O activity.
But hopefully, you can have some help with SQL*Plus:
(1) Look for sessions that are currently waiting for I/O resources:
SELECT username,
program,
machine,
sql_id
FROM v$session
WHERE event LIKE 'db file%read';
(2) which SQL statements are using a lots of disks:
col schema format a20
SELECT *
FROM ( SELECT parsing_schema_name Schema, SQL_ID,
SUBSTR (sql_text, 1, 75) SQL,
disk_reads
FROM v$sql
ORDER BY disk_reads DESC)
WHERE ROWNUM < 20;
And with the result set of (1) or (2):
set long 1000
select SQL_FULLTEXT from v$sql where sql_id='xxxxxxxxxxx'
--> Tune your SQL Statement or execute it less times.
After that you can also check the global Oracle wait events:
http://www.oracle-scripts.net/resources-waits/