check_cpu.sql
prompt ====================================================
prompt == Start of check_cpu.sql
prompt ====================================================
prompt ====================================================
prompt == Check CPU notes
prompt == OS level CPU usage can be monitor by top or topas
prompt == Where you should be able to pin point the top
prompt == process ID
prompt ==
prompt ====================================================
prompt ====================================================
prompt == Show top 20 CPU usage sessions
prompt ====================================================
set lines 300
set pages 300
col machine format a20
col program format a30
col name format a30
select * from
(SELECT se.username, ss.sid,se.machine,se.program, name, se.status, ROUND (value/100) "CPU Usage Seconds", se.sql_id
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
AND name LIKE '%CPU used by this session%'
AND se.sid = ss.SID
AND se.username IS NOT NULL
ORDER BY value DESC)
where rownum <=20;
prompt ====================================================
prompt == Check Sql Text related to above sql_id
prompt ====================================================
SET LONG 100000
select sql_id, SQL_TEXT from v$SQL where SQL_ID in (
select sql_id from
(SELECT se.username, ss.sid,se.machine,se.program, name, se.status, ROUND (value/100) "CPU Usage Seconds", se.sql_id
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
AND name LIKE '%CPU used by this session%'
AND se.sid = ss.SID
AND se.username IS NOT NULL
ORDER BY value DESC)
where rownum <=20
);
prompt ====================================================
prompt == End of check_cpu.sql
prompt ====================================================
No comments:
Post a Comment