Oracle PGA monitoring using ASH and AWR

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

PGA_monitoring_01

Full code of report is in here:

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

 

Leave a Reply

Your email address will not be published. Required fields are marked *