@@header

/*
*
*  Author  : Vishal Gupta
*  Purpose : Display Session Statistics
*  Parameters : 1 - SID
*               2 - INST_ID (optional, default to 1)
*               3 - Where Clause
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  16-Mar-12  Vishal Gupta  First Draft
*/

VARIABLE SID VARCHAR2(10) ;
VARIABLE INST_ID VARCHAR2(10) ;

UNDEFINE TOP_ROWCOUNT
UNDEFINE WHERECLAUSE

DEFINE TOP_ROWCOUNT=30
DEFINE WHERECLAUSE="&&3"

BEGIN
	:SID := NULL;
	:INST_ID := NULL;
	:SID := '&&1';
	:INST_ID := NVL('&&2',1);
	IF :INST_ID = '' OR :INST_ID IS NULL THEN
	   :INST_ID := 1;
	END IF;   
END;
/


Prompt
Prompt ***********************************************
Prompt ********  Session Statistics ( Top &TOP_ROWCOUNT rows)
Prompt ***********************************************
Prompt

COLUMN INST_ID                HEADING "I#"              FORMAT 99
COLUMN SPID                                             FORMAT a6
COLUMN SID                                              FORMAT 9999
COLUMN serial#                HEADING "Serial#"         FORMAT 99999
COLUMN username                                         FORMAT a20 TRUNCATE
COLUMN osuser                                           FORMAT a10 TRUNCATE
COLUMN machine                                          FORMAT a20
COLUMN logon_time                                       FORMAT a15
COLUMN service_name                                     FORMAT a21 TRUNCATE
COLUMN program                                          FORMAT a20 TRUNCATE
COLUMN process                                          FORMAT a10
COLUMN last_call_et           HEADING "Last|Call|(s)"   FORMAT 99,999        

COLUMN name            HEADING "Statistic Name"          FORMAT a40
COLUMN value           HEADING "Value"                   FORMAT 999,999,999,999,999
COLUMN class           HEADING "Class"                   FORMAT a10

SELECT * 
FROM
( 
	SELECT ss.inst_id
		 , ss.sid
   , s.username
   , s.osuser
   , s.program
		 , DECODE(sn.class
		        , 1, 'User'
			    , 2, 'Redo'
			    , 4, 'Enqueue'
			    , 8, 'Cache'
			    , 16, 'OS'
			    , 32, 'RAC'
			    , 64, 'SQL'
			    , 128, 'Debug'
			    , sn.class
		   ) class
		 , sn.name
		 , ss.value 
	FROM gv$sesstat ss
	   , v$statname sn
    , gv$session s
	WHERE s.inst_id     = ss.inst_id
   AND s.sid         = ss.sid
   AND ss.statistic# = sn.statistic# 
	  AND s.sid         LIKE :SID
	  AND s.inst_id     LIKE :INST_ID
   &&WHERECLAUSE
	ORDER BY value desc  
)
WHERE ROWNUM <= &TOP_ROWCOUNT
;


@@footer