I/O Bottlenecks

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/

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.