141 lines
4.8 KiB
SQL
141 lines
4.8 KiB
SQL
@@header
|
|
|
|
PROMPT
|
|
PROMPT **********************************************************************************
|
|
PROMPT * INCOMPLETE
|
|
PROMPT **********************************************************************************
|
|
|
|
/*
|
|
*
|
|
* 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
|
|
* 3 - Statname (Use % as wildcard, double quotes for spaces.
|
|
* 4 - Where clause
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 11-Sep-07 Vishal Gupta First cut
|
|
* 27-Mar-12 Vishal Gupta Modified to take instance_id as input parameter to
|
|
* give ability to give topsession at instance level.
|
|
* 15-Jun-12 Vishal Gupta Added last_call_et to output
|
|
* 22-Jun-12 Vishal Gupta Change parameter order to be in line with other scripts.
|
|
*
|
|
*/
|
|
|
|
set lines 3000
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
|
|
DEFINE inst_id="&&1"
|
|
DEFINE toprecordcount="&&2"
|
|
DEFINE statname="&&3"
|
|
DEFINE whereclause="&&4"
|
|
|
|
|
|
|
|
COLUMN _inst_id NEW_VALUE inst_id NOPRINT
|
|
COLUMN _toprecordcount NEW_VALUE toprecordcount NOPRINT
|
|
COLUMN _statname NEW_VALUE statname NOPRINT
|
|
COLUMN _whereclause NEW_VALUE whereclause NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&inst_id','','%','&&inst_id') "_inst_id"
|
|
, DECODE('&&toprecordcount','','10','&&toprecordcount') "_toprecordcount"
|
|
-- , DECODE('&&whereclause','','AND s.type <> ''BACKGROUND'' ','&&whereclause') "_whereclause"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
|
|
--DEFINE SHOW_BACKGROUND_PROCESSES='Y'
|
|
DEFINE SHOW_BACKGROUND_PROCESSES='N'
|
|
|
|
DEFINE COUNT_FORMAT=999,999,999
|
|
--DEFINE COUNT_DIVIDER="1"
|
|
--DEFINE COUNT_HEADING="#"
|
|
DEFINE COUNT_DIVIDER="1000"
|
|
DEFINE COUNT_HEADING="#1000"
|
|
|
|
DEFINE BYTES_FORMAT="999,999"
|
|
--DEFINE BYTES_DIVIDER="1024"
|
|
--DEFINE BYTES_HEADING="KB"
|
|
DEFINE BYTES_DIVIDER="1024/1024"
|
|
DEFINE BYTES_HEADING="MB"
|
|
--DEFINE BYTES_DIVIDER="1024/1024/1024"
|
|
--DEFINE BYTES_HEADING="GB"
|
|
|
|
DEFINE TIME_FORMAT=9,999
|
|
DEFINE TIME_DIVIDER="60"
|
|
DEFINE TIME_HEADING="min"
|
|
|
|
|
|
PROMPT
|
|
PROMPT **********************************************************************************
|
|
PROMPT * Top &toprecordcount Services by Statistic - "&&statname"
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Instance Number = '&&INST_ID'
|
|
PROMPT * - Top row count = '&&toprecordcount'
|
|
PROMPT * - Statistic Name = '&&statname'
|
|
PROMPT * - Where Clause = "&&whereclause"
|
|
PROMPT **********************************************************************************
|
|
|
|
COLUMN name HEADING "Statistic Name" FORMAT a30 TRUNCATE
|
|
COLUMN value HEADING "Value" FORMAT 99,999,999,999,999
|
|
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 last_call_et HEADING "LastCall" FORMAT a12 JUSTIFY RIGHT
|
|
COLUMN logon_time HEADING "Logon Time" FORMAT a20
|
|
COLUMN username HEADING "UserName" FORMAT a20
|
|
COLUMN service_name HEADING "ServiceName" FORMAT a25
|
|
COLUMN osuser HEADING "OS User" FORMAT a20 TRUNCATE
|
|
COLUMN program HEADING "Program" FORMAT a20 TRUNCATE
|
|
COLUMN machine HEADING "Machine" FORMAT a20 TRUNCATE
|
|
|
|
SELECT *
|
|
FROM
|
|
(SELECT a.service_name
|
|
, a.VALUE
|
|
, ROUND((a.VALUE / b.total_value) *100, 2) "%age"
|
|
FROM (SELECT ss.service_name
|
|
, SUM(ss.VALUE) value
|
|
FROM gv$service_stats ss
|
|
WHERE ss.inst_id LIKE '&&inst_id'
|
|
AND LOWER(ss.stat_name) LIKE LOWER('&&statname')
|
|
&&whereclause
|
|
GROUP BY ss.service_name
|
|
) a
|
|
, (SELECT SUM(ss.VALUE) total_value
|
|
FROM gv$service_stats ss
|
|
WHERE ss.inst_id LIKE '&&inst_id'
|
|
AND LOWER(ss.stat_name) LIKE LOWER( '&&statname' )
|
|
&&whereclause
|
|
) b
|
|
--WHERE a.service_name = b.service_name
|
|
ORDER BY a.VALUE DESC
|
|
)
|
|
WHERE rownum <= &&toprecordcount
|
|
;
|
|
|
|
UNDEFINE inst_id
|
|
UNDEFINE toprecordcount
|
|
UNDEFINE statname
|
|
UNDEFINE whereclause
|
|
|
|
@@footer
|