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;
标签:1024,name,PGA,free,SGA,Oracle,total,round,select From: https://www.cnblogs.com/william2019/p/17342415.html