查看最耗temp、undo、cup、磁盘读取、缓冲读取的sql

--查看最耗temp空间的sql语句
select v.sql_text 
  from v$sql v, v$sort_usage s
 where v.hash_value = s.sqlhash
 order by blocks desc;
 
--查看最耗undo空间的sql语句
select a.sql_text, t.ubablk
  from v$session s, v$transaction t, v$sql a
 where s.taddr = t.addr
   and s.prev_hash_value = a.hash_value
 order by ubablk desc;

--列出cpu使用率最高的5个查询：
select sql_text,executions
  from (select sql_text,executions,
               rank() over(order by executions desc) exec_rank
          from v$sql)
 where exec_rank <=5;

--消耗磁盘读取最多的sql top5：
select disk_reads,sql_text
  from (select sql_text,disk_reads,
               dense_rank() over(order by disk_reads desc) disk_reads_rank
         from v$sql)
  where disk_reads_rank <=5;

--找出需要大量缓冲读取（逻辑读）操作的查询：
select buffer_gets,sql_text
  from (select sql_text,buffer_gets,
               dense_rank() over(order by buffer_gets desc) buffer_gets_rank
          from v$sql)
 where buffer_gets_rank<=5;

--查看最耗内存的进程: 一个连接，一般消耗5-10m内存
select max(pga_used_mem)/1024/1024 M from v$process;                      --当前一个process消耗最大的内存
select min(pga_used_mem)/1024/1024 M from v$process where pga_used_mem>0; --process消耗最少内存