Oracle 查看当前执行IO次数最多的SQL

--查询当前执行IO最多的sql，并按大到小来排序 
select cast(buffer_gets/decode(EXECUTions,0,10000,EXECUTions) as int) 平均IO , 
EXECUTions 执行次数,buffer_gets 总逻辑IO,disk_reads 硬盘读取, 
sql_text SQl语句 from v$sqlarea 
where parsing_schema_name='HQT' 
order by buffer_gets desc 
oracle查看执行计划 
你可以在session中set autotrace on,你可以看到统计信息和执行计划 
也可以使用dbms_xplan包去查看执行计划。 
Oracle IO优化心得 
http://wenku.baidu.com/link?url=JsMtHdp5NLN2IqZf06Bbldhcc6tKIqGBf7XhbTwo5o7YV0QkKBgUTtGGGoZhc8j0e4rlJ56eIKtd3GIOIBuKsOdbebfHx9UUu6A7C7J0NbO 
查看IO较大正在运行的session: 

SELECT se.sid,
se.serial#,
pr.SPID,
se.username,
se.status,
se.terminal,
se.program,
se.MODULE,
se.sql_address,
st.event,
st.p1text,
st.p1,
st.p2,
st.p3,
st.STATE,
st.SECONDS_IN_WAIT,
si.physical_reads,
si.block_changes
  FROM v$session se,
v$session_wait st,
v$sess_io si,
v$process pr
WHERE st.sid=se.sid
  AND st.sid=si.sid
  AND se.PADDR=pr.ADDR
  AND se.sid>6
  AND st.wait_time=0
  AND st.event NOT LIKE '%SQL%'
ORDER BY physical_reads DESC