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/