Oracle Pga Usage History

 
Customer RecommendedHow to Find Top sql_id's That Consume PGA and Temporary Segments Most from ASH (Doc ID 2516606.1)

 
Top sql_id's that consumed PGA: 

select *
from (select instance_number, sql_id, max(pga_sum_mb) pga_max
        from (select instance_number, sample_time, sql_id, round(sum(nvl(pga_allocated, 0))/1024/1024) pga_sum_mb
                from dba_hist_active_sess_history
               where sample_time between to_timestamp('&begin_timestamp', 'yyyy/mm/dd hh24:mi') and to_timestamp('&end_timestamp', 'yyyy/mm/dd hh24:mi')
            group by instance_number, sample_time, sql_id)
       group by instance_number, sql_id
       order by pga_max desc)
where rownum <= 10;




Top sql_id's that consumed temporary segments: 

select *
from (select instance_number, sql_id, max(temp_sum_mb) temp_max
        from (select instance_number, sample_time, sql_id, round(sum(nvl(temp_space_allocated, 0))/1024/1024) temp_sum_mb
                from dba_hist_active_sess_history
               where sample_time between to_timestamp('&begin_timestamp', 'yyyy/mm/dd hh24:mi') and to_timestamp('&end_timestamp', 'yyyy/mm/dd hh24:mi')
            group by instance_number, sample_time, sql_id)
       group by instance_number, sql_id
       order by temp_max desc)
where rownum <= 10;  




New 1 

WITH 
pga_data as
(
  SELECT /*+ MATERIALIZED */
      sample_time,    
      nvl(sum(ash.pga_allocated/1024/1024),0) AS sum_pga_mb
    FROM
      dba_hist_active_sess_history ash,
      dba_users u
   WHERE ash.user_id = u.user_id
     AND u.username  LIKE :username
     AND sample_time > SYSDATE-:n_days
     AND sample_time < SYSDATE
  GROUP BY action, sample_time
),
cal_data AS
(
  SELECT
    trunc(SYSDATE, 'MI') - (LEVEL/(24*60)*:per_mins)     AS date_min,
    trunc(SYSDATE, 'MI') - ((LEVEL-1)/(24*60)*:per_mins) AS date_max
  FROM dual 
  CONNECT BY LEVEL < (24*60*:n_days/:per_mins)+1
  ORDER BY date_min
)
SELECT /*+ NO_MERGE(h) NO_MERGE(c) */
    to_char(c.date_min, 'YYYY-MM-DD HH24:MI:SS') date_min,
    trunc(nvl(avg(sum_pga_mb),0), 2) avg_pga_mb,
    trunc(nvl(min(sum_pga_mb),0), 2) min_pga_mb,
    trunc(nvl(max(sum_pga_mb),0), 2) max_pga_mb
  FROM
    pga_data h,
    cal_data c
 WHERE h.sample_time (+) >= c.date_min
   AND h.sample_time (+) <  c.date_max
GROUP BY c.date_min;












username – usernames that uses PGA
n_days – defines how many n-days is reported since today
per_mins – defines granularity of each snapshot in minutes. Smaller granularity more precise report – more records.



WITH 
pga_data as
(
  SELECT /*+ MATERIALIZED */
      sample_time,    
      nvl(sum(ash.pga_allocated/1024/1024),0) AS sum_pga_mb
    FROM
      dba_hist_active_sess_history ash,
      dba_users u
   WHERE ash.user_id = u.user_id
     AND u.username  LIKE :username
     AND sample_time > SYSDATE-:n_days
     AND sample_time < SYSDATE
  GROUP BY action, sample_time
),
cal_data AS
(
  SELECT
    trunc(SYSDATE, 'MI') - (LEVEL/(24*60)*:per_mins)     AS date_min,
    trunc(SYSDATE, 'MI') - ((LEVEL-1)/(24*60)*:per_mins) AS date_max
  FROM dual 
  CONNECT BY LEVEL < (24*60*:n_days/:per_mins)+1
  ORDER BY date_min
)
SELECT /*+ NO_MERGE(h) NO_MERGE(c) */
    to_char(c.date_min, 'YYYY-MM-DD HH24:MI:SS') date_min,
    trunc(nvl(avg(sum_pga_mb),0), 2) avg_pga_mb,
    trunc(nvl(min(sum_pga_mb),0), 2) min_pga_mb,
    trunc(nvl(max(sum_pga_mb),0), 2) max_pga_mb
  FROM
    pga_data h,
    cal_data c
 WHERE h.sample_time (+) >= c.date_min
   AND h.sample_time (+) <  c.date_max
GROUP BY c.date_min;





EG

WITH 
pga_data as
(
  SELECT /*+ MATERIALIZED */
      sample_time,    
      nvl(sum(ash.pga_allocated/1024/1024),0) AS sum_pga_mb
    FROM
      dba_hist_active_sess_history ash,
      dba_users u
   WHERE ash.user_id = u.user_id
     AND sample_time > SYSDATE-10
     AND sample_time < SYSDATE
  GROUP BY action, sample_time
),
cal_data AS
(
  SELECT
    trunc(SYSDATE, 'MI') - (LEVEL/(24*60)*30)     AS date_min,
    trunc(SYSDATE, 'MI') - ((LEVEL-1)/(24*60)*30) AS date_max
  FROM dual 
  CONNECT BY LEVEL < (24*60*10/30)+1
  ORDER BY date_min
)
SELECT /*+ NO_MERGE(h) NO_MERGE(c) */
    to_char(c.date_min, 'YYYY-MM-DD HH24:MI:SS') date_min,
    trunc(nvl(avg(sum_pga_mb),0), 2) avg_pga_mb,
    trunc(nvl(min(sum_pga_mb),0), 2) min_pga_mb,
    trunc(nvl(max(sum_pga_mb),0), 2) max_pga_mb
  FROM
    pga_data h,
    cal_data c
 WHERE h.sample_time (+) >= c.date_min
   AND h.sample_time (+) <  c.date_max
GROUP BY c.date_min;
 


Option 2 : 

SELECT sn.INSTANCE_NUMBER,
sga.allo sga,
pga.allo pga,
(sga.allo + pga.allo) tot,
TRUNC (SN.END_INTERVAL_TIME, 'mi') time
FROM ( SELECT snap_id, INSTANCE_NUMBER, ROUND (SUM (bytes) / 1024 / 1024 / 1024, 3) allo
FROM DBA_HIST_SGASTAT
GROUP BY snap_id, INSTANCE_NUMBER) sga,
( SELECT snap_id, INSTANCE_NUMBER, ROUND (SUM (VALUE) / 1024 / 1024 / 1024, 3) allo
FROM DBA_HIST_PGASTAT
WHERE name = 'total PGA allocated'
GROUP BY snap_id, INSTANCE_NUMBER) pga,
dba_hist_snapshot sn
WHERE sn.snap_id = sga.snap_id
AND sn.INSTANCE_NUMBER = sga.INSTANCE_NUMBER
AND sn.snap_id = pga.snap_id
AND sn.INSTANCE_NUMBER = pga.INSTANCE_NUMBER
-- SN.END_INTERVAL_TIME between to_date(:begin_time,'DD_MON_YYYY_hh24_mi')-65/1440 and to_date(:end_time,'DD_MON_YYYY_hh24_mi')+5/1440    
ORDER BY sn.snap_id DESC, sn.INSTANCE_NUMBER;

or

SELECT
    snap_id,
    begin_interval_time,
    end_interval_time,
    max_pga_used_mem / 1024 / 1024 AS max_pga_used_mb,
    pga_cache_hit_percentage,
    pga_cache_miss_percentage
FROM
    dba_hist_pgastat
JOIN
    dba_hist_snapshot USING (snap_id)
WHERE
    begin_interval_time BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-01-31', 'YYYY-MM-DD')
ORDER BY
    snap_id;


Or 


SELECT 
    s.sql_id,
    s.plan_hash_value,
    s.module,
    s.action,
    s.parsing_schema_name,
    ROUND(SUM(pg.max_pga_used_mem) / 1024 / 1024, 2) AS total_pga_used_mb
FROM 
    dba_hist_sqlstat s
JOIN 
    dba_hist_pgastat pg ON s.sql_id = pg.sql_id
WHERE 
    s.parsing_schema_name NOT IN ('SYS')
GROUP BY 
    s.sql_id,
    s.plan_hash_value,
    s.module,
    s.action,
    s.parsing_schema_name
ORDER BY 
    total_pga_used_mb DESC;

or 

SELECT 
    ash.session_id AS sid,
    SUM(pga.max_pga_used_mem) / 1024 / 1024 AS total_pga_used_mb
FROM 
    dba_hist_active_sess_history ash
JOIN 
    dba_hist_pgastat pga ON ash.sample_time = pga.snap_time
WHERE 
    ash.session_type = 'FOREGROUND'
GROUP BY 
    ash.session_id
ORDER BY 
    total_pga_used_mb DESC;




Option 3:

set line 200
col obejct_name format a40
col sql_id format a20
SELECT
TO_CHAR(ASH.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') SAMPLE_TIME,
ASH.SESSION_ID,
ASH.BLOCKING_SESSION,
O.OBJECT_NAME,
S.SQL_id , nvl ( ash.pga_allocated/1024/1024/1024 , 0) AS SUM_PGA_MB 
FROM
DBA_HIST_ACTIVE_SESS_HISTORY ASH,
DBA_HIST_SQLTEXT S,
DBA_OBJECTS O
WHERE
ASH.SQL_ID = S.SQL_ID (+) AND
ASH.CURRENT_OBJ# = O.OBJECT_ID (+) AND
ASH.SAMPLE_TIME BETWEEN
TO_TIMESTAMP('09.05.2007 15:30:00', 'dd.mm.yyyy hh24:mi:ss') AND
TO_TIMESTAMP('09.05.2007 16:30:00', 'dd.mm.yyyy hh24:mi:ss')  
group by  s.sql_id , ash.session_id , ash.sample_time , ash.blocking_session ,  o.object_name , ash.pga_allocated 
ORDER BY sum_pga_mb  DESC , ash.session_id 
fetch first 100 rows only ;




Option 4:


BREAK ON snap_begin SKIP 1 ON snap_end ON event_name

COL event_name FOR A40

SELECT
    CAST(begin_interval_time AS DATE) snap_begin
  , TO_CHAR(CAST(end_interval_time AS DATE), 'HH24:MI') snap_end
  , category
  , num_processes
  , ROUND(allocated_max/1048576)     max_mb
  , ROUND(max_allocated_max/1048576) max_max_mb
FROM
    dba_hist_snapshot
  NATURAL JOIN
    dba_hist_process_mem_summary
WHERE
    begin_interval_time > SYSDATE - 3
--AND category = 'SQL'
ORDER BY
    snap_begin
  , category
/


Option 5: 

SET LINESIZE 150
SET PAGESIZE 100

COLUMN snap_time FORMAT A20
COLUMN total_pga_allocated_mb FORMAT 999,999.99
COLUMN total_pga_used_mb FORMAT 999,999.99

SELECT TO_CHAR(s.end_interval_time, 'YYYY-MM-DD HH24:MI:SS') AS snap_time,
       ROUND((p.value + px.value) / 1024 / 1024, 2) AS total_pga_allocated_mb,
       ROUND((p.value - p_old.value + px.value - px_old.value) / 1024 / 1024, 2) AS total_pga_used_mb
  FROM DBA_HIST_SNAPSHOT s,
       DBA_HIST_PGASTAT p,
       DBA_HIST_PGASTAT p_old,
       DBA_HIST_PGASTAT px,
       DBA_HIST_PGASTAT px_old
 WHERE s.snap_id = p.snap_id
   AND s.snap_id = p_old.snap_id(+)
   AND s.snap_id = px.snap_id
   AND s.snap_id = px_old.snap_id(+)
   AND p.name = 'total PGA allocated'
   AND p_old.name(+) = 'total PGA allocated'
   AND px.name = 'total PGA used for auto workareas'
   AND px_old.name(+) = 'total PGA used for auto workareas'
   AND s.instance_number = p.instance_number
   AND s.instance_number = p_old.instance_number(+)
   AND s.instance_number = px.instance_number
   AND s.instance_number = px_old.instance_number(+)
 ORDER BY s.snap_id;




Current Usage :


set line 200
col name format a70
col value format 999,999,999,999
select name , value/1024/1024 "size_in_mb" , unit  from v$pgastat ;

col username format a20
col program format a20
SELECT s.sid,
       s.serial#,
       s.username,
       s.program,
       n.name,
       p2.pga_alloc_mem / 1024 / 1024 AS allocated_mb,
       p2.pga_used_mem / 1024 / 1024 AS used_mb
  FROM v$session s  , v$process p2 ,v$sesstat p ,   v$statname n
    where   s.paddr = p2.addr 
      and s.sid = p.sid 
     and p.statistic# = n.statistic# 
and  n.name = 'session pga memory'
   AND s.username IS NOT NULL
 ORDER BY allocated_mb DESC;


Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more