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

-- Parameters to check

SHOW PARAMETER  PGA_AGGREGATE_TARGET

-- A non zero value of PGA_AGGREGATE_TARGET has the effect of
-- automatically setting WORKAREA_SIZE_POLICY to AUTO
-- Range 10 MB to 4096 GB - 1

SHOW PARAMETER  WORKAREA_SIZE_POLICY

-- WORKAREA_SIZE_POLICY => AUTO or MANUAL
-- AUTO: automatic sizing of work areas used by memory-intensive operators
-- MANUAL: may result in poor performance and PGA memory utilization 

-- For Reference Only
-- Oracle recommends not to use or change values of sort_area_size
-- SHOW PARAMETER  sort_area_size

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