How to find out total memory used by each database ?

DB Scripts

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);

Leave a Reply

Your email address will not be published.