113 lines
5.4 KiB
SQL
113 lines
5.4 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display instance level PGA usage from AWR Repository
|
|
* Compability: 10.x, 11.x
|
|
* Parameters : 1 - DAYS - Default Value - 7
|
|
* 2 - INST_ID - Default Value - %, (Use % as wildcard)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 30-Sep-13 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE DAYS
|
|
UNDEFINE INST_ID
|
|
|
|
DEFINE DAYS="&&1"
|
|
DEFINE INST_ID="&&2"
|
|
|
|
|
|
set term off
|
|
COLUMN _INST_ID NEW_VALUE INST_ID NOPRINT
|
|
COLUMN _DAYS NEW_VALUE DAYS NOPRINT
|
|
SELECT TRIM(DECODE('&&DAYS','',7,'&&DAYS')) "_DAYS"
|
|
, DECODE('&&INST_ID','','%','&&INST_ID') "_INST_ID"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
|
|
DEFINE BYTES_LARGE_FORMAT="9,999,999"
|
|
DEFINE BYTES_FORMAT="99,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"
|
|
|
|
|
|
|
|
PROMPT *-------------------------------------------------------------------*
|
|
PROMPT * I N S T A N C E L E V E L P G A U S A G E S U M M A R Y *
|
|
PROMPT * (From AWR Repository) *
|
|
PROMPT * *
|
|
PROMPT * Input Parameters *
|
|
PROMPT * - Days = '&&DAYS'
|
|
PROMPT * - INST_ID = '&&INST_ID'
|
|
PROMPT *-------------------------------------------------------------------*
|
|
|
|
|
|
COLUMN seperator HEADING "!|!|!" FORMAT a1
|
|
|
|
COLUMN total_allocated HEADING "Total|Alloc|(&&BYTES_HEADING)" FORMAT &&BYTES_LARGE_FORMAT
|
|
COLUMN freeable_allocated HEADING "Freeable|Alloc|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
COLUMN sql_allocated HEADING "SQL|Alloc|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
COLUMN plsql_allocated HEADING "PL/SQL|Alloc|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
COLUMN olap_allocated HEADING "OLAP|Alloc|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
COLUMN java_allocated HEADING "Java|Alloc|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
COLUMN other_allocated HEADING "Other|Alloc|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
|
|
COLUMN total_used HEADING "Total|Used|(&&BYTES_HEADING)" FORMAT &&BYTES_LARGE_FORMAT
|
|
COLUMN freeable_used HEADING "Freeable|Used|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
COLUMN sql_used HEADING "SQL|Used|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
COLUMN plsql_used HEADING "PL/SQL|Used|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
COLUMN olap_used HEADING "OLAP|Used|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
COLUMN java_used HEADING "Java|Used|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
COLUMN other_used HEADING "Other|Used|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
|
|
COLUMN end_interval_time HEADING "Snap Time" FORMAT a15
|
|
COLUMN instance_number HEADING "Instance" FORMAT 9999999
|
|
|
|
SELECT /*+ USE_NL(d s p) */
|
|
TO_CHAR(TRUNC(s.end_interval_time,'MI'),'DD-MON-YY HH24:MI') end_interval_time
|
|
, s.instance_number
|
|
, ROUND(SUM(p.allocated_total)/ &&BYTES_DIVIDER) total_allocated
|
|
, ROUND(SUM(p.used_total)/ &&BYTES_DIVIDER) total_used
|
|
, ROUND(MAX(DECODE(p.category,'Freeable',p.allocated_total,0))/ &&BYTES_DIVIDER) freeable_allocated
|
|
, ROUND(MAX(DECODE(p.category,'Freeable',p.used_total,0))/ &&BYTES_DIVIDER) freeable_used
|
|
, ROUND(MAX(DECODE(p.category,'SQL',p.allocated_total,0))/ &&BYTES_DIVIDER) sql_allocated
|
|
, ROUND(MAX(DECODE(p.category,'SQL',p.used_total,0))/ &&BYTES_DIVIDER) sql_used
|
|
, ROUND(MAX(DECODE(p.category,'PL/SQL',p.allocated_total,0))/ &&BYTES_DIVIDER) plsql_allocated
|
|
, ROUND(MAX(DECODE(p.category,'PL/SQL',p.used_total,0))/ &&BYTES_DIVIDER) plsql_used
|
|
, ROUND(MAX(DECODE(p.category,'OLAP',p.allocated_total,0))/ &&BYTES_DIVIDER) olap_allocated
|
|
, ROUND(MAX(DECODE(p.category,'OLAP',p.used_total,0))/ &&BYTES_DIVIDER) olap_used
|
|
, ROUND(MAX(DECODE(p.category,'Other',p.allocated_total,0))/ &&BYTES_DIVIDER) other_allocated
|
|
, ROUND(MAX(DECODE(p.category,'Other',p.used_total,0))/ &&BYTES_DIVIDER) other_used
|
|
FROM dba_hist_snapshot s
|
|
JOIN v$database d ON s.dbid = d.dbid
|
|
JOIN dba_hist_process_mem_summary p ON p.dbid = s.dbid
|
|
AND p.instance_number = s.instance_number
|
|
AND p.snap_id = s.snap_id
|
|
WHERE s.end_interval_time > sysdate - &&DAYS
|
|
AND s.instance_number LIKE '&&INST_ID'
|
|
GROUP BY TRUNC(s.end_interval_time,'MI')
|
|
, s.instance_number
|
|
ORDER BY TRUNC(s.end_interval_time,'MI')
|
|
, s.instance_number
|
|
;
|
|
|
|
@@footer
|