查询IO相关SQL

--查询当前执行IO最多的sql，并按大到小来排序
select parsing_schema_name, 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 = 'AML2010' --属主
 order by buffer_gets desc;


--查找前十条性能差的sql
select *
  from (select parsing_user_id,
               executions,
               sorts,
               command_type,
               disk_reads,
               sql_text
          from v$sqlarea
         order by disk_reads desc)
 where rownum <= 10;
 
--查看占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;


select b.spid,
       c.piece,
       a.username,
       a.machine,
       a.program,
       a.sid,
       a.serial#,
       a.status,
       c.piece,
       c.sql_text,
       d.sql_fulltext
  from v$session a, v$process b, v$sqltext c, v$sqlarea d
 where b.addr = a.paddr
   and a.sql_address = c.address(+)
   and c.sql_id = d.sql_id
   and a.sql_id = d.sql_id
   and b.spid = '10438'
 order by c.piece
 
 
--查询走哪些SQL走了全表扫描
select distinct a.sql_text, a.sql_id, b.options, a.last_load_time, b.operation
  from v$sql a, v$sql_plan b
 where a.sql_id = b.sql_id
   and a.last_load_time like '2015-04-15%'
   and b.options = 'FULL'
   and b.operation = 'TABLE ACCESS'