Oracle Database 記憶體管理

標籤：bytes   max   database   ransient   user   variable   show   ufs   ppc   

SGASQL> show parameter sga_targetNAME                                 TYPE                              VALUE------------------------------------ --------------------------------- ------------------------------sga_target                           big integer                       2GSQL> show parameter sga_max_sizeNAME                                 TYPE                              VALUE------------------------------------ --------------------------------- ------------------------------sga_max_size                         big integer                       2GSQL> select name,value/1024/1024 as "SIZE (MB)" from v$sga;NAME                            SIZE (MB)------------------------------ ----------Fixed Size                     2.11244965Variable Size                  320.000832Database Buffers                     1712Redo Buffers                     4.734375DB BUFFER CACHESQL> show parameter db_block_sizeNAME                                 TYPE                              VALUE------------------------------------ --------------------------------- ------------------------------db_block_size                        integer                           8192SQL> select * from v$sgainfo where name='Granule Size';NAME                                BYTES RESIZEABL------------------------------ ---------- ---------Granule Size                     16777216 NoSQL> select 16777216/8192 from dual; -- granule中包含2048個buffer16777216/8192-------------         2048         SQL> select * from v$sgainfo where name='Buffer Cache Size';NAME                                BYTES RESIZEABL------------------------------ ---------- ---------Buffer Cache Size              1795162112 YesSQL> select 1795162112/1024/1024 from dual;1795162112/1024/1024--------------------                1712                SQL> select 1795162112/8192 from dual;  --db_cache中包含219136個buffer1795162112/8192---------------         219136         SQL> select 219136/2048 from dual; --db_cache由107個granule組成219136/2048-----------        107        SHARED POOLSQL> select * from v$sgainfo where name='Shared Pool Size';NAME                                BYTES RESIZEABL------------------------------ ---------- ---------Shared Pool Size                285212672 YesSQL> select 285212672/1024/1024 from dual;285212672/1024/1024-------------------                272                 SQL> column indx heading "indx | indx num"SQL> column kghlurcr heading "recurrent|chunks"SQL> column kghlutrn heading "transient|chunks"SQL> column kghlufsh heading "flushed|chunks"SQL> column kghluops heading "pins and|releases"SQL> column kghlunfu heading "ora-4031|errors"SQL> column kghlunfs heading "last error|size"SQL> select indx,kghlurcr,kghlutrn,kghlufsh,kghluops,kghlunfu,kghlunfs from x$kghlu where inst_id=userenv('Instance');     indx   recurrent  transient    flushed   pins and   ora-4031 last error  indx num     chunks     chunks     chunks   releases     errors       size---------- ---------- ---------- ---------- ---------- ---------- ----------         0       3541       8077          0      22813          0          0               SQL> show parameter shared_pool_reserved_sizeNAME                                 TYPE                              VALUE------------------------------------ --------------------------------- ------------------------------shared_pool_reserved_size            big integer                       14260633SQL> col ksppinm for a35SQL> col ksppstvl for a20SQL> col ksppdesc for a80SQL> select ksppinm,ksppstvl,ksppdesc from x$ksppi x,x$ksppcv y where x.indx = y.indx and ksppinm = '_shared_pool_reserved_min_alloc';KSPPINM                             KSPPSTVL             KSPPDESC----------------------------------- -------------------- --------------------------------------------------------------------------------_shared_pool_reserved_min_alloc     4400                 minimum allocation size in bytes for reserved area of shared poolSQL> select free_space,avg_free_size,used_space,request_failures,last_failure_size from v$shared_pool_reserved;FREE_SPACE AVG_FREE_SIZE USED_SPACE REQUEST_FAILURES LAST_FAILURE_SIZE---------- ------------- ---------- ---------------- -----------------  10146416        724744   16704568                0                 0      SQL> col ksppinm for a35SQL> col ksppstvl for a20SQL> col ksppdesc for a50SQL> select ksppinm,ksppstvl,ksppdesc from x$ksppi x,x$ksppcv y where x.indx = y.indx and ksppinm = '_kghdsidx_count';KSPPINM                             KSPPSTVL             KSPPDESC----------------------------------- -------------------- --------------------------------------------------_kghdsidx_count                     1                    max kghdsidx countSQL> select 'shared pool('||nvl (decode (to_char(ksmdsidx),'0','0-Unused',ksmdsidx),'Total')||'):'subpool,round(sum(ksmsslen)/1048576,2) "SIZE(MB)"  2  from x$ksmss where ksmsslen > 0  3  group by rollup(ksmdsidx) order by subpool asc;SUBPOOL                          SIZE(MB)------------------------------ ----------shared pool(0-Unused):                 64shared pool(1):                       208shared pool(Total):                   272SQL>  select subpool,name,round(sum(bytes)/1048576,2) "FREE SIZE(MB)"from (select 'shared pool (' || decode (to_char (ksmdsidx),'0','0-Unused',ksmdsidx) || '):' subpool,ksmssnam name,ksmsslen bytes from x$ksmss where ksmsslen>0 and lower(ksmssnam) like lower ('%free memory%'))group by subpool,name order by subpool asc,sum(bytes) desc;SUBPOOL                        NAME                           FREE SIZE(MB)------------------------------ ------------------------------ -------------shared pool (0-Unused):        free memory                               64shared pool (1):               free memory                            48.41

Oracle Database 記憶體管理