FindingSessionswithHighCPUUsage

If database server CPU usage is showing 100%, or high 90%, DBA needs to find out which session is hogging the CPU(s) and
take appropriate action.

企业建站必须是能够以充分展现企业形象为主要目的，是企业文化与产品对外扩展宣传的重要窗口，一个合格的网站不仅仅能为公司带来巨大的互联网上的收集和信息发布平台，成都创新互联面向各种领域：火锅店设计等网站设计、成都全网营销推广解决方案、网站设计等建站排名服务。

I will explain in the following that how can we find out the session(s) which are excessively using CPU.

v$statname and v$sesstat dictionary view contains different metrics values, and in this example we will
be checking values of 2 metrics highlighted in red bellow.

These metrics would help to find out CPU usage by the sessions.

SQL> set linesize 150
col name format a70
select statistic#,name
from v$statname
where upper(name) like '%CPU%';

STATISTIC# NAME
---------- ----------------------------------------------------------------------
         0 OS CPU Qt wait time
        10 recursive cpu usage
        16 CPU used when call started
        17 CPU used by this session
        61 IPC CPU used by this session
        64 global enqueue CPU used by this session
       229 gc CPU used by this session
       248 cell physical IO bytes sent directly to DB node to balance CPU
       581 parse time cpu

9 rows selected.

STATISTIC# NAME
---------- ----------------------------------------------------------------------
         0 OS CPU Qt wait time
        10 recursive cpu usage
        18 CPU used when call started
        19 CPU used by this session
        77 IPC CPU used by this session
        80 global enqueue CPU used by this session
       253 gc CPU used by this session
       275 cell physical IO bytes sent directly to DB node to balance CPU
       622 parse time cpu

9 rows selected.

If we want to check which session is top consumer of the CPU currently, we can use following script to find it out.
Script for 11g and bellow

alter session set nls_date_format='Dd-MON-YY HH24:MI:SS';

set lines 250
set pages 2000
col name format a26
col sid format 99999
col username format a15
col program format a40
col SESS_CPU_SECS wra format 999,999,999.99
col LAST_CPU_SECS wra format 999,999,999.99
col logon_secs  wra format 999,999,999
col Percent  wra format 999.99

select sess_cpu.sid,
       NVL(sess_cpu.username, 'Oracle Process') username,
       sess_cpu.status,
       sess_cpu.logon_time, 
       round((sysdate - sess_cpu.logon_time)*1440*60) logon_SECS,
       sess_cpu.value/100 SESS_CPU_SECS,
       (sess_cpu.value - call_cpu.value)/100 LAST_CPU_SECS,
       round ((sess_cpu.value/100)/round((sysdate - sess_cpu.logon_time)*1440*60)*100,2) Percent,
       sess_cpu.sql_id         
from  (select se.sql_id,ss.statistic#,se.sid, se.username, se.status, se.program, se.logon_time, sn.name, ss.value
       from v$session se, v$sesstat ss, v$statname sn
       where se.sid=ss.sid
       and sn.statistic#=ss.statistic#
       and sn.name in ('CPU used by this session') ) sess_cpu,
            (select ss.statistic#,se.sid, ss.value, value/100 seconds from v$session se, v$sesstat ss, v$statname sn
where se.sid=ss.sid
and sn.statistic#=ss.statistic#
and sn.name in ('CPU used when call started') ) call_cpu
where sess_cpu.sid=call_cpu.sid
order by SESS_CPU_SECS desc;

   SID USERNAME        STATUS   LOGON_TIME             LOGON_SECS   SESS_CPU_SECS   LAST_CPU_SECS PERCENT SQL_ID
------ --------------- -------- -------------------- ------------ --------------- --------------- ------- -------------
   168 Oracle Process  ACTIVE   23-SEP-17 16:33:38      6,743,707       17,328.94       17,328.94     .26
     4 Oracle Process  ACTIVE   23-SEP-17 16:33:19      6,743,726        1,104.15        1,104.15     .02
   248 Oracle Process  ACTIVE   23-SEP-17 16:33:19      6,743,726          757.68          757.68     .01
   258 Oracle Process  ACTIVE   23-SEP-17 16:34:01      6,743,684          545.56          545.56     .01
    12 Oracle Process  ACTIVE   23-SEP-17 16:34:00      6,743,685          506.23          506.23     .01
    85 Oracle Process  ACTIVE   23-SEP-17 16:33:19      6,743,726          426.53          426.53     .01
    96 HIDS_AUDITOR    INACTIVE 04-DEC-17 19:14:47        513,238          264.17             .00     .05
    84 Oracle Process  ACTIVE   23-SEP-17 16:33:19      6,743,726          239.57          239.57     .00
   246 Oracle Process  ACTIVE   23-SEP-17 16:33:19      6,743,726          181.17          181.17     .00
   106 DBSNMP          INACTIVE 09-DEC-17 04:13:33        135,312           49.85             .00     .04
   273 DBSNMP          INACTIVE 04-NOV-17 18:11:31      3,109,034           38.04             .00     .00
   173 Oracle Process  ACTIVE   23-SEP-17 16:34:00      6,743,685           35.15           35.15     .00
   166 Oracle Process  ACTIVE   23-SEP-17 16:33:19      6,743,726           25.01           25.01     .00
   172 Oracle Process  ACTIVE   23-SEP-17 16:33:51      6,743,694           24.51           24.51     .00
   193 SHEC_USER       INACTIVE 09-DEC-17 21:46:15         72,150            5.41             .02     .01
   103 SHEC_USER       INACTIVE 09-DEC-17 21:45:35         72,190            2.41             .00     .00
    34 SHEC_USER       INACTIVE 09-DEC-17 21:45:35         72,190            1.80             .00     .00
    14 PUBLIC          INACTIVE 05-NOV-17 14:52:38      3,034,567            1.71             .00     .00
    20 SHEC_USER       INACTIVE 09-DEC-17 21:45:29         72,196            1.42             .00     .00
   101 Oracle Process  ACTIVE   09-DEC-17 00:50:37        147,488             .67             .67     .00
   192 SHEC_USER       INACTIVE 09-DEC-17 21:45:35         72,190             .60             .00     .00
   270 SHEC_USER       INACTIVE 09-DEC-17 21:45:35         72,190             .51             .23     .00
   196 SYS             ACTIVE   10-DEC-17 17:29:12          1,173             .40             .00     .03 ff0k8584k3x9j
    99 SHEC_USER       INACTIVE 09-DEC-17 21:45:29         72,196             .27             .00     .00
   190 SHEC_USER       INACTIVE 09-DEC-17 21:46:05         72,160             .14             .00     .00
   268 SHEC_USER       INACTIVE 09-DEC-17 21:45:29         72,196             .12             .00     .00
   271 SHEC_USER       INACTIVE 09-DEC-17 21:45:29         72,196             .12             .00     .00
   187 SHEC_USER       INACTIVE 09-DEC-17 21:45:29         72,196             .10             .00     .00
    89 Oracle Process  ACTIVE   23-SEP-17 16:33:49      6,743,696             .09             .09     .00
   263 SHEC_USER       INACTIVE 09-DEC-17 21:45:28         72,197             .08             .00     .00
   255 SHEC_USER       INACTIVE 09-DEC-17 21:46:09         72,156             .06             .00     .00
     6 SHEC_USER       INACTIVE 09-DEC-17 21:45:35         72,190             .06             .00     .00
   182 SHEC_USER       INACTIVE 09-DEC-17 21:45:29         72,196             .05             .01     .00
    98 SHEC_USER       INACTIVE 09-DEC-17 21:45:29         72,196             .04             .00     .00
   177 SHEC_USER       INACTIVE 09-DEC-17 21:45:28         72,197             .04             .00     .00
   272 SHEC_USER       INACTIVE 09-DEC-17 21:45:35         72,190             .04             .00     .00
   264 SHEC_USER       INACTIVE 09-DEC-17 21:45:35         72,190             .04             .00     .00
    23 SHEC_USER       INACTIVE 09-DEC-17 21:45:29         72,196             .03             .00     .00
   184 SHEC_USER       INACTIVE 09-DEC-17 21:45:54         72,171             .02             .00     .00
    28 SHEC_USER       INACTIVE 09-DEC-17 21:45:56         72,169             .02             .00     .00
    19 SHEC_USER       INACTIVE 09-DEC-17 21:45:35         72,190             .02             .00     .00
   256 Oracle Process  ACTIVE   23-SEP-17 16:34:00      6,743,685             .01             .01     .00
   266 SHEC_USER       INACTIVE 09-DEC-17 21:45:55         72,170             .01             .00     .00
   105 SHEC_USER       INACTIVE 09-DEC-17 21:46:13         72,152             .01             .00     .00
    91 Oracle Process  ACTIVE   23-SEP-17 16:34:00      6,743,685             .01             .01     .00
    93 SHEC_USER       INACTIVE 09-DEC-17 21:46:03         72,162             .01             .00     .00
    24 Oracle Process  ACTIVE   10-DEC-17 17:35:53            772             .01             .01     .00
   195 SHEC_USER       INACTIVE 09-DEC-17 21:45:35         72,190             .01             .00     .00
   186 DBSNMP          INACTIVE 10-DEC-17 17:43:12            333             .01             .00     .00
    21 SHEC_USER       INACTIVE 09-DEC-17 21:45:46         72,179             .01             .00     .00
   278 Oracle Process  ACTIVE   10-DEC-17 06:00:00         42,525             .00             .00     .00
   275 SHEC_USER       INACTIVE 09-DEC-17 21:45:59         72,166             .00             .00     .00
   269 SHEC_USER       INACTIVE 09-DEC-17 21:45:45         72,180             .00             .00     .00
   267 SHEC_USER       INACTIVE 09-DEC-17 21:46:19         72,146             .00             .00     .00
   252 Oracle Process  ACTIVE   23-SEP-17 16:33:41      6,743,704             .00             .00     .00
   250 Oracle Process  ACTIVE   23-SEP-17 16:33:38      6,743,707             .00             .00     .00
   247 Oracle Process  ACTIVE   23-SEP-17 16:33:19      6,743,726             .00             .00     .00
   245 Oracle Process  ACTIVE   23-SEP-17 16:33:18      6,743,727             .00             .00     .00
   244 Oracle Process  ACTIVE   23-SEP-17 16:33:25      6,743,720             .00             .00     .00
   197 SHEC_USER       INACTIVE 09-DEC-17 21:45:58         72,167             .00             .00     .00
   183 SHEC_USER       INACTIVE 09-DEC-17 21:45:43         72,182             .00             .00     .00
   181 SHEC_USER       INACTIVE 10-DEC-17 17:45:57            168             .00             .00     .00
   180 SHEC_USER       INACTIVE 09-DEC-17 21:45:29         72,196             .00             .00     .00
   169 Oracle Process  ACTIVE   23-SEP-17 16:33:38      6,743,707             .00             .00     .00
   167 Oracle Process  ACTIVE   23-SEP-17 16:33:27      6,743,718             .00             .00     .00
   165 Oracle Process  ACTIVE   23-SEP-17 16:33:19      6,743,726             .00             .00     .00
   164 Oracle Process  ACTIVE   23-SEP-17 16:33:19      6,743,726             .00             .00     .00
   163 Oracle Process  ACTIVE   23-SEP-17 16:33:18      6,743,727             .00             .00     .00
   104 SHEC_USER       INACTIVE 09-DEC-17 21:45:35         72,190             .00             .00     .00
   100 SHEC_USER       INACTIVE 09-DEC-17 21:45:57         72,168             .00             .00     .00
    97 SHEC_USER       INACTIVE 09-DEC-17 21:45:35         72,190             .00             .00     .00
    94 Oracle Process  ACTIVE   23-SEP-17 16:39:05      6,743,380             .00             .00     .00
    87 Oracle Process  ACTIVE   23-SEP-17 16:33:38      6,743,707             .00             .00     .00
    86 Oracle Process  ACTIVE   23-SEP-17 16:33:31      6,743,714             .00             .00     .00
    83 Oracle Process  ACTIVE   23-SEP-17 16:33:19      6,743,726             .00             .00     .00
    82 Oracle Process  ACTIVE   23-SEP-17 16:33:19      6,743,726             .00             .00     .00
    31 SHEC_USER       INACTIVE 09-DEC-17 21:45:51         72,174             .00             .00     .00
    27 HIDS_AUDITOR    INACTIVE 04-DEC-17 19:14:47        513,238             .00             .00     .00
    26 SHEC_USER       INACTIVE 09-DEC-17 21:46:11         72,154             .00             .00     .00
    25 SHEC_USER       INACTIVE 09-DEC-17 21:46:00         72,165             .00             .00     .00
    22 HIDS_AUDITOR    INACTIVE 04-DEC-17 19:14:45        513,240             .00             .00     .00
    16 Oracle Process  ACTIVE   05-NOV-17 14:45:13      3,035,012             .00             .00     .00
     7 Oracle Process  ACTIVE   23-SEP-17 16:33:38      6,743,707             .00             .00     .00
     5 Oracle Process  ACTIVE   23-SEP-17 16:34:00      6,743,685             .00             .00     .00
     3 Oracle Process  ACTIVE   23-SEP-17 16:33:19      6,743,726             .00             .00     .00
     2 Oracle Process  ACTIVE   23-SEP-17 16:33:19      6,743,726             .00             .00     .00
     1 Oracle Process  ACTIVE   23-SEP-17 16:33:19      6,743,726             .00             .00     .00

87 rows selected.

Script for 12c and above
Column CON_ID is added in the script to list the container id where sessions are connected. If CON_ID is 0, it would mean that this is an internal process/session. If value is 1, it would mean that this session is from container database. Any value other than 0 or 1 would represent a pluggable database sessions. Check v$containers to match container ID with your pluggable database.

alter session set nls_date_format='Dd-MON-YY HH24:MI:SS';

set lines 250
set pages 2000
col name format a26
col username format a15
col program format a40
col SESS_CPU_SECS wra format 999,999,999.99
col LAST_CPU_SECS wra format 999,999,999.99
col logon_secs  wra format 999,999,999
col Percent  wra format 999.99

select sess_cpu.con_id,
       sess_cpu.sid,
       NVL(sess_cpu.username, 'Oracle Process') username,
       sess_cpu.status,
       sess_cpu.logon_time, 
       round ((sysdate-sess_cpu.logon_time)*1440*60) logon_SECS,
       sess_cpu.value/100 SESS_CPU_SECS,
       (sess_cpu.value - call_cpu.value)/100 LAST_CPU_SECS,
       round((sess_cpu.value/100)/round((sysdate - sess_cpu.logon_time)*1440*60)*100,2) Percent,
       sess_cpu.sql_id         
from  (select se.con_id,se.sql_id,ss.statistic#,se.sid, se.username, se.status, se.program, se.logon_time, sn.name, ss.value
       from v$session se, v$sesstat ss, v$statname sn
       where se.sid=ss.sid
         and sn.statistic#=ss.statistic#
         and sn.name in ('CPU used by this session') ) sess_cpu,
         (select se.con_id, ss.statistic#,se.sid, ss.value, value/100 seconds
          from v$session se, v$sesstat ss, v$statname sn
          where se.sid=ss.sid
           and sn.statistic#=ss.statistic#
           and sn.name in ('CPU used when call started') ) call_cpu
where sess_cpu.sid=call_cpu.sid
and sess_cpu.con_id=call_cpu.con_id
order by SESS_CPU_SECS ;

LOGON_SECS column shows total time in seconds this session is connected to the database.
SESS_CPU_SECS columns shows number of seconds this session has spent using CPU.
LAST_CPU_SECS column shows the CPU taken by last execution call ¨C a single SQL may call CPU several times during its execution.
PERCENT column shows the percent of time this session has spent using CPU since this session has connect to the database.
  
Points to note
While on CPU, LAST_CPU_SECS would show zero (session status would be ACTIVE). During execution of SQL(s), session may be switching between CPU and IO - and hence we can see value changing under SESS_CPU_SECS and LAST_CPU_SECS columns as soon as session switches from CPU to IO.
If a session is on CPU, status would be ACTIVE and SESS_CPU_SECS would be showing last value captured for this session, and LAST_CPU_DECS would be 0.
If a session is on IO, status would be ACTIVE and SESS_CPU_SECS would be showing total seconds of CPU taken by the session until now.
LAST_CPU_SECS would show CPU seconds taken during last call to the CPU. I have also seen value of zero under this column even if session is not ACTIVE. I can't figure it out why, but most important thing is total time of CPU taken by the session which is under SESS_CPU_SECS.
See the following example where I have used ORDER BY SESS_CPU_SECS to display sessions which are at the top CPU consumption since log in.

If you see session bellow highlighted in red, it is connected to the database for 799 seconds
and out of which it has spent 260 seconds on CPU which accounts to 32.55 percent of total session time.

In real time scenario, if you see a session spending huge amount time on CPU, have a look at the SQL(s) it is executing (last column showing SQL_ID) and investigate further.
You can modify this script to ?°ORDER BY PERCENT?± to see which session has spent most of its connected time on CPU.