Search This Blog

Total Pageviews

Friday 12 March 2010

Oracle script to monitor for the PGA per session

 
 
 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

Oracle DBA

anuj blog Archive