130 lines
4.2 KiB
SQL
130 lines
4.2 KiB
SQL
|
|
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display top session by statname
|
|
* Usage : @<filename> <statname> [<toprecordcount>]
|
|
* Parameters : 1 - Instance Number (Use % as wildcard)
|
|
* 2 - Number of top record counts
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 11-Apr-12 Vishal Gupta First cut
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
|
|
DEFINE inst_id="&&1"
|
|
DEFINE toprecordcount="&&2"
|
|
|
|
COLUMN _inst_id NEW_VALUE inst_id NOPRINT
|
|
COLUMN _toprecordcount NEW_VALUE toprecordcount NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&inst_id','','%','&&inst_id') "_inst_id"
|
|
, DECODE('&&toprecordcount','','20','&&toprecordcount') "_toprecordcount"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT
|
|
PROMPT **********************************************************************************
|
|
PROMPT * Top &toprecordcount Session by CPU Usage in current call
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Instance Number = '&&INST_ID'
|
|
PROMPT * - Top row count = '&&toprecordcount'
|
|
PROMPT **********************************************************************************
|
|
|
|
|
|
COLUMN name HEADING "Stat Name" FORMAT a40
|
|
COLUMN cpu_time HEADING "CPU Time" FORMAT a12 JUSTIFY RIGHT
|
|
COLUMN "%age" HEADING "%age" FORMAT 999.00
|
|
COLUMN inst_id HEADING "I#" FORMAT 99
|
|
COLUMN sid HEADING "SID" FORMAT 9999
|
|
COLUMN sess HEADING "Session" FORMAT a15
|
|
COLUMN spid HEADING "SPID" FORMAT a6
|
|
COLUMN logon_time HEADING "Logon Time" FORMAT a20
|
|
COLUMN username HEADING "UserName" FORMAT a20
|
|
COLUMN last_call_et HEADING "Last|Call|(sec)" FORMAT 999,999
|
|
COLUMN service_name HEADING "ServiceName" FORMAT a25
|
|
COLUMN osuser HEADING "OS User" FORMAT a20 TRUNCATE
|
|
COLUMN program HEADING "Program" FORMAT a50 TRUNCATE
|
|
COLUMN machine HEADING "Machine" FORMAT a20 TRUNCATE
|
|
|
|
WITH sess AS
|
|
(
|
|
SELECT /*+ materialize */ sn.name
|
|
, ROUND((ss.VALUE - ss2.VALUE)/ 100 ) value
|
|
, p.spid
|
|
, s.*
|
|
FROM gv$session s
|
|
, gv$sesstat ss
|
|
, gv$sesstat ss2
|
|
, v$statname sn
|
|
, v$statname sn2
|
|
, gv$process p
|
|
WHERE s.sid = ss.sid
|
|
AND s.inst_id = ss.inst_id
|
|
AND ss.statistic# = sn.statistic#
|
|
AND s.paddr = p.addr (+)
|
|
AND s.inst_id = p.inst_id (+)
|
|
AND s.sid = ss2.sid
|
|
AND s.inst_id = ss2.inst_id
|
|
AND ss2.statistic# = sn2.statistic#
|
|
AND s.inst_id LIKE '&&INST_ID'
|
|
AND sn.name = 'CPU used by this session'
|
|
AND sn2.name = 'CPU used when call started'
|
|
AND s.type <> 'BACKGROUND'
|
|
AND s.status = 'ACTIVE'
|
|
-- AND DECODE(s.status ,'INACTIVE',s.last_call_et,0) < 60 -- exclude all session inactive for more than 60 sec.
|
|
)
|
|
SELECT c.cpu_time
|
|
, c.sid
|
|
, c.inst_id
|
|
-- , c.sid || ',' || c.serial# || ',@' || c.inst_id sess
|
|
, c.spid
|
|
, c.status
|
|
, to_char(c.logon_time, 'dd-MON-yyyy hh24:mi:ss') logon_time
|
|
, c.username
|
|
, c.last_call_et
|
|
, c.service_name
|
|
, c.osuser
|
|
, c.sql_id
|
|
, c.prev_sql_id
|
|
, c.PROGRAM
|
|
--, c.machine
|
|
FROM
|
|
(SELECT LPAD(FLOOR(a.value / 3600) || 'h '
|
|
|| LPAD(FLOOR(MOD(a.value , 3600 ) / 60),2) || 'm '
|
|
|| LPAD(MOD(a.value , 60 ) ,2) || 's'
|
|
, 12) cpu_time
|
|
, ROUND((a.VALUE / b.total_value) *100, 2) "%age"
|
|
, a.*
|
|
FROM sess a
|
|
, (SELECT DECODE('&&INST_ID', '%' ,'%', inst_id) inst_id
|
|
, SUM(value) total_value
|
|
FROM sess
|
|
GROUP BY DECODE('&&INST_ID', '%' ,'%', inst_id)
|
|
) b
|
|
WHERE a.value > 0
|
|
ORDER BY a.VALUE DESC
|
|
) c
|
|
WHERE rownum <= &&TOPRECORDCOUNT;
|
|
|
|
|
|
UNDEFINE INST_ID
|
|
UNDEFINE TOPRECORDCOUNT
|
|
UNDEFINE statname
|
|
|
|
@@footer
|