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 1000SQL> set pages 1000SQL> 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_NUMBERORDER BY sn.snap_id DESC, sn.INSTANCE_NUMBER; |
(OR)
select (sga+pga)/1024/1024 as “sga_pga”
from
(select sum(value) sga from v$sga),
(select sum(pga_used_mem) pga from v$process);
