99 lines
2.7 KiB
SQL
99 lines
2.7 KiB
SQL
@@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
|