oracle--查询oracle比较慢的session和sql

1)查询最慢的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

2)查询对应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,
       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 sql_address
  FROM V$SESSION SS, V$SQLTEXT TT
 WHERE SS.SQL_HASH_VALUE = TT.HASH_VALUE
   AND SID = 439;

3)根据sid查找完整sql语句

select sql_text
  from v$sqltext a
 where a.hash_value =
       (select sql_hash_value from v$session b where b.sid = '&sid')
 order by piece asc;

select a.CPU_TIME, --CPU时间 百万分之一（微秒）
       a.OPTIMIZER_MODE, --优化方式
       a.EXECUTIONS, --执行次数
       a.DISK_READS, --读盘次数
       a.SHARABLE_MEM, --占用shared pool的内存多少
       a.BUFFER_GETS, --读取缓冲区的次数
       a.COMMAND_TYPE, --命令类型(3:select,2:insert;6:update;7delete;47:pl/sql程序单元)
       a.SQL_TEXT, --Sql语句
       a.SHARABLE_MEM,
       a.PERSISTENT_MEM,
       a.RUNTIME_MEM,
       a.PARSE_CALLS,
       a.DISK_READS,
       a.DIRECT_WRITES,
       a.CONCURRENCY_WAIT_TIME,
       a.USER_IO_WAIT_TIME
  from SYS.V_$SQLAREA a
 WHERE PARSING_SCHEMA_NAME = 'CHEA_FILL' --表空间
 order by a.CPU_TIME desc;

v$sqltext：存储的是完整的SQL,SQL被分割

v$sqlarea：存储的SQL 和一些相关的信息，比如累计的执行

v$sql：内存共享SQL区域中已经解析的SQL语句。（即时）