oracle database expert performance tuning buffer pools keep recycle sqlplus query TOAD system trigger plsql programming
Edward Stoever is your
DATABASE-EXPERT
Computer solutions for businesses of all sizes!
(818) 528-4111
 Home  Résumé  Articles  Portfolio  Contact  YouTube 
ORACLE
A View to Find Sessions that Consume CPU

By Edward Stoever
Warning: As a DBA you are responsible for the integrity of your database and each session on it. If you do not understand how this view works or if you are unfamiliar with consumer groups and how they are used, install this view on a test database as a learning tool before jumping in.
CREATE OR REPLACE FORCE VIEW SYSTEM.CPU_PER_SESSION
(SID, SERIAL#, USERNAME, OSUSER, MODULE, 
 STATUS, PROCESS_ID, SCNDS_IDLE, SCNDS_SINCE_LOGON, CPU_USAGE, 
 SWITCH_CODE)
AS 
SELECT /* ©2004 by Edward Stoever,  */
-- Occasionally someone will run a script or a process that will consume 
-- the CPU to a point that everything else slows way down. Querying this 
-- view can help you find which user is at fault, and will help you switch 
-- the offending session to a resource group with restriced use of the CPU. 
-- Look for a session which has a STATUS that IS ACTIVE. This indicates a 
-- script that is still running. Also look to see if the CPU_USAGE IS roughly 
-- the same AS SCNDS_SINCE_LOG (OR more). CPU_USAGE IS actually the seconds 
-- of cpu usage. This number may be higher than total SCNDS_SINCE_LOG IF you 
-- have more than one CPU ON your box. -- Edward Stoever   
	   SID, serial#, username, osuser, MODULE, status, process_id, scnds_idle,
       scnds_since_logon, cpu_usage,
       DECODE
          (xyz,
           2, NULL,
              'BEGIN DBMS_RESOURCE_MANAGER.switch_consumer_group_for_sess('
           || SID
           || ','
           || serial#
           || ',''FTS_WEB''); END;'
          ) AS switch_code
  FROM (SELECT   DECODE (c.username,
                         'SYS', 2,
                         'SYSTEM', 2,
                         NULL, 2,
                         1
                        ) AS xyz, a.SID, c.serial#, c.username, c.osuser,
                 c.MODULE, c.status, d.spid AS "PROCESS_ID",
                 DECODE (c.username,
                         NULL, 0,
                         c.last_call_et
                        ) AS "SCNDS_IDLE",
                 ((SYSDATE - c.logon_time) * 60 * 60 * 24
                 ) AS "SCNDS_SINCE_LOGON",
                 trunc(a.VALUE/100) AS cpu_usage
            FROM v$sesstat a, v$sysstat b, v$session c, v$process d
           WHERE c.SID = a.SID
             AND c.paddr(+) = d.addr
             AND a.statistic# = b.statistic#
             AND b.NAME = 'CPU used by this session'
        ORDER BY xyz, c.status ASC, a.VALUE DESC);

COMMENT ON TABLE SYSTEM.CPU_PER_SESSION IS 'Occasionally someone will run a script 
or a process that will consume the CPU to a point that everything else slows way down. 
Querying this view can help you find which user is at fault, and will help you switch 
the offending session to a resource group with restriced use of the CPU. Look for a 
session which has a STATUS that is ACTIVE. This indicates a script that is still 
running. Also look to see if the CPU_USAGE is roughly the same as SCNDS_SINCE_LOG 
(or more). CPU_USAGE is actually the seconds of cpu usage. This number may be higher 
than total SCNDS_SINCE_LOG if you have more than one CPU on your box. -- Edward Stoever';


CREATE PUBLIC SYNONYM CPU_PER_SESSION FOR SYSTEM.CPU_PER_SESSION;

sample output from
Select * from CPU_PER_SESSION;

SID SERIAL# USERNAME OSUSER MODULE STATUS PROCESS_
ID
SCNDS_
IDLE
SCNDS_
SINCE_
LOGON
CPU_
USAGE
SWITCH_CODE
12 7 GNERAL GURJOBS GURJOBS ACTIVE 750e 1405 2010521 8 begin
DBMS_RESOURCE_MANAGER.
switch_consumer_group_for_sess
(12,7,'FTS_WEB');
END;
72 6303 WEB oracle   INACTIVE 1d09c 2991 363290 1412 begin
DBMS_RESOURCE_MANAGER.
switch_consumer_group_for_sess
(72,6303,'FTS_WEB');
END;
20 9196 WEB oracle   INACTIVE 1a89a 53 363291 1313 begin
DBMS_RESOURCE_MANAGER.
switch_consumer_group_for_sess
(20,9196,'FTS_WEB');
END;
97 13791 WEB oracle   INACTIVE 192a9 81071 363251 439 begin
DBMS_RESOURCE_MANAGER.
switch_consumer_group_for_sess
(97,13791,'FTS_WEB');
END;
86 10011 WTALOR cpadmin JDBC Thn Cl INACTIVE 18e67 65 599891 128 begin
DBMS_RESOURCE_MANAGER.
switch_consumer_group_for_sess
(86,10011,'FTS_WEB');
END;
69 14022 OPS$TIM tim GUAQFLW INACTIVE 23455 0 10970 97 begin
DBMS_RESOURCE_MANAGER.
switch_consumer_group_for_sess
(69,14022,'FTS_WEB');
END;
111 5153 OPS$GIG gig GUAGMNU INACTIVE 17ac3 1365 26072 85 begin
DBMS_RESOURCE_MANAGER.
switch_consumer_group_for_sess
(111,5153,'FTS_WEB');
END;
26 9445 OPS$CHAN chan FGIBDSR INACTIVE 21873 644 27861 47 begin
DBMS_RESOURCE_MANAGER.
switch_consumer_group_for_sess
(26,9445,'FTS_WEB');
END;
100 7634 OPS$LOOP lolo GUAGMNU INACTIVE 2263e 1076 22822 43 begin
DBMS_RESOURCE_MANAGER.
switch_consumer_group_for_sess
(100,7634,'FTS_WEB');
END;
19 14357 OPS$RAZ rszz TSAAREV INACTIVE 230c9 32 14574 34 begin
DBMS_RESOURCE_MANAGER.
switch_consumer_group_for_sess
(19,14357,'FTS_WEB');
END;
94 7795 SYSTEM edward TOAD 7.6 ACTIVE 2547d 0 27528 231  
9 1   ORACLE   ACTIVE 3102 0 2010650 0  
5 1   ORACLE   ACTIVE 36fe 0 2010651 0  
1 1   ORACLE   ACTIVE 2efa 0 2010652 0  
7 1   ORACLE   ACTIVE 3700 0 2010651 0  
3 1   ORACLE   ACTIVE 26fc 0 2010651 0  
2 1   ORACLE   ACTIVE 46fb 0 2010651 0  
4 1   ORACLE   ACTIVE 32fd 0 2010651 0  
6 1   ORACLE   ACTIVE 30ff 0 2010651 0  
8 1   ORACLE   ACTIVE 3101 0 2010650 0  
10 1   ORACLE   ACTIVE 3703 0 2010650 0  
129 12651 SYS lugo TOAD 7.6 INACTIVE 1baf4 349100 357130 28  
©2017 Edward Stoever