SQL QUERY TO FIND HIGH CPU SESSIONS ORACLE

SQL QUERY TO FIND HIGH CPU SESSIONS ORACLE

On and on i was trying to find a suitable SQL query to find the top 10 CPU sessions which consume high CPU on the oracle database. This script helped me to identify the sessions eating CPU faster

set lines 250
set pages 2000
col username format a15 justify center
col program format a20 justify center
col event format a20 justify center
col sid format 999999 justify center
col CPU_USAGE format 999999999 justify center
col CPUPCT format 9999999999 justify center
col value format 999999
col PCT% format 99999999 justify center 
col blocking_session format 999999 justify center 
set serveroutput off
variable v_cpu number;
begin
select value into :v_cpu from v$parameter where name like '%cpu_count%';
DBMS_OUTPUT.PUT_LINE(:v_cpu);
end;
/
select 
* from (
        select z.sid,
               z.status,
               nvl(z.username,'oracle-bg') as "username",
               nvl(z.SQL_ID,'non-SQL') as "SQL_ID",
               z.EVENT,
               z.program,
               sum(y.value/100) "CPU_USAGE",
               round((y.value/100) / round((sysdate - z.logon_time)*24*60*60)*100,2) * (:v_cpu) as "PCT%", -- Here 1 denotes the number of cpu cores,
               z.blocking_session
from v$statname x
     inner join v$sesstat y on (x.STATISTIC# = y.STATISTIC#)
     inner join v$session z on (y.SID = z.SID)
where x.name in 
('CPU used by this session') 
group by 
        z.sid,z.username,z.SQL_ID,z.logon_time,
        z.EVENT,z.program,z.status,y.value,z.blocking_session
order by 8 desc
)
where rownum < 20;

Keep this script handy whenever you face situation to find the sessions CPU usage is high on your database system. you can also identify if the session runs SQL or non-SQL program from sql_id column and wait events also included.Modify the script accordingly for your environment

CPU usage query with respect to high % order :

set lines 250
set pages 2000
col username format a15 justify center
col program format a20 justify center
col event format a20 justify center
col sid format 999999 justify center
col CPU_USAGE format 999999999 justify center
col CPUPCT format 9999999999 justify center
col value format 999999
col PCT% format 99999999 justify center 
col blocking_session format 999999 justify center 
set serveroutput off
variable v_cpu number;
begin
select value into :v_cpu from v$parameter where name like '%cpu_count%';
DBMS_OUTPUT.PUT_LINE(:v_cpu);
end;
/
select 
* from (
select z.sid,
z.status,
nvl(z.username,'oracle-bg') as "username",
nvl(z.SQL_ID,'non-SQL') as "SQL_ID",
z.EVENT,
z.program,
sum(y.value/100) "CPU_USAGE",
round((y.value/100) / round((sysdate - z.logon_time)*24*60*60)*100,2) * (:v_cpu) as "PCT%", -- Here 1 denotes the number of cpu cores,
z.blocking_session
from v$statname x
inner join v$sesstat y on (x.STATISTIC# = y.STATISTIC#)
inner join v$session z on (y.SID = z.SID)
where x.name in 
('CPU used by this session') 
group by 
z.sid,z.username,z.SQL_ID,z.logon_time,
z.EVENT,z.program,z.status,y.value,z.blocking_session
order by 8 desc
)
where rownum < 20;

Enjoy!