Oracle 查看SGA PGA使用率

Oracle 查看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;