Oracle Database PGA (Program Global Area)
-- PGA is nonshared memory created by the Oracle database 
-- that contains the data and control information for a
-- server process.  Each server process will have its 
-- own PGA including background processes.  Sum of all
-- PGA is the total or instance PGA.  The size of PGA 
-- is set by the database initialization parameters.
-- List of all PGA objects

SELECT   --owner,
 object_name, object_type,
 status
FROM all_objects
WHERE  INSTR(object_name,'PGA') >0;

=> Oracle 11g PGA Related Objects

-- PGA and memory parameters

SELECT name||' = '||value parameter_info
FROM  v$parameter
WHERE  INSTR(LOWER(name),'pga') > 0
UNION
SELECT name||' = '||value parameter_info
FROM  v$parameter
WHERE  INSTR(LOWER(name),'memory') > 0
AND    INSTR(LOWER(name),'shared') = 0;

=> Oracle 11g PGA and memory parameters

SELECT 
 n.statistic#, CAST(n.name as VARCHAR2(35)) name,
 SUM(s.value)/1024/1024 value_mb 
FROM v$sesstat s, v$statname n
WHERE  n.statistic# = s.statistic#
AND    INSTR(UPPER(n.name),'PGA') > 0
GROUP BY n.statistic#, CAST(n.name as VARCHAR2(35)) 
;

=> Oracle 11g Session PGA memory


SELECT * FROM v$pgastat;

=> Oracle Database v$pgastat setting

SELECT   CAST(program AS VARCHAR2(20)) program,
 PGA_used_mem, PGA_alloc_mem,
 PGA_freeable_mem, PGA_Max_mem 
FROM v$process;

=> Oracle Database PGA Memory - v$process


SELECT 
 pga_target_for_estimate target_4_estimate,
 pga_target_factor  target_factor,
 advice_status,
 bytes_processed  bytes,
 estd_time,
 estd_extra_bytes_rw  xtra_bytes_rw,
 estd_pga_cache_hit_percentage  cache_hit_pct,
 estd_overalloc_count    overalloc_count
FROM v$pga_target_advice
ORDER BY  1;

=> Oracle PGA Target Advice

SELECT
  low_optimal_size/1024 low_kb,
  (high_optimal_size+1)/1024 high_kb,
  estd_optimal_executions      estd_opt_cnt,
  estd_onepass_executions      estd_onepass_cnt,
  estd_multipasses_executions  estd_mpass_cnt
FROM v$pga_target_advice_histogram
WHERE pga_target_factor = 2
AND estd_total_executions != 0
ORDER BY 1;

=> Oracle PGA Target Advice Histogram


-- Note: When OS cannot allocate adequate PGA memory, it
-- throws ORA-04030 error

Oracle SGA (System Global Area) Memory

Reference:
Oracle Database Administrator's Guide 11g Release 2 (11.2) E25494-06
Oracle Database Performance Tuning Guide, 11g Release 1 (11.1) B28274-02
July 2008

Oracle registered trademark of Oracle Corporation.

Last Revised On: September 21st, 2014

  3100