Script:收集11g Oracle实例IO性能信息

以下脚本可以用于收集Oracle Instance I/O 性能信息：

set linesize 80 pagesize 1400;

SELECT ios.filetype_name,
       df.file_name,
       df.tablespace_name,
       ios.small_read_megabytes + ios.large_read_megabytes read_megabytes,
       ios.small_write_megabytes + ios.large_write_megabytes write_megabytes,
       ios.small_read_reqs + ios.large_read_reqs read_reqs,
       ios.small_write_reqs + ios.large_write_reqs write_reqs,
       ios.small_read_servicetime + ios.large_read_servicetime read_servicetime,
       ios.small_write_servicetime + ios.large_write_servicetime write_servicetime,
       ios.small_read_reqs,
       ios.small_read_servicetime
  FROM v$iostat_file ios, dba_data_files df
 WHERE ios.filetype_name = 'Data File'
   AND df.file_id = ios.file_no
UNION ALL
SELECT ios.filetype_name,
       tf.file_name,
       tf.tablespace_name,
       ios.small_read_megabytes + ios.large_read_megabytes read_megabytes,
       ios.small_write_megabytes + ios.large_write_megabytes write_megabytes,
       ios.small_read_reqs + ios.large_read_reqs read_reqs,
       ios.small_write_reqs + ios.large_write_reqs write_reqs,
       ios.small_read_servicetime + ios.large_read_servicetime read_servicetime,
       ios.small_write_servicetime + ios.large_write_servicetime write_servicetime,
       ios.small_read_reqs,
       ios.small_read_servicetime
  FROM v$iostat_file ios, dba_temp_files tf
 WHERE ios.filetype_name = 'Temp File'
   AND tf.file_id = ios.file_no
UNION ALL
SELECT ios.filetype_name,
       NULL file_name,
       NULL tablespace_name,
       ios.small_read_megabytes + ios.large_read_megabytes read_megabytes,
       ios.small_write_megabytes + ios.large_write_megabytes write_megabytes,
       ios.small_read_reqs + ios.large_read_reqs read_reqs,
       ios.small_write_reqs + ios.large_write_reqs write_reqs,
       ios.small_read_servicetime + ios.large_read_servicetime read_servicetime,
       ios.small_write_servicetime + ios.large_write_servicetime write_servicetime,
       ios.small_read_reqs,
       ios.small_read_servicetime
  FROM v$iostat_file ios
 WHERE ios.filetype_name NOT IN ('Temp File', 'Data File')
/

SELECT function_name,
       small_read_megabytes + small_write_megabytes + large_read_megabytes +
       large_write_megabytes throughput_megabytes,
       small_read_reqs + small_write_reqs + large_read_reqs +
       large_write_reqs throughput_reqs,
       number_of_waits,
       wait_time
  FROM v$iostat_function
/

SELECT * FROM v$iostat_file 
/


col filetype_name format a14 heading "File Type"
col reads format 9,999,999 heading "Reads"
col writes format 9,999,999 heading "Writes"
col read_time_sec format  99,999 heading "Read Time|sec"
col write_time_sec format  99,999 heading "Write Time|sec"
col avg_sync_read_ms format 999.99 heading "Avg Sync|Read ms"
col total_io_seconds format 9\99,999,999 heading "Total IO|sec"

set lines 80
set pages 10000
set echo on 

WITH iostat_file AS 
  (SELECT filetype_name,SUM(large_read_reqs) large_read_reqs,
          SUM(large_read_servicetime) large_read_servicetime,
          SUM(large_write_reqs) large_write_reqs,
          SUM(large_write_servicetime) large_write_servicetime,
          SUM(small_read_reqs) small_read_reqs,
          SUM(small_read_servicetime) small_read_servicetime,
          SUM(small_sync_read_latency) small_sync_read_latency,
          SUM(small_sync_read_reqs) small_sync_read_reqs,
          SUM(small_write_reqs) small_write_reqs,
          SUM(small_write_servicetime) small_write_servicetime
     FROM sys.v_$iostat_file
    GROUP BY filetype_name)
SELECT filetype_name, small_read_reqs + large_read_reqs reads,
       large_write_reqs + small_write_reqs writes,
       ROUND((small_read_servicetime + large_read_servicetime)/1000) 
          read_time_sec,
       ROUND((small_write_servicetime + large_write_servicetime)/1000) 
          write_time_sec,
       CASE WHEN small_sync_read_reqs > 0 THEN 
          ROUND(small_sync_read_latency / small_sync_read_reqs, 2) 
       END avg_sync_read_ms,
       ROUND((  small_read_servicetime+large_read_servicetime
              + small_write_servicetime + large_write_servicetime)
             / 1000, 2)  total_io_seconds
  FROM iostat_file
 ORDER BY 7 DESC
/