75 lines
1.9 KiB
SQL
75 lines
1.9 KiB
SQL
@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Session Details
|
|
* Compatibility : 10.1 and above
|
|
* Parameters : 1 - SID
|
|
* 2 - INST_ID (optional, default to 1)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ --------------------------------------------------
|
|
* 03-JUN-15 Vishal Gupta Changed average waits from sec to msec.
|
|
* 27-Aug-04 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
UNDEFINE TOP_EVENT_COUNT
|
|
DEFINE TOP_EVENT_COUNT=10
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
|
|
VARIABLE SID number ;
|
|
VARIABLE INST_ID number ;
|
|
|
|
BEGIN
|
|
:SID := '&&1';
|
|
:INST_ID := NVL('&&2',1);
|
|
IF :INST_ID = '' OR :INST_ID IS NULL THEN
|
|
:INST_ID := 1;
|
|
END IF;
|
|
END;
|
|
/
|
|
|
|
PROMPT
|
|
PROMPT ######### Session Events (Top &&TOP_EVENT_COUNT) ###
|
|
|
|
COLUMN inst_id HEAD "I#" FORMAT 99
|
|
COLUMN SID HEAD "SID" FORMAT 99999
|
|
COLUMN wait_class HEAD "WaitClass" FORMAT a20 TRUNCATE
|
|
COLUMN event HEAD "EventName" FORMAT a40
|
|
COLUMN total_waits HEAD "TotalWaits" FORMAT 999,999,999
|
|
COLUMN total_timeouts HEAD "TotalTimeOuts" FORMAT 9,999,999
|
|
COLUMN time_waited HEAD "TimeWaited (s)" FORMAT 9,999,999
|
|
COLUMN average_wait HEAD "AvgWait (ms)" FORMAT 9,999,999
|
|
COLUMN max_wait HEAD "MaxWait (s)" FORMAT 9,999,999
|
|
|
|
select * from
|
|
(
|
|
SELECT e.sid
|
|
, e.inst_id
|
|
, e.wait_class
|
|
, e.event
|
|
, e.total_waits
|
|
, e.total_timeouts
|
|
, e.time_waited / 100 time_waited
|
|
, e.average_wait * 10 average_wait
|
|
, e.max_wait / 100 max_wait
|
|
FROM gv$session_event e
|
|
WHERE e.sid = :SID
|
|
AND e.inst_id = :INST_ID
|
|
order by e.time_waited desc, e.total_waits desc
|
|
)
|
|
where rownum <= &&TOP_EVENT_COUNT
|
|
/
|
|
|
|
|
|
|
|
@@footer
|