support/oracle
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
oracle/vg/pgausage.sql
root 93039b8489 2026-03-12 20:23:15
2026-03-12 21:23:47 +01:00

281 lines
14 KiB
SQL
Raw Permalink Blame History

@@header
/*
*
* Author : Vishal Gupta
* Purpose : Display pga usage
* Compability: 10.x, 11.x
* Parameters : 1 - INST_ID - Default Value - %, (Use % as wildcard)
* 2 - TOP_ROWCOUNT - Default Value - 30
* 3 - WHERE CLAUSE - Default Value - ''
*
* Revision History:
* ===================
* Date Author Description
* --------- ------------ -----------------------------------------
* 25-Feb-13 Vishal Gupta Added instance level summary
* 16-Oct-12 Vishal Gupta Re-ordered output columns
* 10-Jul-12 Vishal Gupta Created
*
*/
/************************************
* INPUT PARAMETERS
************************************/
UNDEFINE INST_ID
UNDEFINE TOP_ROWCOUNT
UNDEFINE WHERE_CLAUSE
DEFINE INST_ID="&&1"
DEFINE TOP_ROWCOUNT="&&2"
DEFINE WHERE_CLAUSE="&&3"
set term off
COLUMN _INST_ID NEW_VALUE INST_ID NOPRINT
COLUMN _TOP_ROWCOUNT NEW_VALUE TOP_ROWCOUNT NOPRINT
COLUMN _WHERE_CLAUSE NEW_VALUE WHERE_CLAUSE NOPRINT
SELECT DECODE('&&INST_ID','','%','&&INST_ID') "_INST_ID"
, TRIM(DECODE('&&TOP_ROWCOUNT','',30,'&&TOP_ROWCOUNT')) "_TOP_ROWCOUNT"
-- , DECODE('&&WHERE_CLAUSE','','','&&WHERE_CLAUSE') "_WHERE_CLAUSE"
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 * Input Parameters
PROMPT * - INST_ID = '&&INST_ID'
PROMPT * - TOP_ROWCOUNT = '&&TOP_ROWCOUNT'
PROMPT * - WHERE_CLAUSE = '&&WHERE_CLAUSE'
PROMPT *- -------------------------------------------------*
COLUMN seperator HEADING "!|!|!" FORMAT a1
COLUMN seperator1 HEADING "!|!|!|!" FORMAT a1
COLUMN seperator2 HEADING "!|!|!|!" FORMAT a1
COLUMN seperator3 HEADING "!|!|!|!" FORMAT a1
COLUMN seperator4 HEADING "!|!|!|!" FORMAT a1
COLUMN sid FORMAT 9999
COLUMN serial# FORMAT 99999
COLUMN logon_time FORMAT a15
COLUMN status FORMAT a10
COLUMN spid FORMAT a6
COLUMN username FORMAT a20
COLUMN machine FORMAT a20 TRUNCATE
COLUMN program FORMAT a10 TRUNCATE
COLUMN osuser FORMAT a20 TRUNCATE
COLUMN total_allocated HEADING "Total|Alloc|(&&BYTES_HEADING)" FORMAT &&BYTES_LARGE_FORMAT
COLUMN unfreeable HEADING "UnFree|able|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
COLUMN freeable HEADING "Free|able|(&&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 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 total_max_allocated HEADING "Total|Max|Alloc|(&&BYTES_HEADING)" FORMAT &&BYTES_LARGE_FORMAT
COLUMN sql_max_allocated HEADING "SQL|Max|Alloc|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
COLUMN plsql_max_allocated HEADING "PL/SQL|Max|Alloc|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
COLUMN olap_max_allocated HEADING "OLAP|Max|Alloc|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
COLUMN java_max_allocated HEADING "Java|Max|Alloc|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
COLUMN other_max_allocated HEADING "Other|Max|Alloc|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
BREAK ON REPORT
COMPUTE SUM LABEL 'Total' OF total_allocated FORMAT &&BYTES_LARGE_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF unfreeable FORMAT &&BYTES_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF freeable FORMAT &&BYTES_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF sql_allocated FORMAT &&BYTES_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF plsql_allocated FORMAT &&BYTES_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF olap_allocated FORMAT &&BYTES_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF java_allocated FORMAT &&BYTES_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF other_allocated FORMAT &&BYTES_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF total_used FORMAT &&BYTES_LARGE_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF sql_used FORMAT &&BYTES_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF plsql_used FORMAT &&BYTES_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF olap_used FORMAT &&BYTES_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF java_used FORMAT &&BYTES_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF other_used FORMAT &&BYTES_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF total_max_allocated FORMAT &&BYTES_LARGE_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF sql_max_allocated FORMAT &&BYTES_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF plsql_max_allocated FORMAT &&BYTES_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF olap_max_allocated FORMAT &&BYTES_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF java_max_allocated FORMAT &&BYTES_FORMAT ON REPORT
COMPUTE SUM LABEL 'Total' OF other_max_allocated FORMAT &&BYTES_FORMAT ON REPORT
PROMPT
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 *-------------------------------------------------------------------*
COLUMN inst_id HEADING "Instance" FORMAT 9999999
SELECT inst_id
, ROUND(SUM(allocated)/ &&BYTES_DIVIDER) total_allocated
, ROUND(SUM(DECODE(category,'Freeable',allocated,0))/ &&BYTES_DIVIDER) freeable
, ROUND((SUM(allocated) - SUM(DECODE(category,'Freeable',allocated,0)))/ &&BYTES_DIVIDER) unfreeable
, '|' seperator
, ROUND(SUM(DECODE(category,'SQL',allocated,0))/ &&BYTES_DIVIDER) sql_allocated
, ROUND(SUM(DECODE(category,'PL/SQL',allocated,0))/ &&BYTES_DIVIDER) plsql_allocated
, ROUND(SUM(DECODE(category,'OLAP',allocated,0))/ &&BYTES_DIVIDER) olap_allocated
, ROUND(SUM(DECODE(category,'JAVA',allocated,0))/ &&BYTES_DIVIDER) java_allocated
, ROUND(SUM(DECODE(category,'Other',allocated,0))/ &&BYTES_DIVIDER) other_allocated
, '|' seperator
, ROUND(SUM(used)/ &&BYTES_DIVIDER) total_used
, ROUND(SUM(DECODE(category,'SQL',used,0))/ &&BYTES_DIVIDER) sql_used
, ROUND(SUM(DECODE(category,'PL/SQL',used,0))/ &&BYTES_DIVIDER) plsql_used
, ROUND(SUM(DECODE(category,'OLAP',used,0))/ &&BYTES_DIVIDER) olap_used
, ROUND(SUM(DECODE(category,'JAVA',used,0))/ &&BYTES_DIVIDER) java_used
, ROUND(SUM(DECODE(category,'Other',used,0)) / &&BYTES_DIVIDER) other_used
, '|' seperator
FROM gv$process_memory m
WHERE m.inst_id LIKE '&&INST_ID'
GROUP BY inst_id
ORDER BY inst_id
;
PROMPT
PROMPT *-----------------------------------------------------------*
PROMPT * U S E R L E V E L P G A U S A G E S U M M A R Y *
PROMPT *-----------------------------------------------------------*
SELECT * FROM (
SELECT s.username
, ROUND(SUM(allocated)/ &&BYTES_DIVIDER) total_allocated
, ROUND(SUM(DECODE(category,'Freeable',allocated,0))/ &&BYTES_DIVIDER) freeable
, ROUND((SUM(allocated) - SUM(DECODE(category,'Freeable',allocated,0)))/ &&BYTES_DIVIDER) unfreeable
, '|' seperator
, ROUND(SUM(DECODE(category,'SQL',allocated,0))/ &&BYTES_DIVIDER) sql_allocated
, ROUND(SUM(DECODE(category,'PL/SQL',allocated,0))/ &&BYTES_DIVIDER) plsql_allocated
, ROUND(SUM(DECODE(category,'OLAP',allocated,0))/ &&BYTES_DIVIDER) olap_allocated
, ROUND(SUM(DECODE(category,'JAVA',allocated,0))/ &&BYTES_DIVIDER) java_allocated
, ROUND(SUM(DECODE(category,'Other',allocated,0))/ &&BYTES_DIVIDER) other_allocated
FROM gv$process_memory m
, gv$process p
, gv$session s
WHERE m.inst_id = p.inst_id
AND m.pid = p.pid
AND p.inst_id = s.inst_id
AND p.addr = s.paddr
AND m.inst_id LIKE '&&INST_ID'
&&WHERE_CLAUSE
GROUP BY s.username
ORDER BY SUM(allocated) desc nulls last
)
WHERE rownum <= LEAST (&&top_rowcount,10)
;
PROMPT
PROMPT
PROMPT *---------------------------------------*
PROMPT * TOP &&TOP_ROWCOUNT PGA Using Process
PROMPT *---------------------------------------*
COLUMN inst_id HEADING "I#" FORMAT 99
WITH process_memory AS
(
SELECT inst_id
, pid
, SUM(allocated) total_allocated
, SUM(DECODE(category,'Freeable',allocated,0)) freeable
, SUM(allocated) - SUM(DECODE(category,'Freeable',allocated,0)) unfreeable
, SUM(DECODE(category,'SQL',allocated,0)) sql_allocated
, SUM(DECODE(category,'PL/SQL',allocated,0)) plsql_allocated
, SUM(DECODE(category,'OLAP',allocated,0)) olap_allocated
, SUM(DECODE(category,'JAVA',allocated,0)) java_allocated
, SUM(DECODE(category,'Other',allocated,0)) other_allocated
, SUM(NVL(used,0)) total_used
, SUM(DECODE(category,'SQL',used,0)) sql_used
, SUM(DECODE(category,'PL/SQL',used,0)) plsql_used
, SUM(DECODE(category,'OLAP',used,0)) olap_used
, SUM(DECODE(category,'JAVA',used,0)) java_used
, SUM(DECODE(category,'Other',used,0)) other_used
, SUM(NVL(max_allocated,0)) total_max_allocated
, SUM(DECODE(category,'SQL',max_allocated,0)) sql_max_allocated
, SUM(DECODE(category,'PL/SQL',max_allocated,0)) plsql_max_allocated
, SUM(DECODE(category,'OLAP',max_allocated,0)) olap_max_allocated
, SUM(DECODE(category,'JAVA',max_allocated,0)) java_max_allocated
, SUM(DECODE(category,'Other',max_allocated,0)) other_max_allocated
FROM gv$process_memory
GROUP BY inst_id
, pid
)
SELECT * FROM
(
SELECT p.spid
, s.sid
, m.inst_id
, s.username
, DECODE(s.type , 'BACKGROUND',REPLACE(SUBSTR(s.program,INSTR(s.program,'(')+1),')',''),s.program) program
, '|' seperator1
, ROUND(m.total_allocated/ &&BYTES_DIVIDER) total_allocated
, ROUND(m.freeable/ &&BYTES_DIVIDER) freeable
, ROUND(m.unfreeable/ &&BYTES_DIVIDER) unfreeable
, ROUND(m.sql_allocated/ &&BYTES_DIVIDER) sql_allocated
, ROUND(m.plsql_allocated/ &&BYTES_DIVIDER) plsql_allocated
, ROUND(m.other_allocated/ &&BYTES_DIVIDER) other_allocated
, ROUND(m.olap_allocated/ &&BYTES_DIVIDER) olap_allocated
, ROUND(m.java_allocated/ &&BYTES_DIVIDER) java_allocated
-- , '|' seperator2
-- , ROUND(m.total_used/ &&BYTES_DIVIDER) total_used
-- , ROUND(m.sql_used/ &&BYTES_DIVIDER) sql_used
-- , ROUND(m.plsql_used/ &&BYTES_DIVIDER) plsql_used
-- , ROUND(m.other_used/ &&BYTES_DIVIDER) other_used
-- , ROUND(m.olap_used/ &&BYTES_DIVIDER) olap_used
-- , ROUND(m.java_used/ &&BYTES_DIVIDER) java_used
, '|' seperator3
, ROUND(m.total_max_allocated/ &&BYTES_DIVIDER) total_max_allocated
, ROUND(m.sql_max_allocated/ &&BYTES_DIVIDER) sql_max_allocated
, ROUND(m.plsql_max_allocated/ &&BYTES_DIVIDER) plsql_max_allocated
, ROUND(m.other_max_allocated/ &&BYTES_DIVIDER) other_max_allocated
, ROUND(m.olap_max_allocated/ &&BYTES_DIVIDER) olap_max_allocated
, ROUND(m.java_max_allocated/ &&BYTES_DIVIDER) java_max_allocated
, '|' seperator4
FROM process_memory m
, gv$process p
, gv$session s
WHERE m.inst_id = p.inst_id
AND m.pid = p.pid
AND p.inst_id = s.inst_id
AND p.addr = s.paddr
AND m.inst_id LIKE '&&INST_ID'
&&WHERE_CLAUSE
ORDER BY m.total_allocated desc
)
WHERE rownum <= &&top_rowcount
;
@@footer
Reference in New Issue View Git Blame Copy Permalink