@@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