ORACLE PGA
The PGA (Program or Process Global Area) is a memory area (RAM) that stores data and control information for a single process. For example, it typically contains a sort area, hash area, session cursor cache, etc.
prompt
prompt PGA Usage Summary Report
prompt ------------------------
col alloc_sum format 999,999,990 heading 'Total Allocation'
col free_sum format 999,999,990 heading 'Total Freeable'
col used_sum format 999,999,990 heading 'Total In Use'
col pga_range heading 'PGA Range'
col sessions heading 'No.|Sessions'
compute sum of alloc_sum on report
compute sum of used_sum on report
compute sum of free_sum on report
break on report
select count (*) as sessions
, case
when pga_alloc_mem < (1*1024*1024) then ' < 1Mb'
when pga_alloc_mem < (2*1024*1024) then ' 1Mb-> 2Mb'
when pga_alloc_mem < (3*1024*1024) then ' 2Mb-> 3Mb'
when pga_alloc_mem < (4*1024*1024) then ' 3Mb-> 4Mb'
when pga_alloc_mem < (5*1024*1024) then ' 4Mb-> 5Mb'
when pga_alloc_mem < (10*1024*1024) then ' 5Mb->10Mb'
when pga_alloc_mem < (15*1024*1024) then '10Mb->15Mb'
when pga_alloc_mem < (20*1024*1024) then '15Mb->20Mb'
when pga_alloc_mem < (25*1024*1024) then '20Mb->25Mb'
else '25Mb+ '
end as pga_range
, sum (pga_alloc_mem) as alloc_sum
, sum (pga_used_mem) as used_sum
, sum (pga_freeable_mem) as free_sum
from v$process
group by case
when pga_alloc_mem < (1*1024*1024) then ' < 1Mb'
when pga_alloc_mem < (2*1024*1024) then ' 1Mb-> 2Mb'
when pga_alloc_mem < (3*1024*1024) then ' 2Mb-> 3Mb'
when pga_alloc_mem < (4*1024*1024) then ' 3Mb-> 4Mb'
when pga_alloc_mem < (5*1024*1024) then ' 4Mb-> 5Mb'
when pga_alloc_mem < (10*1024*1024) then ' 5Mb->10Mb'
when pga_alloc_mem < (15*1024*1024) then '10Mb->15Mb'
when pga_alloc_mem < (20*1024*1024) then '15Mb->20Mb'
when pga_alloc_mem < (25*1024*1024) then '20Mb->25Mb'
else '25Mb+ '
end
order by pga_range
/
prompt SQL work area (current)
col sid for 99990
col operation_type for a20
col wsize for 999,990 heading 'Current|W.Size(k)'
col esize for 999,990 heading 'Expected|W.Size(k)'
col amsize for 999,999,990 heading 'Current|Mem.(k)'
col mmsize for 999,999,990 heading 'Maximum|Mem.(k)'
col tsize for 999,999,990 heading 'Tmp. Seg.|Size (k)'
col passes for 999,990 heading 'Passes'
select SID
, OPERATION_TYPE
, WORK_AREA_SIZE/1024 as wsize
, EXPECTED_SIZE/1024 as esize
, ACTUAL_MEM_USED/1024 as amsize
, MAX_MEM_USED/1024 as mmsize
, TEMPSEG_SIZE/1024 as tsize
, NUMBER_PASSES as passes
from gv$sql_workarea_active
order by sid
/
--clear breaks
--clear computes
Script to monitor for the 'session pga memory'
col VALUE format 9999999999999
col name format a40
select name,value ,unit from V$PGASTAT ;
SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM/(1024*1024) "ALLOC(MB)",
PGA_FREEABLE_MEM FREEABLE, PGA_MAX_MEM
FROM V$PROCESS
order by pga_alloc_mem asc;
Show the maximum PGA usage per process:
select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process;
set pagesize 100
prompt PER SESSION PGA
col TXT format a30
col MACHINE format a12
col MODULE format a12
col "MEMORY/SESSION" format a10
col EVENT format a12
col program format a12
col unix_pid format a8
col schemaname format a10
col username format a12
col sid_serial format a10
SELECT
a.sid||','|| c.serial# sid_serial, lpad(e.spid,7) unix_pid,
round(sum(a.value)/1024/1024,2) || ' MB ' "MEMORY/SESSION",
c.username, c.schemaname, c.machine, c.status, c.server,
c.program, c.module, c.event,d.sql_id,
round(e.pga_used_mem/1024/1024,2) as pga_used_mem,
round(e.pga_alloc_mem/1024/1024,2) as pga_alloc_mem,
round(e.pga_freeable_mem/1024/1024,2) as pga_freeable_mem,
round(e.pga_max_mem/1024/1024,2) as pga_max_mem
FROM v$sesstat a, v$statname b, v$session c, v$sqlarea d, v$process e
WHERE name = 'session pga memory'
AND a.statistic# = b.statistic#
AND a.sid = c.sid
AND c.sql_address = d.address(+)
AND c.sql_hash_value = d.hash_value(+)
AND e.addr=c.paddr
GROUP BY a.sid, c.serial#, lpad(e.spid,7),
c.username, c.schemaname, c.machine, c.status, c.server, c.program,
c.module, c.event,d.sql_id,e.pga_used_mem/1024/1024, e.pga_alloc_mem/1024/1024,
e.pga_freeable_mem/1024/1024, e.pga_max_mem/1024/1024
ORDER BY round(sum(a.value)/1024/1024,2) DESC;
SGA Pools History Report from AWR (include free memory)
https://github.com/carlos-sierra/cscripts/blob/master/cs_sgastat_awr_report.sql
set linesize 300 pagesize 300
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
define cs_instance_number=1
define cs_dbid=''
COL snap_id FOR 9999999 HEA 'Snap ID'
COL begin_time FOR A19 HEA 'Begin Time'
COL end_time FOR A19 HEA 'End Time'
COL pga_aggregate_target FOR 9,990.000 HEA 'PGA|Aggregate|Target'
COL sga_target FOR 9,990.0 HEA 'SGA|Target'
COL buffer_cache FOR 9,990.000 HEA 'Buffer|Cache'
COL log_buffer FOR 9,990.000 HEA 'Log|Buffer'
COL shared_io_pool FOR 9,990.000 HEA 'Shared|IO Pool'
COL fixed_sga FOR 9,990.000 HEA 'Fixed|SGA'
COL shared_pool FOR 9,990.000 HEA 'Shared|Pool'
COL shared_pool_free_memory FOR 9,990.000 HEA 'Shared|Pool|Free|Memory'
COL large_pool FOR 9,990.000 HEA 'Large|Pool'
COL large_pool_free_memory FOR 9,990.000 HEA 'Large|Pool|Free|Memory'
COL java_pool FOR 9,990.000 HEA 'Java|Pool'
COL java_pool_free_memory FOR 9,990.000 HEA 'Java|Pool|Free|Memory'
COL streams_pool FOR 9,990.000 HEA 'Streams|Pool'
COL streams_pool_free_memory FOR 9,990.000 HEA 'Streams|Pool|Free|Memory'
--
PRO
PRO Memory Pools (GBs)
PRO ~~~~~~~~~~~~~~~~~~
WITH
sgastat AS (
SELECT /*+ MATERIALIZE NO_MERGE */
snap_id,
ROUND(SUM(CASE WHEN name = 'buffer_cache' AND pool IS NULL THEN bytes ELSE 0 END)/POWER(2,30), 3) buffer_cache, -- see bug 18166499
ROUND(SUM(CASE WHEN name = 'log_buffer' AND pool IS NULL THEN bytes ELSE 0 END)/POWER(2,30), 3) log_buffer,
ROUND(SUM(CASE WHEN name = 'shared_io_pool' AND pool IS NULL THEN bytes ELSE 0 END)/POWER(2,30), 3) shared_io_pool,
ROUND(SUM(CASE WHEN name = 'fixed_sga' AND pool IS NULL THEN bytes ELSE 0 END)/POWER(2,30), 3) fixed_sga,
ROUND(SUM(CASE WHEN pool = 'shared pool' AND name IS NULL THEN bytes ELSE 0 END)/POWER(2,30), 3) shared_pool, -- see bug 18166499
ROUND(SUM(CASE WHEN pool = 'shared pool' AND name = 'free memory' THEN bytes ELSE 0 END)/POWER(2,30), 3) shared_pool_free_memory,
ROUND(SUM(CASE WHEN pool = 'large pool' AND name IS NULL THEN bytes ELSE 0 END)/POWER(2,30), 3) large_pool, -- see bug 18166499
ROUND(SUM(CASE WHEN pool = 'large pool' AND name = 'free memory' THEN bytes ELSE 0 END)/POWER(2,30), 3) large_pool_free_memory,
ROUND(SUM(CASE WHEN pool = 'java pool' AND name IS NULL THEN bytes ELSE 0 END)/POWER(2,30), 3) java_pool, -- see bug 18166499
ROUND(SUM(CASE WHEN pool = 'java pool' AND name = 'free memory' THEN bytes ELSE 0 END)/POWER(2,30), 3) java_pool_free_memory,
ROUND(SUM(CASE WHEN pool = 'streams pool' AND name IS NULL THEN bytes ELSE 0 END)/POWER(2,30), 3) streams_pool, -- see bug 18166499
ROUND(SUM(CASE WHEN pool = 'streams pool' AND name = 'free memory' THEN bytes ELSE 0 END)/POWER(2,30), 3) streams_pool_free_memory
FROM dba_hist_sgastat
WHERE 1=1
and dbid = TO_NUMBER(:DID)
AND instance_number = TO_NUMBER(:INST_NUMBER )
AND snap_id BETWEEN TO_NUMBER(:BgnSnap) AND TO_NUMBER(:EndSnap)
GROUP BY
snap_id
),
param AS (
SELECT /*+ MATERIALIZE NO_MERGE */
snap_id,
ROUND(SUM(CASE parameter_name WHEN '__db_cache_size' THEN TO_NUMBER(value) ELSE 0 END)/POWER(2,30), 3) buffer_cache,
ROUND(SUM(CASE parameter_name WHEN '__shared_io_pool_size' THEN TO_NUMBER(value) ELSE 0 END)/POWER(2,30), 3) shared_io_pool,
ROUND(SUM(CASE parameter_name WHEN '__shared_pool_size' THEN TO_NUMBER(value) ELSE 0 END)/POWER(2,30), 3) shared_pool,
ROUND(SUM(CASE parameter_name WHEN '__large_pool_size' THEN TO_NUMBER(value) ELSE 0 END)/POWER(2,30), 3) large_pool,
ROUND(SUM(CASE parameter_name WHEN '__java_pool_size' THEN TO_NUMBER(value) ELSE 0 END)/POWER(2,30), 3) java_pool,
ROUND(SUM(CASE parameter_name WHEN '__streams_pool_size' THEN TO_NUMBER(value) ELSE 0 END)/POWER(2,30), 3) streams_pool,
ROUND(SUM(CASE parameter_name WHEN '__sga_target' THEN TO_NUMBER(value) ELSE 0 END)/POWER(2,30), 3) sga_target,
ROUND(SUM(CASE parameter_name WHEN '__pga_aggregate_target' THEN TO_NUMBER(value) ELSE 0 END)/POWER(2,30), 3) pga_aggregate_target
FROM dba_hist_parameter
WHERE 1=1
and dbid = TO_NUMBER(:DID)
AND instance_number = TO_NUMBER(:INST_NUMBER )
AND snap_id BETWEEN TO_NUMBER(:BgnSnap) AND TO_NUMBER(:EndSnap)
-- AND parameter_name IN ('__db_cache_size', '__java_pool_size', '__large_pool_size', '__pga_aggregate_target', '__sga_target', '__shared_io_pool_size', '__shared_pool_size', '__streams_pool_size')
GROUP BY
snap_id
),
my_query AS (
SELECT /*+ MATERIALIZE NO_MERGE */
s.snap_id,
CAST(s.begin_interval_time AS DATE) begin_time,
CAST(s.end_interval_time AS DATE) end_time,
p.pga_aggregate_target,
p.sga_target,
p.buffer_cache,
p.shared_pool,
p.large_pool,
p.java_pool,
p.streams_pool,
p.shared_io_pool,
t.shared_pool_free_memory,
t.large_pool_free_memory,
t.java_pool_free_memory,
t.streams_pool_free_memory
FROM dba_hist_snapshot s,
sgastat t,
param p
WHERE 1=1
and s.dbid = TO_NUMBER(:DID)
AND s.instance_number = TO_NUMBER(:INST_NUMBER )
AND s.snap_id BETWEEN TO_NUMBER(:BgnSnap) AND TO_NUMBER(:EndSnap)
AND t.snap_id = s.snap_id
AND p.snap_id = s.snap_id
)
SELECT end_time,
pga_aggregate_target,
sga_target,
buffer_cache,
shared_pool,
large_pool,
java_pool,
streams_pool,
shared_io_pool,
shared_pool_free_memory,
large_pool_free_memory,
java_pool_free_memory,
streams_pool_free_memory
FROM my_query
ORDER BY
snap_id
/
Shared Large Java Streams
PGA Pool Pool Pool Pool
Aggregate SGA Buffer Shared Large Java Streams Shared Free Free Free Free
End Time Target Target Cache Pool Pool Pool Pool IO Pool Memory Memory Memory Memory
------------------- ---------- -------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
23-SEP-22 0.000 0.0 0.000 0.000 0.000 0.000 0.000 0.000 0.535 0.035 0.016 0.000
23-SEP-22 0.000 0.0 0.000 0.000 0.000 0.000 0.000 0.000 0.535 0.035 0.016 0.000
23-SEP-22 0.000 0.0 0.000 0.000 0.000 0.000 0.000 0.000 0.521 0.035 0.016 0.000
WITH
pgastat_denorm_1 AS (
SELECT /*+ MATERIALIZE NO_MERGE */
snap_id,
dbid,
instance_number,
SUM(CASE name WHEN 'PGA memory freed back to OS' THEN value ELSE 0 END) pga_mem_freed_to_os,
SUM(CASE name WHEN 'aggregate PGA auto target' THEN value ELSE 0 END) aggr_pga_auto_target,
SUM(CASE name WHEN 'aggregate PGA target parameter' THEN value ELSE 0 END) aggr_pga_target_param,
SUM(CASE name WHEN 'bytes processed' THEN value ELSE 0 END) bytes_processed,
SUM(CASE name WHEN 'extra bytes read/written' THEN value ELSE 0 END) extra_bytes_rw,
SUM(CASE name WHEN 'global memory bound' THEN value ELSE 0 END) global_memory_bound,
SUM(CASE name WHEN 'maximum PGA allocated' THEN value ELSE 0 END) max_pga_allocated,
SUM(CASE name WHEN 'maximum PGA used for auto workareas' THEN value ELSE 0 END) max_pga_used_aut_wa,
SUM(CASE name WHEN 'maximum PGA used for manual workareas' THEN value ELSE 0 END) max_pga_used_man_wa,
SUM(CASE name WHEN 'total PGA allocated' THEN value ELSE 0 END) tot_pga_allocated,
SUM(CASE name WHEN 'total PGA inuse' THEN value ELSE 0 END) tot_pga_inuse,
SUM(CASE name WHEN 'total PGA used for auto workareas' THEN value ELSE 0 END) tot_pga_used_aut_wa,
SUM(CASE name WHEN 'total PGA used for manual workareas' THEN value ELSE 0 END) tot_pga_used_man_wa,
SUM(CASE name WHEN 'total freeable PGA memory' THEN value ELSE 0 END) tot_freeable_pga_mem
FROM dba_hist_pgastat
WHERE name IN
('PGA memory freed back to OS'
,'aggregate PGA auto target'
,'aggregate PGA target parameter'
,'bytes processed'
,'extra bytes read/written'
,'global memory bound'
,'maximum PGA allocated'
,'maximum PGA used for auto workareas'
,'maximum PGA used for manual workareas'
,'total PGA allocated'
,'total PGA inuse'
,'total PGA used for auto workareas'
,'total PGA used for manual workareas'
,'total freeable PGA memory'
)
AND snap_id in (select snap_id from dba_hist_snapshot where begin_interval_time > sysdate -60)
GROUP BY
snap_id,
dbid,
instance_number
),
pgastat_denorm_2 AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h.dbid,
h.instance_number,
s.startup_time,
MIN(h.pga_mem_freed_to_os) pga_mem_freed_to_os,
MIN(h.bytes_processed) bytes_processed,
MIN(h.extra_bytes_rw) extra_bytes_rw
FROM pgastat_denorm_1 h,
dba_hist_snapshot s
WHERE s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
GROUP BY
h.dbid,
h.instance_number,
s.startup_time
),
pgastat_delta AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h1.snap_id,
h1.dbid,
h1.instance_number,
s1.begin_interval_time,
s1.end_interval_time,
ROUND((CAST(s1.end_interval_time AS DATE) - CAST(s1.begin_interval_time AS DATE)) * 24 * 60 * 60) interval_secs,
(h1.pga_mem_freed_to_os - h0.pga_mem_freed_to_os) pga_mem_freed_to_os,
h1.aggr_pga_auto_target,
h1.aggr_pga_target_param,
(h1.bytes_processed - h0.bytes_processed) bytes_processed,
(h1.extra_bytes_rw - h0.extra_bytes_rw) extra_bytes_rw,
h1.global_memory_bound,
h1.max_pga_allocated,
h1.max_pga_used_aut_wa,
h1.max_pga_used_man_wa,
h1.tot_pga_allocated,
h1.tot_pga_inuse,
h1.tot_pga_used_aut_wa,
h1.tot_pga_used_man_wa,
h1.tot_freeable_pga_mem
FROM pgastat_denorm_1 h0,
pgastat_denorm_1 h1,
dba_hist_snapshot s0,
dba_hist_snapshot s1,
pgastat_denorm_2 min /* to see cumulative use (replace h0 with min on select list above) */
WHERE h1.snap_id = h0.snap_id + 1
AND h1.dbid = h0.dbid
AND h1.instance_number = h0.instance_number
AND s0.snap_id = h0.snap_id
AND s0.dbid = h0.dbid
AND s0.instance_number = h0.instance_number
AND s1.snap_id = h1.snap_id
AND s1.dbid = h1.dbid
AND s1.instance_number = h1.instance_number
AND s1.snap_id = s0.snap_id + 1
AND s1.startup_time = s0.startup_time
AND s1.begin_interval_time > (s0.begin_interval_time + (1 / (24 * 60))) /* filter out snaps apart < 1 min */
AND min.dbid = s1.dbid
AND min.instance_number = s1.instance_number
AND min.startup_time = s1.startup_time
)
SELECT snap_id,
TO_CHAR(MIN(begin_interval_time), 'YYYY-MM-DD HH24:MI') begin_time,
TO_CHAR(MIN(end_interval_time), 'YYYY-MM-DD HH24:MI') end_time,
ROUND(SUM(pga_mem_freed_to_os) / POWER(2, 30), 3) pga_mem_freed_to_os,
ROUND(SUM(aggr_pga_auto_target) / POWER(2, 30), 3) aggr_pga_auto_target,
ROUND(SUM(aggr_pga_target_param) / POWER(2, 30), 3) aggr_pga_target_param,
ROUND(SUM(bytes_processed) / POWER(2, 30), 3) bytes_processed,
ROUND(SUM(extra_bytes_rw) / POWER(2, 30), 3) extra_bytes_rw,
ROUND(SUM(global_memory_bound) / POWER(2, 30), 3) global_memory_bound,
ROUND(SUM(max_pga_allocated) / POWER(2, 30), 3) max_pga_allocated,
ROUND(SUM(max_pga_used_aut_wa) / POWER(2, 30), 3) max_pga_used_aut_wa,
ROUND(SUM(max_pga_used_man_wa) / POWER(2, 30), 3) max_pga_used_man_wa,
ROUND(SUM(tot_pga_allocated) / POWER(2, 30), 3) tot_pga_allocated,
ROUND(SUM(tot_pga_inuse) / POWER(2, 30), 3) tot_pga_inuse,
ROUND(SUM(tot_pga_used_aut_wa) / POWER(2, 30), 3) tot_pga_used_aut_wa,
ROUND(SUM(tot_pga_used_man_wa) / POWER(2, 30), 3) tot_pga_used_man_wa,
ROUND(SUM(tot_freeable_pga_mem) / POWER(2, 30), 3) tot_freeable_pga_mem,
0 dummy_15
FROM pgastat_delta
GROUP BY
snap_id
ORDER BY
snap_id;
=====
https://weidongzhou.wordpress.com/2016/02/25/pga_aggregate_target-vs-pga_aggregate_limit/comment-page-1/
set linesize 700 pagesize 300
WITH
pgastat_denorm_1 AS (
SELECT /*+ MATERIALIZE NO_MERGE */
snap_id,
dbid,
instance_number,
SUM(CASE name WHEN 'PGA memory freed back to OS' THEN value ELSE 0 END) pga_mem_freed_to_os,
SUM(CASE name WHEN 'aggregate PGA auto target' THEN value ELSE 0 END) aggr_pga_auto_target,
SUM(CASE name WHEN 'aggregate PGA target parameter' THEN value ELSE 0 END) aggr_pga_target_param,
SUM(CASE name WHEN 'bytes processed' THEN value ELSE 0 END) bytes_processed,
SUM(CASE name WHEN 'extra bytes read/written' THEN value ELSE 0 END) extra_bytes_rw,
SUM(CASE name WHEN 'global memory bound' THEN value ELSE 0 END) global_memory_bound,
SUM(CASE name WHEN 'maximum PGA allocated' THEN value ELSE 0 END) max_pga_allocated,
SUM(CASE name WHEN 'maximum PGA used for auto workareas' THEN value ELSE 0 END) max_pga_used_aut_wa,
SUM(CASE name WHEN 'maximum PGA used for manual workareas' THEN value ELSE 0 END) max_pga_used_man_wa,
SUM(CASE name WHEN 'total PGA allocated' THEN value ELSE 0 END) tot_pga_allocated,
SUM(CASE name WHEN 'total PGA inuse' THEN value ELSE 0 END) tot_pga_inuse,
SUM(CASE name WHEN 'total PGA used for auto workareas' THEN value ELSE 0 END) tot_pga_used_aut_wa,
SUM(CASE name WHEN 'total PGA used for manual workareas' THEN value ELSE 0 END) tot_pga_used_man_wa,
SUM(CASE name WHEN 'total freeable PGA memory' THEN value ELSE 0 END) tot_freeable_pga_mem
FROM dba_hist_pgastat
WHERE name IN
('PGA memory freed back to OS'
,'aggregate PGA auto target'
,'aggregate PGA target parameter'
,'bytes processed'
,'extra bytes read/written'
,'global memory bound'
,'maximum PGA allocated'
,'maximum PGA used for auto workareas'
,'maximum PGA used for manual workareas'
,'total PGA allocated'
,'total PGA inuse'
,'total PGA used for auto workareas'
,'total PGA used for manual workareas'
,'total freeable PGA memory'
)
AND snap_id in (select snap_id from dba_hist_snapshot where begin_interval_time > sysdate -60)
GROUP BY
snap_id,
dbid,
instance_number
),
pgastat_denorm_2 AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h.dbid,
h.instance_number,
s.startup_time,
MIN(h.pga_mem_freed_to_os) pga_mem_freed_to_os,
MIN(h.bytes_processed) bytes_processed,
MIN(h.extra_bytes_rw) extra_bytes_rw
FROM pgastat_denorm_1 h,
dba_hist_snapshot s
WHERE s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
GROUP BY
h.dbid,
h.instance_number,
s.startup_time
),
pgastat_delta AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h1.snap_id,
h1.dbid,
h1.instance_number,
s1.begin_interval_time,
s1.end_interval_time,
ROUND((CAST(s1.end_interval_time AS DATE) - CAST(s1.begin_interval_time AS DATE)) * 24 * 60 * 60) interval_secs,
(h1.pga_mem_freed_to_os - h0.pga_mem_freed_to_os) pga_mem_freed_to_os,
h1.aggr_pga_auto_target,
h1.aggr_pga_target_param,
(h1.bytes_processed - h0.bytes_processed) bytes_processed,
(h1.extra_bytes_rw - h0.extra_bytes_rw) extra_bytes_rw,
h1.global_memory_bound,
h1.max_pga_allocated,
h1.max_pga_used_aut_wa,
h1.max_pga_used_man_wa,
h1.tot_pga_allocated,
h1.tot_pga_inuse,
h1.tot_pga_used_aut_wa,
h1.tot_pga_used_man_wa,
h1.tot_freeable_pga_mem
FROM pgastat_denorm_1 h0,
pgastat_denorm_1 h1,
dba_hist_snapshot s0,
dba_hist_snapshot s1,
pgastat_denorm_2 min /* to see cumulative use (replace h0 with min on select list above) */
WHERE h1.snap_id = h0.snap_id + 1
AND h1.dbid = h0.dbid
AND h1.instance_number = h0.instance_number
AND s0.snap_id = h0.snap_id
AND s0.dbid = h0.dbid
AND s0.instance_number = h0.instance_number
AND s1.snap_id = h1.snap_id
AND s1.dbid = h1.dbid
AND s1.instance_number = h1.instance_number
AND s1.snap_id = s0.snap_id + 1
AND s1.startup_time = s0.startup_time
AND s1.begin_interval_time > (s0.begin_interval_time + (1 / (24 * 60))) /* filter out snaps apart < 1 min */
AND min.dbid = s1.dbid
AND min.instance_number = s1.instance_number
AND min.startup_time = s1.startup_time
)
SELECT snap_id,
TO_CHAR(MIN(begin_interval_time), 'YYYY-MM-DD HH24:MI') begin_time,
TO_CHAR(MIN(end_interval_time), 'YYYY-MM-DD HH24:MI') end_time,
ROUND(SUM(pga_mem_freed_to_os) / POWER(2, 30), 3) pga_mem_freed_to_os,
ROUND(SUM(aggr_pga_auto_target) / POWER(2, 30), 3) aggr_pga_auto_target,
ROUND(SUM(aggr_pga_target_param) / POWER(2, 30), 3) aggr_pga_target_param,
ROUND(SUM(bytes_processed) / POWER(2, 30), 3) bytes_processed,
ROUND(SUM(extra_bytes_rw) / POWER(2, 30), 3) extra_bytes_rw,
ROUND(SUM(global_memory_bound) / POWER(2, 30), 3) global_memory_bound,
ROUND(SUM(max_pga_allocated) / POWER(2, 30), 3) max_pga_allocated,
ROUND(SUM(max_pga_used_aut_wa) / POWER(2, 30), 3) max_pga_used_aut_wa,
ROUND(SUM(max_pga_used_man_wa) / POWER(2, 30), 3) max_pga_used_man_wa,
ROUND(SUM(tot_pga_allocated) / POWER(2, 30), 3) tot_pga_allocated,
ROUND(SUM(tot_pga_inuse) / POWER(2, 30), 3) tot_pga_inuse,
ROUND(SUM(tot_pga_used_aut_wa) / POWER(2, 30), 3) tot_pga_used_aut_wa,
ROUND(SUM(tot_pga_used_man_wa) / POWER(2, 30), 3) tot_pga_used_man_wa,
ROUND(SUM(tot_freeable_pga_mem) / POWER(2, 30), 3) tot_freeable_pga_mem,
0 dummy_15
FROM pgastat_delta
GROUP BY
snap_id
ORDER BY
snap_id;
Snap ID Begin Time End Time PGA_MEM_FREED_TO_OS AGGR_PGA_AUTO_TARGET AGGR_PGA_TARGET_PARAM BYTES_PROCESSED EXTRA_BYTES_RW GLOBAL_MEMORY_BOUND MAX_PGA_ALLOCATED MAX_PGA_USED_AUT_WA MAX_PGA_USED_MAN_WA TOT_PGA_ALLOCATED TOT_PGA_INUSE TOT_PGA_USED_AUT_WA TOT_PGA_USED_MAN_WA TOT_FREEABLE_PGA_MEM DUMMY_15
-------- ------------------- ------------------- ------------------- -------------------- --------------------- --------------- -------------- ------------------- ----------------- ------------------- ------------------- ----------------- ------------- ------------------- ------------------- -------------------- ----------
8399 2022-09-14 21:00 2022-09-14 22:00 .009 .644 2.666 1.233 0 .267 2.528 .182 .028 2.257 1.954 .006 0 .035 0
8400 2022-09-14 22:00 2022-09-14 23:00 .005 .717 2.666 3.32 0 .267 2.537 .182 .028 2.13 1.869 0 0 .035 0
8403 2022-09-16 06:16 2022-09-16 07:00 .008 .706 2.666 .578 0 .267 2.537 .182 .028 2.126 1.886 0 0 .035 0
8404 2022-09-16 07:00 2022-09-16 08:00 .007 .716 2.666 .847 0 .267 2.537 .182 .028 2.118 1.879 0 0 .034 0
8405 2022-09-16 08:00 2022-09-16 09:00 .009 .718 2.666 .865 0 .267 2.537 .182 .028 2.117 1.878 0 0 .034 0
8406 2022-09-16 09:00 2022-09-16 10:00 .01 .709 2.666 .858 0 .267 2.537 .182 .028 2.128 1.886 0 0 .036 0
8407 2022-09-16 10:00 2022-09-16 11:00 .007 .696 2.666 .966 0 .267 2.537 .182 .028 2.143 1.901 0 0 .035 0
8408 2022-09-16 11:00 2022-09-16 12:00 .009 .698 2.666 .864 0 .267 2.537 .182 .028
col NAME for a40
SELECT NAME, VALUE, UNIT FROM V$PGASTAT
where
NAME in ( 'aggregate PGA target parameter', 'aggregate PGA auto target', 'total PGA inuse ',
'total PGA allocated', 'maximum PGA used for auto workareas', 'cache hit percentage', 'over allocation count');
NAME VALUE UNIT
---------------------------------------- ---------- ------------
aggregate PGA target parameter 1431306240 bytes
aggregate PGA auto target 348337152 bytes
total PGA allocated 1173649408 bytes
maximum PGA used for auto workareas 98097152 bytes
over allocation count 0
cache hit percentage 99.99 percent
6 rows selected.
SELECT * FROM (
SELECT
count(*) AS count,
user_id, program, module, machine, sql_id
FROM SYS.DBA_HIST_ACTIVE_SESS_HISTORY
WHERE sample_time > sysdate-1
AND sample_time < sysdate
-- AND pga_allocated > 1024*1024*1024
GROUP BY user_id, program, module, machine, sql_id
ORDER BY count(*) DESC
)
WHERE rownum <= 20
define sql_id='8cnh50qfgwg73'
SELECT sql_id,
TO_CHAR(sample_time,'DD-MON-YYYY HH24:MI:SS') AS sample_time,
pga_allocated
FROM SYS.DBA_HIST_ACTIVE_SESS_HISTORY
WHERE sample_time > sysdate-1
AND sample_time < sysdate
AND sql_id = '&SQL_ID'
ORDER BY sample_time;
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 9,999,999,999.9
col avg_pio for 9,999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_lio,
(disk_reads_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_pio,
rows_processed_delta total_rows
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','8cnh50qfgwg73')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and s.instance_number like nvl('&instance_number',s.instance_number)
order by 1, 2, 3
/
col sid for 99999
col operation for a20
col start_time for a20
col expected_size_MB for 99,999.9
col Act_Used_MB for 99,999.9
col Max_Used_MB for 99,999.9
col TEMP_Seg_MB for 999,999
select to_char(sql_exec_start, 'yyyy-mm-dd hh24:mi:ss') start_time, sql_id,
to_number(decode(sid, 65535, NULL, sid)) sid,
operation_type operation, trunc(expected_size/1024/1024) expected_size_MB,
trunc(actual_mem_used/1024/1024) Act_Used_MB, trunc(max_mem_used/1024/1204) Max_Used_MB,
number_passes pass, trunc(tempseg_size/1024/1024) TEMP_Seg_MB
FROM gv$sql_workarea_active
where 1=1
and SQL_ID='&SQL_ID'
ORDER BY 1,2;
define top=50
col STAR for a50
select SQL_ID,round(PGA_MB,1) PGA_MB,percent,rpad('*',percent*10/100,'*') star
from
(
select SQL_ID,sum(DELTA_PGA_MB) PGA_MB ,(ratio_to_report(sum(DELTA_PGA_MB)) over ())*100 percent,rank() over(order by sum(DELTA_PGA_MB) desc) rank
from
(
select SESSION_ID,SESSION_SERIAL#,sample_id,SQL_ID,SAMPLE_TIME,IS_SQLID_CURRENT,SQL_CHILD_NUMBER,PGA_ALLOCATED,
greatest(PGA_ALLOCATED - first_value(PGA_ALLOCATED) over (partition by SESSION_ID,SESSION_SERIAL# order by sample_time rows 1 preceding),0)/power(1024,2) "DELTA_PGA_MB"
from
gv$active_session_history
where
IS_SQLID_CURRENT='Y'
and sample_time > sysdate - interval '30' minute
order by 1,2,3,4
)
group by sql_id
having sum(DELTA_PGA_MB) > 0
)
where rank < (&top+1)
order by rank
