Gvpga (gvpga.sql)

What This Script Does

This script displays sessions ordered by PGA memory usage, helping identify memory-intensive operations and potential memory issues. Essential for PGA tuning and troubleshooting out-of-memory errors.

The Script

rem
set linesize 1000
rem
col inst_id format 99 heading 'RAC|INST'
col sid_serial format a11 heading 'SID,SERIAL#'
col machine format a25
col username format a15 heading 'USERNAME'
col service_name format a10 heading 'SERVICE'
col osuser format a8 heading 'OS|USERNAME'
col seconds_in_wait format b9,999,999 heading 'SECONDS|IN WAIT'
col server format a9 heading 'SERVER'
col module format a30 heading 'MODULE/PROGRAM'

break on username skip 1

select
   s.sid||','||s.serial# sid_serial
     , s.username
     , s.osuser
     , substr(s.service_name,1,10) service_name
     , s.machine
     , substr(s.module||'--'|| s.program,1,22) module
     , s.status
from gv$process p
    ,gv$session s
where p.inst_id = s.inst_id
  and p.ADDR = s.PADDR
  and p.username <> 'ordb'
  and nvl(s.username,'null') like nvl(upper('&user'),'%')
order by p.pga_used_mem desc
/
clear break

Parameters

The script prompts for:

&Username - to filter (use % for all)

Usage

-- Basic usage
@gvpga.sql

-- When prompted, enter:
-- user: Username to filter (use % for all)

Sample Output

SID,SERIAL# USERNAME        OS       SERVICE    MACHINE                   MODULE/PROGRAM                 STATUS
----------- --------------- -------- ---------- ------------------------- ------------------------------ --------

HR          234,5678        HR       jsmith    HR_SERVICE workstation01                    SQL Developer--sqldevelo       ACTIVE
            345,6789        HR       appuser   HR_SERVICE appserver02                      JDBC Thin Client--JDBC         ACTIVE
            456,7890        HR       batch     HR_BATCH   batchserver01                    DataPump--expdp.exe           ACTIVE

SCOTT       567,8901        SCOTT    mjones    USERS      workstation03                    SQL*Plus--sqlplus.exe         INACTIVE
            678,9012        SCOTT    webuser   WEB_APP    webserver01                      Apache--httpd                 ACTIVE

SYS         789,0123        SYS      oracle    SYS$USERS  dbserver01                       RMAN--rman.exe                ACTIVE
            890,1234        SYS      oracle    SYS$USERS  dbserver01                       DBMS_SCHEDULER--ORA$AT         ACTIVE