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;