Oracle PGA REPORT ...
pga report
PGA Diagnostics
from web http://oracle-cookies.blogspot.com/2007/08/pga-diagnostics.html
-- PGA Report
-- Displays various statistics regarding the PGA usage
set linesize 300 pagesize 400 heading off 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 feedback on
col OPERATION for a20
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 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 linesize 200
col sql_text for a50
col operation_type for a20
SELECT *
FROM (SELECT distinct s.sql_id,
operation_type,
estimated_optimal_size,
max_tempseg_size,
substr(s.SQL_TEXT, 1, 50) sql_text
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 pagesize 300
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 feedback on
col SQL_TEXT for a100
SELECT s.sql_id,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 s.sql_id,sql_text
HAVING sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0
order by 3;
set feedback off 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;
prompt PGA_TARGET_ADVICE
set linesize 300 pagesize 300
col PGA_TARGET_FOR_ESTIMATE for 999,999,999,990 heading 'PGA_TARGET'
col PGA_TARGET_FACTOR for 99.0 heading 'Factor'
col BYTES_PROCESSED for 999,999,999,999,990 heading 'Bytes Processed'
col ESTD_EXTRA_BYTES_RW for 999,999,999,999,990 heading 'Est. Bytes|r/w'
col ESTD_PGA_CACHE_HIT_PERCENTAGE for 9,990 heading 'Est. PGA|hit rate'
col ESTD_OVERALLOC_COUNT for 999,990 heading 'Est.|overalloc'
select PGA_TARGET_FOR_ESTIMATE
, PGA_TARGET_FACTOR
, BYTES_PROCESSED
, ESTD_EXTRA_BYTES_RW
, ESTD_PGA_CACHE_HIT_PERCENTAGE
, ESTD_OVERALLOC_COUNT
from v$pga_target_advice
order by PGA_TARGET_FACTOR
/
set linesize 300 pagesize 300
col "O/1/M" format a10
col name format a20
SELECT operation, options, object_name name, trunc(bytes/1024/1024) "input(MB)",trunc(last_memory_used/1024) last_mem,
trunc(estimated_optimal_size/1024) optimal_mem, trunc(estimated_onepass_size/1024) onepass_mem,
decode(optimal_executions, null, null, optimal_executions||'/'||onepass_executions||'/'||multipasses_executions) "O/1/M"
FROM V$SQL_PLAN p, V$SQL_WORKAREA w
WHERE p.address=w.address(+)
AND p.hash_value=w.hash_value(+)
AND p.id=w.operation_id(+)
and p.sql_id='&sql_id'
/
COL mem_component HEAD COMPONENT FOR A35
SELECT
component mem_component
, ROUND(current_size/1048576) cur_mb
, ROUND(min_size/1048576) min_mb
, ROUND(max_size/1048576) max_mb
, ROUND(user_specified_size/1048576) spec_mb
, oper_count
, last_oper_type last_optype
, last_oper_mode last_opmode
, last_oper_time last_optime
, granule_size/1048576 gran_mb
FROM
v$sga_dynamic_components
/
set lines 200 pages 100
col parameter format a50
col description format a80 word_wrapped
col "session value" format a10
col "instance value" format a10
select a.ksppinm "parameter",
a.ksppdesc "description",
b.ksppstvl "session value",
c.ksppstvl "instance value"
from x$ksppi a,x$ksppcv b,x$ksppsv c
where a.indx = b.indx
and a.indx = c.indx
and a.ksppinm like '/_pga%' escape '/'
order by a.ksppinm;
parameter description session va instance v
-------------------------------------------------- -------------------------------------------------------------------------------- ---------- ----------
_pga_aggregate_xmem_limit limit of aggregate PGA XMEM memory consumed by the instance 0 0
_pga_auto_snapshot_percentage percent growth of PGA memory for additional snapshots 20 20
_pga_auto_snapshot_threshold bytes of PGA memory in one process to trigger detail snapshot 524288000 524288000
_pga_in_sga_param1 pga in sga param1
_pga_in_sga_param2 pga in sga param2
_pga_in_sga_param3 pga in sga param4
_pga_in_sga_param4 pga in sga param4
_pga_in_sga_param5 pga in sga param4
_pga_large_extent_size PGA large extent size 1048576 1048576
_pga_limit_check_wait_time microseconds to wait for over limit confirmation 1000000 1000000
_pga_limit_dump_summary dump PGA summary when signalling ORA-4036 TRUE TRUE
_pga_limit_interrupt_smaller whether to interrupt smaller eligible processes FALSE FALSE
_pga_limit_min_req_size bytes of PGA usage below which process will not get ORA-4036 4194304 4194304
_pga_limit_per_process_minimum pga_aggregate_limit per-process minimum 3145728 3145728
_pga_limit_physmem_perc default percent of physical memory for pga_aggregate_limit and SGA 90 90
_pga_limit_simulated_physmem_size bytes of physical memory to determine pga_aggregate_limit with 0 0
_pga_limit_target_perc default percent of pga_aggregate_target for pga_aggregate_limit 200 200
_pga_limit_time_to_interrupt seconds to wait until direct interrupt 2 2
_pga_limit_time_until_idle seconds to wait before treating process as idle 15 15
_pga_limit_time_until_killed seconds to wait before killing session over limit 30 30
_pga_limit_tracing trace pga_aggregate_limit activity 0 0
_pga_limit_use_immediate_kill use immediate kill for sessions over limit TRUE TRUE
_pga_limit_watch_perc percentage of limit to have processes watch 50 50
_pga_limit_watch_size bytes of PGA usage at which process will begin watching limit 104857600 104857600
_pga_max_size Maximum size of the PGA memory for one process 209715200 209715200
