SQL queries which is using more CPU resources

Here we'll see how to find heavy or more cpu consumed sql query in oracle

We are facing high CPU load on Linux servers daily at times. Whenever CPU load is high, we'll get the TOP output and if the load is due to oracle database, we'll track currently running sql queries which is using more CPU on database & update the customer when they ask RCA report of high CPU load on servers.

The below query is to find the sql queries which is causing CPU load on server & using more CPU resources currently.

sessions based on cpu usage :
-----------------------------------------
set pages 1000
set lines 1000
col OSPID for a06
col SID for 99999
col SERIAL# for 999999
col SQL_ID for a14
col USERNAME for a15
col PROGRAM for a23
col MODULE for a18
col OSUSER for a10
col MACHINE for a25
select * from (
select p.spid "ospid",
(se.SID),ss.serial#,ss.SQL_ID,ss.username,substr(ss.program,1,22) "program",ss.module,ss.osuser,ss.MACHINE,ss.status,
se.VALUE/100 cpu_usage_sec
from
v$session ss,
v$sesstat se,
v$statname sn,
v$process p
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and ss.username !='SYS' and
ss.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr and value > 0
order by se.VALUE desc);

The output looks like below

sessions based on cpu usage

The above query will give you everything including OS process id (PID)

Compare the above query output with the output of TOP command in Linux by using pid. 

You will get a clear picture about the cpu load in terms of oracle database.

TOP command output

At times pid using more cpu in top command will be an inactive session.

The below query is to find the inactive session and their details 

set pages 1000

set lines 1000

col SPID for a06

col SID for 99999

col SERIAL# for 999999

col SQL_ID for a14

col USERNAME for a10

col PROGRAM for a30

col MODULE for a18

col OSUSER for a15

col MACHINE for a25

select p.spid,s.sid,s.serial#,s.sql_id,s.username,s.status,s.program,s.module,s.osuser,s.machine

from v$session s, v$process p where s.paddr=p.addr and s.status like '%INACTIVE%';


If you want to track the session details by using PID listing in top output when cpu load is high, you can use below query only one time. After getting output, you just put / and enter then it'll ask enter your ospid then paste your PID alone and enter. You'll get the output. You can track as many as you can by viewing top output in parallel in the another session.

set pages 100
set lines 1000
col SPID heading 'SPID' for a06
col SID heading 'SID' for 99999
col SERIAL# heading 'serial' for 999999
col SQL_ID for a14
col USERNAME for a10
col PROGRAM for a17
col MODULE for a08
col OSUSER for a07
col MACHINE for a20
col TRACEFILE for a40
select p.spid,s.sid,s.serial#,s.username,s.status,s.sql_id,s.program,s.module,s.osuser,s.machine,
s.EVENT from v$session s, v$process p where s.paddr=p.addr and p.spid=&ospid;