首页 > 数据库 >Oracle 查看SGA PGA使用率

Oracle 查看SGA PGA使用率

时间:2023-04-22 09:13:31浏览次数:37  
标签:1024 name PGA free SGA Oracle total round select

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

相关文章

  • oracle 增长列-主键
      oracle英[ˈɒrəkl]美[ˈɔːrəkl]n.(古希腊的)神示所;(传达神谕的)牧师,女祭司;(古希腊常有隐含意义的)神谕,神示;能提供宝贵信息的人(或书);权威;智囊;  INSERTINTOtbl_testVALUES(seq_test.nextval,'测试');  seq_test  就是   sequence增长列    n......
  • Oracle:fedora-server-38:安装oracle11g
    基本过程不再赘述。关键点:问题1:解决1: 问题2:解决2:  问题3: 解决3: ......
  • Oracle RAC 更改DB_UNIQUE_NAME
    背景遇到一个场景是更改RAC架构下的OracleDB_UNIQUE_NAME,使得跟DB_NAME不一致,尝试了网上的方法,都没能成功,最后是看了官方support的solution,下面是主要操作步骤,11g203版本,已经验证是没问题的。具体操作步骤Forexample,adatabasethatwasoriginallycreatedwithGlob......
  • Oracle VM VirtualBox 全系列版本下载服务
    可提供OracleVMVirtualBox全系列版本下载服务:OracleVM3TemplateforOracleSunRaySoftwarewithOracleLinux5.3.0OracleVM3TemplatesforOracleLinux51.0.0.0.0OracleVM3TemplatesforOracleLinux61.0.0.0.0OracleVM3TemplatesforOracleLinux6......
  • Oracle:分页
    SQL: Selectenamefromempwhereenamelike'_M%'第二个字母Selectto_char(sysdate,'yyyy')afromdual//2018selectdeptno,job,avg(sal)fromempgroupbyjob,deptno部门职员平均工资select*fromempdeptselect*fromemp,deptwhereemp.deptno......
  • oracle:触发、分页
    分页解释:intindex=1;intsize=5;intstart=(index-1)*size+1;intend=index*size;--115--2610wheremrbetween6and10select*from(selecta.*,rownummrfromempa)wheremr>=6andmr<=10 触发:createorreplacefunctionf(xnumber)returnnumberi......
  • Oracle删除用户及用户下的全部数据
     1、查看用户select*fromall_usersselect*fromuser_usersselect*fromdba_users2、查看用户的连接状况selectusername,sid,serial#fromv$sessionwhereusername='NCC'3、找到要删除用户的sid,和serial,并删除altersystemkillsession'4521,27770'4......
  • 使用Oracle的springBoot
    application.ymlspring:application:name:zplatformdatasource:driver-class-name:oracle.jdbc.driver.OracleDriverurl:jdbc:oracle:thin:@主机号:端口号/数据库?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetriev......
  • oracle function 函数
    oraclefunction的几个实例,只限入门,高手路过 --********thesimplestexampleoffunction--tip:canusesqltodirectlycallthefunctionwithinmoduleparametercreateorreplacefunctionget_customer_namereturnvarchar2isv_uservarchar2(100);begins......
  • oracle constraint的属性
    constraint有三个属性:deferrabledeferredvalidated;其中deferrable和deferred联合使用,可以控制何时判断数据完整deferrabledefered就可以在commit时再判断;其中validated和enable联合使用,可以允许旧数据的不完整enablenotvalidated时,即可。 然后转一篇文章,讲解很详细:Oracle......