97 lines
3.1 KiB
SQL
97 lines
3.1 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display pga details of various sessions
|
|
* Compability:
|
|
* Parameters : 1 - INST_ID - Default Value - %, (Use % as wildcard)
|
|
* 2 - SID - Default Value - %, (Use % as wildcard)
|
|
* 3 - Where clause to filter the data.
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 02-Nov-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE INST_ID
|
|
UNDEFINE SID
|
|
UNDEFINE WHERE_CLAUSE
|
|
|
|
DEFINE INST_ID="&&1"
|
|
DEFINE SID="&&2"
|
|
DEFINE WHERE_CLAUSE="&&3"
|
|
|
|
|
|
COLUMN _INST_ID NEW_VALUE INST_ID NOPRINT
|
|
COLUMN _TOP_ROWCOUNT NEW_VALUE TOP_ROWCOUNT NOPRINT
|
|
COLUMN _WHERE_CLAUSE NEW_VALUE WHERE_CLAUSE NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&INST_ID','','%','&&INST_ID') "_INST_ID"
|
|
, TRIM(DECODE('&&SID','','%','&&SID')) "_SID"
|
|
-- , DECODE('&&WHERE_CLAUSE','','','&&WHERE_CLAUSE') "_WHERE_CLAUSE"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
|
|
|
|
PROMPT ***************************************************************
|
|
PROMPT * Process PGA Details
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - INST_ID = '&&INST_ID'
|
|
PROMPT * - SID = '&&SID'
|
|
PROMPT * - WHERE_CLAUSE = "&&WHERE_CLAUSE"
|
|
PROMPT ***************************************************************
|
|
|
|
COLUMN inst_id HEADING "I#" FORMAT 99
|
|
COLUMN sid FORMAT 9999
|
|
COLUMN separator HEADING "!|!|!|!" FORMAT a1
|
|
|
|
SELECT c.sid
|
|
, c.serial#
|
|
, s.type "SessionType"
|
|
, s.username
|
|
, MAX(DECODE(c.name,'session pga memory',c.value)) current_pga
|
|
, MAX(DECODE(m.NAME,'session pga memory max',c.VALUE)) max_pga
|
|
, '|' separator
|
|
, MAX(DECODE(c.NAME,'session uga memory',c.VALUE)) current_uga
|
|
, MAX(DECODE(m.NAME,'session uga memory max',c.VALUE)) max_uga
|
|
, '|' separator
|
|
, MAX(DECODE(e.NAME,'workarea_size_policy',e.VALUE)) param_workarea_size_policy
|
|
, MAX(DECODE(e.name,'hash_area_size',e.value)) param_hash_area_size
|
|
, MAX(DECODE(e.NAME,'sort_area_size',e.VALUE)) param_sort_area_size
|
|
, MAX(DECODE(e.NAME,'sort_area_retained_size',e.VALUE)) param_sort_area_retained_size
|
|
, MAX(DECODE(e.NAME,'bitmap_merge_area_size',e.VALUE)) param_bitmap_merge_area_size
|
|
, '|' separator
|
|
FROM sys.curpgauga c
|
|
, sys.maxpgauga m
|
|
, gv$ses_optimizer_env e
|
|
, gv$session s
|
|
WHERE c.sid = m.sid
|
|
AND c.serial# = m.serial#
|
|
AND e.inst_id = USERENV('Instance')
|
|
AND e.SID = c.SID
|
|
AND s.inst_id = e.inst_id
|
|
AND s.SID = e.SID
|
|
&&WHERE_CLAUSE
|
|
GROUP BY c.sid
|
|
, c.serial#
|
|
, s.type
|
|
, s.username
|
|
;
|
|
|
|
|
|
@@footer
|