SQL QUERY TO FIND TOP 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

--HIGH CPU FINDER
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 999999.99
with "CPUPCT" as (select round(sum(value),3) as total_cpu from v$sesstat) 
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,
round(sum(y.value)/100,6) as "CPU_USAGE",round(10000000000*round(sum(y.value/100),3)/(select * from CPUPCT),3) as PCT
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.EVENT,z.program,z.status order by CPU_USAGE desc) 
where rownum < 6;

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

Output:

kish@exdbx<>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 999999.99
with "CPUPCT" as (select round(sum(value),3) as total_cpu from v$sesstat)
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,
round(sum(y.value)/100,6) as "CPU_USAGE",round(10000000000*round(sum(y.value/100),3)/(select * from CPUPCT),3) as PCT
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.EVENT,z.program,z.status order by CPU_USAGE desc)
where rownum < 6;
kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>  2    3    4    5    6    7    8    9
  SID   STATUS      USERNAME     SQL_ID               EVENT               PROGRAM        CPU_USAGE         PCT
------- -------- --------------- ------------- -------------------- -------------------- ---------- ----------
     58 INACTIVE TEST            d0urr6ugq9psz PX Deq: Execution Ms oracle@exdbadm01 (P0         13       3.89
                                               g                    05)

     35 INACTIVE TEST            d0urr6ugq9psz SQL*Net message from sqlplus.exe                  11       3.06
                                                client


     14 ACTIVE   oracle-bg       non-SQL       gcs remote message   oracle@exdbadm01 (LM         10       2.99
                                                                    S0)

     34 ACTIVE   SYS             13s8b5hxaczd6 SQL*Net message to c sqlplus@exdbadm01 (T         10       2.92
                                               lient                NS V1-V3)

      1 ACTIVE   oracle-bg       non-SQL       rdbms ipc message    oracle@exdbadm01 (CJ         10       2.77
                                                                    Q0)

Enjoy!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s