查询 SGA ,PGA 等内存信息SQL

查询 SGA ,PGA 等内存信息(网络搜来的实不知出处 见谅)

SELECT NAME,
       TOTAL,
       ROUND(TOTAL - FREE, 2) USED,
       ROUND(FREE, 2) FREE,
       ROUND((TOTAL - FREE) / TOTAL * 100, 2) PCTUSED
  FROM (SELECT 'SGA' NAME,
               (SELECT SUM(VALUE / 1024 / 1024) FROM V$SGA) TOTAL,

               (SELECT SUM(BYTES / 1024 / 1024)
                  FROM V$SGASTAT
                 WHERE NAME = 'free memory') FREE
          FROM DUAL)

UNION

SELECT NAME,
       TOTAL,
       ROUND(USED, 2) USED,
       ROUND(TOTAL - USED, 2) FREE,
       ROUND(USED / TOTAL * 100, 2) PCTUSED
  FROM (

        SELECT 'PGA' NAME,
                (SELECT VALUE / 1024 / 1024 TOTAL
                   FROM V$PGASTAT
                  WHERE NAME = 'aggregate PGA target parameter') TOTAL,

                (SELECT VALUE / 1024 / 1024 USED
                   FROM V$PGASTAT
                  WHERE NAME = 'total PGA allocated') USED
          FROM DUAL)

UNION

SELECT NAME,
       ROUND(TOTAL, 2) TOTAL,
       ROUND((TOTAL - FREE), 2) USED,
       ROUND(FREE, 2) FREE,
       ROUND((TOTAL - FREE) / TOTAL * 100, 2) PCTUSED
  FROM (

        SELECT 'Shared pool' NAME,
                (SELECT SUM(BYTES / 1024 / 1024)
                   FROM V$SGASTAT
                  WHERE POOL = 'shared pool') TOTAL,

                (SELECT BYTES / 1024 / 1024
                   FROM V$SGASTAT
                  WHERE NAME = 'free memory'
                    AND POOL = 'shared pool') FREE
          FROM DUAL)

UNION

SELECT NAME,
       ROUND(TOTAL, 2) TOTAL,
       ROUND(TOTAL - FREE, 2) USED,
       ROUND(FREE, 2) FREE,
       ROUND((TOTAL - FREE) / TOTAL, 2) PCTUSED
  FROM (

        SELECT 'Default pool' NAME,
                (SELECT A.CNUM_REPL *
                        (SELECT VALUE
                           FROM V$PARAMETER
                          WHERE NAME = 'db_block_size') / 1024 / 1024 TOTAL
                   FROM X$KCBWDS A, V$BUFFER_POOL P

                  WHERE A.SET_ID = P.LO_SETID
                    AND P.NAME = 'DEFAULT'
                    AND P.BLOCK_SIZE =
                        (SELECT VALUE
                           FROM V$PARAMETER
                          WHERE NAME = 'db_block_size')) TOTAL,

                (SELECT A.ANUM_REPL *
                        (SELECT VALUE
                           FROM V$PARAMETER
                          WHERE NAME = 'db_block_size') / 1024 / 1024 FREE
                   FROM X$KCBWDS A, V$BUFFER_POOL P

                  WHERE A.SET_ID = P.LO_SETID
                    AND P.NAME = 'DEFAULT'
                    AND P.BLOCK_SIZE =
                        (SELECT VALUE
                           FROM V$PARAMETER
                          WHERE NAME = 'db_block_size')) FREE
          FROM DUAL)

UNION

SELECT NAME,
       NVL(ROUND(TOTAL, 2), 0) TOTAL,
       NVL(ROUND(TOTAL - FREE, 2), 0) USED,
       NVL(ROUND(FREE, 2), 0) FREE,
       NVL(ROUND((TOTAL - FREE) / TOTAL, 2), 0) PCTUSED
  FROM (

        SELECT 'KEEP pool' NAME,
                (SELECT A.CNUM_REPL *
                        (SELECT VALUE
                           FROM V$PARAMETER
                          WHERE NAME = 'db_block_size') / 1024 / 1024 TOTAL
                   FROM X$KCBWDS A, V$BUFFER_POOL P

                  WHERE A.SET_ID = P.LO_SETID
                    AND P.NAME = 'KEEP'
                    AND P.BLOCK_SIZE =
                        (SELECT VALUE
                           FROM V$PARAMETER
                          WHERE NAME = 'db_block_size')) TOTAL,

                (SELECT A.ANUM_REPL *
                        (SELECT VALUE
                           FROM V$PARAMETER
                          WHERE NAME = 'db_block_size') / 1024 / 1024 FREE
                   FROM X$KCBWDS A, V$BUFFER_POOL P

                  WHERE A.SET_ID = P.LO_SETID
                    AND P.NAME = 'KEEP'
                    AND P.BLOCK_SIZE =
                        (SELECT VALUE
                           FROM V$PARAMETER
                          WHERE NAME = 'db_block_size')) FREE
          FROM DUAL)

UNION

SELECT NAME,
       NVL(ROUND(TOTAL, 2), 0) TOTAL,
       NVL(ROUND(TOTAL - FREE, 2), 0) USED,
       NVL(ROUND(FREE, 2), 0) FREE,
       NVL(ROUND((TOTAL - FREE) / TOTAL, 2), 0) PCTUSED
  FROM (

        SELECT 'RECYCLE pool' NAME,
                (SELECT A.CNUM_REPL *
                        (SELECT VALUE
                           FROM V$PARAMETER
                          WHERE NAME = 'db_block_size') / 1024 / 1024 TOTAL
                   FROM X$KCBWDS A, V$BUFFER_POOL P

                  WHERE A.SET_ID = P.LO_SETID
                    AND P.NAME = 'RECYCLE'
                    AND P.BLOCK_SIZE =
                        (SELECT VALUE
                           FROM V$PARAMETER
                          WHERE NAME = 'db_block_size')) TOTAL,

                (SELECT A.ANUM_REPL *
                        (SELECT VALUE
                           FROM V$PARAMETER
                          WHERE NAME = 'db_block_size') / 1024 / 1024 FREE
                   FROM X$KCBWDS A, V$BUFFER_POOL P

                  WHERE A.SET_ID = P.LO_SETID
                    AND P.NAME = 'RECYCLE'
                    AND P.BLOCK_SIZE =
                        (SELECT VALUE
                           FROM V$PARAMETER
                          WHERE NAME = 'db_block_size')) FREE
          FROM DUAL)

UNION

SELECT NAME,
       NVL(ROUND(TOTAL, 2), 0) TOTAL,
       NVL(ROUND(TOTAL - FREE, 2), 0) USED,
       NVL(ROUND(FREE, 2), 0) FREE,
       NVL(ROUND((TOTAL - FREE) / TOTAL, 2), 0) PCTUSED
  FROM (

        SELECT 'DEFAULT 16K buffer cache' NAME,
                (SELECT A.CNUM_REPL * 16 / 1024 TOTAL
                   FROM X$KCBWDS A, V$BUFFER_POOL P

                  WHERE A.SET_ID = P.LO_SETID
                    AND P.NAME = 'DEFAULT'
                    AND P.BLOCK_SIZE = 16384) TOTAL,

                (SELECT A.ANUM_REPL * 16 / 1024 FREE
                   FROM X$KCBWDS A, V$BUFFER_POOL P

                  WHERE A.SET_ID = P.LO_SETID
                    AND P.NAME = 'DEFAULT'
                    AND P.BLOCK_SIZE = 16384) FREE
          FROM DUAL)

UNION

SELECT NAME,
       NVL(ROUND(TOTAL, 2), 0) TOTAL,
       NVL(ROUND(TOTAL - FREE, 2), 0) USED,
       NVL(ROUND(FREE, 2), 0) FREE,
       NVL(ROUND((TOTAL - FREE) / TOTAL, 2), 0) PCTUSED
  FROM (

        SELECT 'DEFAULT 32K buffer cache' NAME,
                (SELECT A.CNUM_REPL * 32 / 1024 TOTAL
                   FROM X$KCBWDS A, V$BUFFER_POOL P

                  WHERE A.SET_ID = P.LO_SETID
                    AND P.NAME = 'DEFAULT'
                    AND P.BLOCK_SIZE = 32768) TOTAL,

                (SELECT A.ANUM_REPL * 32 / 1024 FREE
                   FROM X$KCBWDS A, V$BUFFER_POOL P

                  WHERE A.SET_ID = P.LO_SETID
                    AND P.NAME = 'DEFAULT'
                    AND P.BLOCK_SIZE = 32768) FREE
          FROM DUAL)

UNION

SELECT NAME,
       TOTAL,
       TOTAL - FREE USED,
       FREE,
       (TOTAL - FREE) / TOTAL * 100 PCTUSED
  FROM (

        SELECT 'Java Pool' NAME,
                (SELECT SUM(BYTES / 1024 / 1024) TOTAL
                   FROM V$SGASTAT
                  WHERE POOL = 'java pool'
                  GROUP BY POOL) TOTAL,

                (SELECT BYTES / 1024 / 1024 FREE
                   FROM V$SGASTAT
                  WHERE POOL = 'java pool'
                    AND NAME = 'free memory') FREE
          FROM DUAL)

UNION

SELECT NAME,
       ROUND(TOTAL, 2),
       ROUND(TOTAL - FREE, 2) USED,
       ROUND(FREE, 2) FREE,
       ROUND((TOTAL - FREE) / TOTAL * 100, 2) PCTUSED
  FROM (

        SELECT 'Large Pool' NAME,
                (SELECT SUM(BYTES / 1024 / 1024) TOTAL
                   FROM V$SGASTAT
                  WHERE POOL = 'large pool'
                  GROUP BY POOL) TOTAL,

                (SELECT BYTES / 1024 / 1024 FREE
                   FROM V$SGASTAT
                  WHERE POOL = 'large pool'
                    AND NAME = 'free memory') FREE
          FROM DUAL)

 ORDER BY PCTUSED DESC;