Oracle Database 内存管理

DB?BUFFER?CACHE
SQL>?show?parameter?db_block_size
?
NAME?????????????????????????????????TYPE??????????????????????????????VALUE
------------------------------------?---------------------------------?------------------------------
db_block_size????????????????????????integer???????????????????????????8192
?
SQL>?select?*?from?v$sgainfo?where?name='Granule?Size';
?
NAME????????????????????????????????BYTES?RESIZEABL
------------------------------?----------?---------
Granule?Size?????????????????????16777216?No
?
SQL>?select?16777216/8192?from?dual;?--?granule中包含2048个buffer
16777216/8192
-------------
?????????2048
??????????
SQL>?select?*?from?v$sgainfo?where?name='Buffer?Cache?Size';
?
NAME????????????????????????????????BYTES?RESIZEABL
------------------------------?----------?---------
Buffer?Cache?Size??????????????1795162112?Yes
?
SQL>?select?1795162112/1024/1024?from?dual;
?
1795162112/1024/1024
--------------------
????????????????1712
?????????????????
SQL>?select?1795162112/8192?from?dual;??--db_cache中包含219136个buffer
1795162112/8192
---------------
?????????219136
??????????
SQL>?select?219136/2048?from?dual;?--db_cache由107个granule组成
219136/2048
-----------
????????107

SHARED?POOL
SQL>?select?*?from?v$sgainfo?where?name='Shared?Pool?Size';
?
NAME????????????????????????????????BYTES?RESIZEABL
------------------------------?----------?---------
Shared?Pool?Size????????????????285212672?Yes
?
SQL>?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_size
?
NAME?????????????????????????????????TYPE??????????????????????????????VALUE
------------------------------------?---------------------------------?------------------------------
shared_pool_reserved_size????????????big?integer???????????????????????14260633
?
SQL>?col?ksppinm?for?a35
SQL>?col?ksppstvl?for?a20
SQL>?col?ksppdesc?for?a80
SQL>?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?pool
?
?
SQL>?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?a35
SQL>?col?ksppstvl?for?a20
SQL>?col?ksppdesc?for?a50
SQL>?select?ksppinm,x$ksppcv?y?where?x.indx?=?y.indx?and?ksppinm?=?'_kghdsidx_count';
?
KSPPINM?????????????????????????????KSPPSTVL?????????????KSPPDESC
-----------------------------------?--------------------?--------------------------------------------------
_kghdsidx_count?????????????????????1????????????????????max?kghdsidx?count
?
SQL>?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):?????????????????64
shared?pool(1):???????????????????????208
shared?pool(Total):???????????????????272
?
SQL>??select?subpool,name,round(sum(bytes)/1048576,2)?"FREE?SIZE(MB)"
from?(select?'shared?pool?('?||?decode?(to_char?(ksmdsidx),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???????????????????????????????64
shared?pool?(1):???????????????free?memory????????????????????????????48.41

?

LOG?BUFFER
SQL>?select?*?from?v$sgainfo?where?name='Redo?Buffers';
?
NAME????????????????????????????????BYTES?RESIZEABL
------------------------------?----------?---------
Redo?Buffers??????????????????????4964352?No
?
SQL>?select?4964352/1024/1024?from?dual;
?
4964352/1024/1024
-----------------
?????????4.734375
?????????
SQL>?select?20*1024*1024?from?dual;
20*1024*1024
------------
????20971520

SQL>?alter?system?set?log_buffer=20971520?scope=spfile;

System?altered.
???????
SQL>?shutdown?immediate
Database?closed.
Database?dismounted.
ORACLE?instance?shut?down.
SQL>?startup
ORACLE?instance?started.

Total?System?Global?Area?2137886720?bytes
Fixed?Size??????????????????2215064?bytes
Variable?Size?????????????520094568?bytes
Database?Buffers?????????1593835520?bytes
Redo?Buffers???????????????21741568?bytes
Database?mounted.
Database?opened.
SQL>?select?*?from?v$sgainfo?where?name='Redo?Buffers';

NAME????????????????????????????????BYTES?RESIZEABL
------------------------------?----------?---------
Redo?Buffers?????????????????????21741568?No

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!