PGA Diagnostics

Do not forget a very important Oracle memory buffer which is the PGA (Program Global Area)! Most of us are tempted to look first of all at the SGA, especially at the library cache, whenever users complain that their SQLs run poorly. Of course, the library cache is a proper place to look at for problems, as well as the disk activity, CPU load and so many, many others. However, do NOT forget the PGA, because an inappropriate configuration can lead to a lot of performance issues.

So, reading through the "Database Performance Tuning Guide" I have found the section which talk about the tuning of the PGA. I am reading it quite often but it's still difficult to remember all the related fixed views and queries which are in connection with this topic. Therefore I have decided to put all the nice queries into a script which should give me a rough image of the PGA configuration.

Below is the script which I would like to share with all whom might be interested:

-- PGA Report
-- Displays various statistics regarding the PGA usage

set linesize 140
set pagesize 9999
set heading off
set feedback off

-- general statistics
select 'SECTION 1: GENERAL STATISTICS FOR THE PGA' from dual;
select '=================================================================='
  || '=========================' from dual;

set heading on
select name,
       decode(unit, 'bytes', round(value / 1024 / 1024, 2), value) value,
       decode(unit, 'bytes', 'MB', '') unit
  from v$pgastat;

set heading off
select 'Check the following:' from dual;
select '  - "aggregate PGA auto target" should not be too small in comparison ' 
  || 'with the "aggregate PGA target parameter"' from dual;
select '  - "global memory bound" should not be lower than 1M' from dual;
select '  - "over allocation count" should be near 0 or should not increasing in time' from dual;
select '  - the bigger "cache hit percentage", the better' from dual;
select '' from dual;

set heading on
SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
    FROM (SELECT name, value cnt, (sum(value) over ()) total
    FROM V$SYSSTAT 
    WHERE name like 'workarea exec%');
set heading off

-- PGA used by all DB processes
select 'SECTION 2: PGA USED BY CURRENT DB PROCESSES (IN MB), ORDER BY PGA_ALLOC_MEM' from dual;
select '=================================================================='
  || '=========================' from dual;
set heading on

break on report;
compute sum label '--> TOTAL' of pga_used_mem on report;
compute sum label '--> TOTAL' of pga_alloc_mem on report;
compute sum label '--> TOTAL' of pga_freeable_mem on report;
compute sum label '--> TOTAL' of pga_max_mem on report;

SELECT PROGRAM,
       round(PGA_USED_MEM / 1024 / 1024, 2) pga_used_mem,
       round(PGA_ALLOC_MEM / 1024 / 1024, 2) pga_alloc_mem,
       round(PGA_FREEABLE_MEM / 1024 / 1024, 2) pga_freeable_mem,
       round(PGA_MAX_MEM / 1024 / 1024, 2) pga_max_mem
  FROM V$PROCESS
 order by pga_alloc_mem desc;
  
set heading off
select 'The columns have the following meaning:' from dual;
select '  - PGA_USED_MEM = PGA memory currently used by the process' from dual;
select '  - PGA_ALLOC_MEM = PGA memory currently allocated by the process (including free ' 
  || 'PGA memory not yet released to the operating system by the server process)' from dual;
select '  - PGA_FREEABLE_MEM = Allocated PGA memory which can be freed' from dual;
select '  - PGA_MAX_MEM = Maximum PGA memory ever allocated by the process' from dual;
select '' from dual;

set feedback off;
select 'SECTION 3: USED PGA MEMORY BY CATHEGORIES (VALUES IN MB ORDER DESC BY ALLOCATED_MB).' from dual;
select '=================================================================='
  || '=========================' from dual;
set heading on
select category,
       round(sum(allocated) / 1024 / 1024, 2) allocated_mb,
       round(sum(used) / 1024 / 1024, 2) used_mb,
       round(sum(max_allocated) / 1024 / 1024, 2) max_allocated_mb
  from v$process_memory
 group by category
 order by 2 desc;

set heading off
select '' from dual;

-- workareas histogram
select 'SECTION 4: SQL WORKAREAS HISTOGRAM' from dual;
select '=================================================================='
  || '=========================' from dual;
set heading on
SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
       (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
       OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS
  FROM V$SQL_WORKAREA_HISTOGRAM
 WHERE TOTAL_EXECUTIONS != 0;

set heading off
select '' from dual;

-- active workareas
select 'SECTION 5: CURRENTLY ACTIVE WORKAREAS' from dual;
select '=================================================================='
  || '=========================' from dual;
set heading on
set feedback on
SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
       operation_type OPERATION,
       trunc(EXPECTED_SIZE/1024) ESIZE,
       trunc(ACTUAL_MEM_USED/1024) MEM,
       trunc(MAX_MEM_USED/1024) "MAX MEM",
       NUMBER_PASSES PASS,
       trunc(TEMPSEG_SIZE/1024/1024) TSIZE_MB
  FROM V$SQL_WORKAREA_ACTIVE
 ORDER BY 1,2;
set heading off
set feedback off
select 'The meaning of the above columns is:' from dual;
select '  - SID = the active session identifier' from dual;
select '  - OPERATION = the type of the operation' from dual;
select '  - ESIZE = the expected size for the sql workarea' from dual;
select '  - MEM = Amount of PGA memory (in KB) currently allocated on behalf of this work area.' from dual;
select '  - MAX MEM = Maximum memory amount (in KB) used by this work area' from dual;
select '  - PASS = Number of passes corresponding to this work area (0 if running in OPTIMAL mode)' from dual;
select '  - TSIZE_MB = Size (in megabytes) of the temporary segment used on behalf of this work area. '
  || 'This column is NULL if this work area has not (yet) spilled to disk.' from dual;
select '' from dual;

-- top 10 sql with gurmand sql areas
select 'SECTION 6: OP 10 WORK AREAS REQUIRING MOST CACHE MEMORY' from dual;
select '=================================================================='
  || '=========================' from dual;
set heading on
SELECT *
  FROM (SELECT distinct s.sql_id,
                        substr(s.SQL_TEXT, 1, 50) sql_text,
                        operation_type,
                        estimated_optimal_size,
                        max_tempseg_size
          FROM V$SQL_WORKAREA a, V$SQLSTATS s
         WHERE a.SQL_ID = s.SQL_ID
         ORDER BY estimated_optimal_size)
 WHERE ROWNUM <= 10;

set heading off
select 'SECTION 7: SQLs WITH WORK AREAS THAT HAVE BEEN EXECUTED IN ONE OR EVEN MULTIPLE PASSES' from dual;
select '=================================================================='
  || '=========================' from dual;
set heading on
set feedback on
SELECT sql_text, sum(ONEPASS_EXECUTIONS) onepass_cnt,
       sum(MULTIPASSES_EXECUTIONS) mpass_cnt 
FROM V$SQL s, V$SQL_WORKAREA wa 
WHERE s.address = wa.address 
GROUP BY sql_text 
HAVING sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0;
set feedback off
set heading off

select 'SECTION 8: PGA TARGET ADVCE' from dual;
select '=================================================================='
  || '=========================' from dual;
show parameter pga_aggregate_target;
set heading on
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
       ESTD_OVERALLOC_COUNT
  FROM V$PGA_TARGET_ADVICE;


set heading off
select '' from dual;
pause press any key to exit...
exit

Have fun!