生产环境查实际SGA和PGA的使用情况

说明：生产环境中，很多时候需要查询数据库SGA和PGA实际占用大小以了解内存使用情况.
以下为测试结果：

SELECT ROUND((a.SGA_MEM + b.PGA_MEM), 2) "TOTAL_MEMORY",

       ROUND(a.SGA_MEM, 2) sga,

       ROUND(b.PGA_MEM, 2) pga

  FROM (SELECT SUM(current_size) / 1024 / 1024 "SGA_MEM"

          FROM v$sga_dynamic_components,

               (SELECT SUM(pga_alloc_mem) / 1024 / 1024 "PGA_MEM"

                  FROM v$process) a

         WHERE component IN ('shared pool',

                             'large pool',

                             'java pool',

                             'streams pool',

                             'DEFAULT buffer cache')) a,

       (SELECT SUM(pga_alloc_mem) / 1024 / 1024 "PGA_MEM" FROM v$process) b;

 

TOTAL_MEMORY        SGA        PGA

------------ ---------- ----------

      2991.3       2848      143.3