oracle 抓出cup执行时间最多，执行次数最多的sql语句

 

遭遇cpu过多占用，表现为%usr很高，top 或者topas中cpu占用最多的进程为oracle server process.

则根据pid可以找出该pid对应的sql_text

select se.username,se.machine,sq.cpu_time,sq.sql_text from
v$process p,v$session se,v$sqlarea sq
where p.addr=se.paddr and se.sql_hash_value=sq.hash_value and p.addr='&pid';

下面的句子列出cpu_time占用top 10的sql

select cpu_time,sql_text
from (select sql_text,cpu_time,
rank() over (order by cpu_time desc) exec_rank
from v$sql
      )
where exec_rank <=10;

执行次数最多的top 10

select sql_text,executions
from (select sql_text,executions,
    rank() over
     (order by executions desc) exec_rank
    from v$sql)
where exec_rank <=10;