背景

在我们维护数据库库过程中，经常会碰到数据库调优的问题。而cpu利用率高是我们最常见的一个问题，而找出哪些session和和SQL语句在消耗最多的CPU，是我们经常面对的一个课题。本文分享一下在ORACLE中如何找出消耗CPU最多的SQL语句。

解决方案Oracle 中的前 10 个 CPU 消耗会话

col program form a30 heading "Program"col CPUMins form 99990 heading "CPU in Mins"select rownum as rank, a.*from (SELECT v.sid,sess.Serial# ,program, v.value / (100 * 60) CPUMinsFROM v$statname s , v$sesstat v, v$session sessWHERE s.name = 'CPU used by this session'and sess.sid = v.sidand v.statistic#=s.statistic#and v.value>0ORDER BY v.value DESC) awhere rownum < 11;

col program form a30 heading "Program"col CPUMins form 99990 heading "CPU in Mins"select rownum as rank, a.*from (SELECT v.sid,sess.Serial# ,program, v.value / (100 * 60) CPUMinsFROM v$statname s , v$sesstat v, v$session sessWHERE s.name = 'CPU used by this session'and sess.sid = v.sidand v.statistic#=s.statistic#and v.value>0ORDER BY v.value DESC) awhere rownum < 11;

col program form a30 heading "Program"col cpu_usage_sec form 99990 heading "CPU in Seconds"col MODULE for a18col OSUSER for a10col USERNAME for a15col OSPID for a06 heading "OS PID"col SID for 99999col SERIAL# for 999999col SQL_ID for a15select * from (select p.spid "ospid",(se.SID),ss.serial#,ss.SQL_ID,ss.username,substr(ss.program,1,30) "program",ss.module,ss.osuser,ss.MACHINE,ss.status,se.VALUE/100 cpu_usage_secfrom v$session ss,v$sesstat se,v$statname sn,v$process pwherese.STATISTIC# = sn.STATISTIC#and NAME like '%CPU used by this session%'and se.SID = ss.SIDand ss.username !='SYS'and ss.status='ACTIVE'and ss.username is not nulland ss.paddr=p.addr and value > 0order by se.VALUE desc);

col cpu_usage_sec form 99990 heading "CPU in Seconds"select * from (select(se.SID),substr(q.sql_text,80),ss.module,ss.status,se.VALUE/100 cpu_usage_secfrom v$session ss,v$sesstat se,v$statname sn, v$process p, v$sql qwherese.STATISTIC# = sn.STATISTIC#AND ss.sql_address = q.addressAND ss.sql_hash_value = q.hash_valueand NAME like '%CPU used by this session%'and se.SID = ss.SIDand ss.username !='SYS'and ss.status='ACTIVE'and ss.username is not nulland ss.paddr=p.addr and value > 0order by se.VALUE desc);