The following query can be used to determine the memory usage rates in Oracle databases. If there is an increase in the memory values from time to time, it will be necessary to examine the processes in the relevant time interval. From the dba_hist_snapshot view, memory usage rates can be determined by a query such as the following.
SQL> set lines 1000
SQL> set pages 1000
SQL> SELECT sn.INSTANCE_NUMBER,
(sga.allo + pga.allo) tot,
TRUNC (SN.END_INTERVAL_TIME, 'mi') time
FROM ( SELECT snap_id,
ROUND (SUM (bytes) / 1024 / 1024 / 1024, 3) allo
GROUP BY snap_id, INSTANCE_NUMBER) sga,
( SELECT snap_id,
ROUND (SUM (VALUE) / 1024 / 1024 / 1024, 3) allo
WHERE name = 'total PGA allocated'
GROUP BY snap_id, INSTANCE_NUMBER) pga,
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
ORDER BY sn.snap_id DESC, sn.INSTANCE_NUMBER;
NOTE TO ANYONE READING THIS:
Great script, however an important item to note:
Provided you are licensed for the Diag+Tuning packs, this query is safe to run..
HOWEVER, if you are not licensed for those, you cannot even THINK of looking at the DBA_HIST_% views/tables because Oracle will tell you you’re in violation of the licensing agreement…