Rem Filename   : allcpu.sql
Rem 
Rem Parameters : None
Rem
Rem Author     : 
Rem RDBMS      : Oracle V8.x.x, V9.x.x
Rem
Rem Modification history
Rem --------------------
Rem 24-Dec-2000  Initial creation.
Rem 24-Apr-2002  Added the "Idle", "Recursive CPU" and "Parse CPU" columns.
Rem 30-Sep-2003  Fetch statictic#s instead of using constants.
Rem
Rem =================================================================================================
Rem
Rem Description : Lists the CPU usage of all sessions.
Rem
Rem               Username            : The name of the Oracle user connected to the database.
Rem               Session             : The session id and serial number of the Oracle session.
Rem               Idle                : The idle time of a session, if the session is active
Rem                                     then ===Active== is displayed, for the current session
Rem                                      ==Current== is displayed.
Rem               Session CPU (sec)   : Total CPU time in seconds consumed by the session.
Rem               Recursive CPU (sec) : Total recursive CPU time in seconds consumed by the session.
Rem                                     When Oracle needs to make changes to table used for internal 
Rem                                     processing, it internally generates SQL statements (recursive
Rem                                     call). For instance, recursive calls are used to handle the
Rem                                     reloading of cursors if they have to be rebinded after being 
Rem                                     closed. 
Rem               Parse CPU (sec)     : Total parse CPU time in seconds consumed by the session.
Rem 
Rem Sample output
Rem -------------
Rem                                                   Session   Recursive       Parse
Rem User name            Session       Time idle    CPU (sec)   CPU (sec)   CPU (sec)
Rem -------------------- ------------ ----------- ----------- ----------- -----------
Rem ...
Rem LANTHONY             '53,87'        0:55:58        113,52        0,32        0,84
Rem LDEMEYER             '13,1103'    ===Active==        1,49        0,11        0,27
Rem MXXXXXXX             '31,15'      ==Current==        4,60        0,26        0,72
Rem MVRANCKEN            '58,127'       1:30:25          1,29        0,13        0,42
Rem SAMREPO              '8,839'        6:10:56          0,04        0,01        0,00
Rem TSCHAUBR             '40,282'       0:26:15         45,73        0,07        0,46
Rem TSCHAUBR             '48,18'        1:28:06          9,39        0,18        0,44
Rem TSCHAUBR             '49,94'        1:32:43        319,06      318,36        1,52
Rem ...
Rem =================================================================================================

SET PAGESIZE  0
SET VERIFY    OFF
SET FEEDBACK  OFF
SET TERMOUT   OFF

alter session set nls_numeric_characters = ",.";

DEFINE TotalCPUNbr   = 0
DEFINE RecursCPUNbr  = 0
DEFINE ParseCPUNbr   = 0

COLUMN statistic# NOPRINT NEW_VALUE TotalCPUNbr
SELECT statistic# FROM v$statname WHERE name = 'CPU used by this session';

COLUMN statistic# NOPRINT NEW_VALUE RecursCPUNbr
SELECT statistic# FROM v$statname WHERE name = 'recursive cpu usage';

COLUMN statistic# NOPRINT NEW_VALUE ParseCPUNbr
SELECT statistic# FROM v$statname WHERE name = 'parse time cpu';


SET PAGESIZE   9999
SET TERMOUT    ON
SET LINESIZE   1000

CLEAR COLUMNS
COLUMN Username     FORMAT A20         HEADING "User name"
COLUMN Sid          FORMAT A12         HEADING "Session"
COLUMN Idle         FORMAT A11         HEADING " Time idle"
COLUMN SessionCPU   FORMAT 999G990D99  HEADING "Session|CPU (sec)"
COLUMN RecursiveCPU FORMAT 999G990D99  HEADING "Recursive|CPU (sec)"
COLUMN ParseCPU     FORMAT 999G990D99  HEADING "Parse|CPU (sec)"

SELECT v$session.username                                    "UserName",
       ''''||ltrim(to_char(stats.sid))||','
           ||ltrim(to_char(v$session.serial#))||''''         "Sid",
       decode( v$session.audsid - userenv('SESSIONID'),
               0, '==Current==',
               decode( substr(status,1,1), 
                       'A', '===Active==',
                       to_char(floor(last_call_et/3600),'99') || ':'
                       || ltrim(to_char(floor(mod(last_call_et,3600)/60),'09')) || ':'
                       || ltrim(to_char(mod(mod(last_call_et,3600),60),'09')) || ' '
                     )
             )                                              "Idle",
       SessionCPUtime/100                                    "SessionCPU",
       RecursiveCPUtime/100                                  "RecursiveCPU",
       ParseCPUtime/100                                      "ParseCPU"
  FROM v$session,
       ( SELECT sid,
                sum(decode(statistic#, &TotalCPUNbr,  value, 0))    SessionCPUtime,
                sum(decode(statistic#, &RecursCPUNbr, value, 0))    RecursiveCPUtime,
                sum(decode(statistic#, &ParseCPUNbr,  value, 0))    ParseCPUtime
           FROM v$sesstat
          WHERE statistic# in (&TotalCPUNbr,&RecursCPUNbr,&ParseCPUNbr)
       GROUP BY sid ) stats
 WHERE stats.sid = v$session.sid
   AND v$session.username is not null
 ORDER BY 1,2;

PROMPT

UNDEFINE TotalCPUNbr
UNDEFINE RecursCPUNbr
UNDEFINE ParseCPUNbr

SET FEEDBACK ON