一
24
分析高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;
no comment untill now