High CPU load analysis in ORACLE with OS PID

What will you do if there is a high load in in your ORACLE database server?

I am sure that you can do something if you can identify what is the oracle database session causes the load and what is the SQL being run by the session.

We can identify the operating system process id (OSPID) which consumes high CPU from the output of "top" command in Linux. You can see from the output of top, whether it is oracle sessions causes the load or something other than the database.

Below is the query/script to display top 15 sessions in the order of CPU consumption with all the session details including SQL ID and OSPID. We can compare the top output and the output of the below query to analyze what are the top CPU consuming sessions.

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 a20
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,ss.program,ss.module,ss.osuser,ss.MACHINE,ss.status,
se.VALUE/100 cpu_usage_seconds
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)
where rownum <16;

The above query will display only ACTIVE sessions, you can remove the given condition to get INACTIVE sessions as well. Also it has to be noted that the output of the above query will list in the order of total CPU usage by the sessions. Not by the current CPU usage by the sessions or the database sessions caused the spike. 

We can modify the above query to get all the sessions listed in the top output so that we can get the sessions which are causing the high CPU usage at the given point of time. The modified query will be like

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 a20
col MODULE for a18
col OSUSER for a10
col MACHINE for a25
select p.spid "ospid",
(se.SID),ss.serial#,ss.SQL_ID,ss.username,ss.program,ss.module,ss.osuser,ss.MACHINE,ss.status,
se.VALUE/100 cpu_usage_seconds
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 is not null
and ss.paddr=p.addr and value > 0
and p.spid in (give the PIDs from the top output separated by comma)
order by se.VALUE desc;

Now, what we have is the sessions caused high CPU load in our server. While giving the PIDs in the above modified query to find oracle sessions consuming high CPU, make sure that the PIDs are of the oracle process itself.

It is also necessary to check the details of the queries. whether the SQL statements causes high CPU utilization in the database has been running for long time? How long this query is being run? When this is expected to finish? How much percentage of the activity has been done?

We can check all these details from the view V$SESSION_LONG_OPS. Below given query can be used to do the same.

set lines 1000
COLUMN percent FORMAT 999.99 
col start_time for a20
col message for a45
SELECT sid,SERIAL#,SQL_ID,to_char(start_time,'dd-mm-yy hh24:mi:ss') start_time,
ELAPSED_SECONDS/60 Elapsed_Mins,TIME_REMAINING/60 "TIME_REMAINING_Mins" ,message,( sofar/totalwork)* 100 percent 
FROM v$session_longops
WHERE sofar/totalwork < 1;

Here we have SQL ID and other session details. We can compare this with the previous output for analysis.

After the analysis you may reach in a conclusion that a particular statement is responsible for the high CPU load. To find out the SQL text of that particular SQL, We can use the below given query

select sql_text from v$sqltext where sql_id ='&SQL_ID' order by piece;

I hope you must have found out what is the exact reason of the high CPU.