Article presents how to create simple report to monitor PGA in Oracle database using ASH or AWR as source.

PGA is dedicated memory structure in SGA stores stores following areas:

Cursors and private sql areas

•  opened cursors, links to shared SQL areas in shared pool
• bind variable for cursors
•  query execution state information

SQL work areas

• sort operations (order by, group-by, rollup, window function)
• hash-join
• bitmap merge
• bitmap create

SQL work areas can be very big and requires detail attention especially for database warehouses. Information about current and historical PGA usage can be found in views:

• DBA_HIST_ACTIVE_SESS_HISTORY – AWR data
• GV$ACTIVE_SESSION_HISTORY – ASH data

Following query returns PGA usage for AWR history DBA_HIST_ACTIVE_SESS_HISTORY table. It expects three parameters:

• 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.

The same report can be used to generate data from GV$ACTIVE_SESSION_HISTORY

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;

DATE_MIN             AVG_PGA_MB MIN_PGA_MB MAX_PGA_MB
-------------------- ---------- ---------- ----------
2016-01-28 05:54:00       12.36       4.77      27.54
2016-01-28 06:44:00       16.3        2.04      42.54
2016-01-28 09:04:00      850.21       3.79    4145.28
2016-01-28 09:14:00      56.52       39.77      91.52
2016-01-28 10:44:00       8.79        8.79       8.79
2016-01-28 16:34:00       0           0          0
2016-01-28 21:24:00       0           0          0
2016-01-28 22:14:00       2.79        2.79       2.79
2016-01-28 23:14:00       0           0          0
2016-01-28 23:24:00       0           0          0

I have created sql developer reports based on the query

Full code of report is in here:

pga per user

1 file(s) 10.88 KB

Download

You can import this report to sqldeveloper “View”->”Reports”->”User defined reports” – click right mouse button you should see “Open Report” and select the report. It should import the report to your sqldeveloper.

Have a fun 🙂

Tomasz