麻袋爸爸

分析高CPU消耗的脚本

set lines 1000
set feedback off
set trims on

col username for a12
col en_name for a16
col cn_name for a8
col sid  format 9999 
col PLAN_OWNER_OBJ_COST_CARD_BYTE for a72

drop table tmp_mon_cpu;

create table tmp_mon_cpu nologging as 
SELECT rownum Top, t.*
  FROM (SELECT s.sid,
               round(x.CPU_TIME / x.executions / 1000, 0) per_cpu,
               round(p.pga_used_mem / 1024 / 1024, 2) PGA_USED,
               --u.user_name en_name,
               --u.user_chin_nm cn_name,
               s.username,
               s.status,
               s.last_call_et last_time,
               to_char(s.logon_time,'yyyy-mm-dd hh24:mi:ss') logon_time,
               e.event,
               x.hash_value
          FROM v$sqlarea x, v$session s, v$process p, v$session_wait e --, cas.tuser_profiles u
         WHERE x.executions > 0
           AND x.parsing_user_id <> 0
           and s.sql_hash_value = x.hash_value
           and s.paddr = p.addr
           and s.sid = e.sid
           --and s.username = u.user_id
         ORDER BY x.CPU_TIME / x.executions DESC) t
 WHERE ROWNUM < 6;

spool dba_log\mon_top_cpu\%log_name%
select top, sid, per_cpu "PER_CPU(us)", pga_used "PGA_USED(MB)", username, status, last_time "LAST_TIME(s)", logon_time, event from tmp_mon_cpu;


select s.sid, q.sql_text
  from v$session s, v$sqltext q
 where q.hash_value in (select hash_value from tmp_mon_cpu)
   and s.sql_hash_value = q.hash_value
 order by s.sid, q.piece;

select s.sid,
       p.operation||' '||p.options||' '||p.object_owner||' '||p.object_name||' '||p.cost||' '||p.cardinality||' '||p.bytes "PLAN_OWNER_OBJ_COST_CARD_BYTE"
  from v$session s, v$sql_plan p
 where p.hash_value in (select hash_value from tmp_mon_cpu)
   and s.sql_hash_value = p.hash_value
 order by s.sid, p.id;

spool off

drop table tmp_mon_cpu;

exit;