Lesson 07
===============================================================================
Managing Memory
- by Full auto, Semi-automatic, manual
Full Auto:
- memory_target <-
set database RAM start, other component auto adjust
memory_max_target <- hard limit, if memory_target=500,
max 800 DB cannot over 800.
- buffer Cache (SGA)
Base on db_block_size
(8K default), if not use auto, set db_cache_size for manual
- buffer status
Pinned:
server process or background process locked RAM (database latch) it will caused
latch wait.
Clean:
unpinned RAM, wait checkpoint to HDD
Free/ unused
Dirty:
unpinned buffer which still not write to HDD (2,3 sec).
- Support multiple block size in same database (unix
only)
Try:
"create tablespace 'name' datafile 'location' size 50M blocksize
32768;"
- if create non-default data block size tablespace,
you should config other size cache_size
How many db_cache_size should set ?
- check v$db_cache_advice; (size_factor=1 is
current status), estd_physical_read is HDD I/O (lower is better).
select
SIZE_FACTOR, ESTD_PHYSICAL_READS from v$db_cache_advice;
* calculate buffer
cache hit ratio (statement not need to use HDD I/O)
Select name,value from v$sysstat where name in ('db
block gets from cache', 'consistent gets from cache', 'physical reads cache');
Ratio =
1-(('physical reads cache')/( 'consistent gets from cache'+'db block gets from
cache')x100), better 100%
select ((1-(select value from v$sysstat where
name='physical reads cache')/
((select value from v$sysstat where
name='consistent gets from cache')+
(select value from v$sysstat where name='db block
gets from cache')))*100) as cache_ratio from dual;
|
- multiple buffer pools
db_recycle_cache_size:
object will keep in memory but fast in fast out (faster remove object in buffer
pool)
db_keep_cache_size: object keep in pool,
retain object sin memory (remove object from buffer pool few hours)
db_cache_size: object will keep 3 sec in
memory then remove object from buffer pool
e.g. create
index cust_ids …. storage (buffer_pool keep);
alter
table oe.customers storage (buffer_pool recycle);
alter
index oe.cust_lname_ix storage (buffer_pool keep);
Shared pool (manual)
- large pool
RMAN backup,
parallel query operation
Shared server,
oracle X/Open <- 2/3PC (two/three phase commit), server to server commit and
rollback
Manage by
AMM and ASMM and parameter with "large_pool_size"
- log_buffer
Circular buffer
in SGA -> LGWR -> Redo log (size depend on HDD I/O, small is better)
* SGA should not use virtual memory, "lock_sga
set to true (not support in AMM)"
* set duplicatied SQL to keep pool or set buffer
cache higher or use memory adviser
AMM = automate PGA and SGA (40%, 60% ratio)
ASMM = Semi-automatic SGA, manual PGA
Off AMM & ASMM = all manual
Oracle recommends use AMM unless you have special
requirments.
- if set wrong memory_target setting
create
pfile from spfile;
edit
memory_target
create
spfile from pfile;
or
restore
spfile from autobackup
or
by
alert.log startup log (control_files, memory_target, db_name)
- if memory_max_target not set,
memory_max_target=memory_target
If enable
memory_target, sga_target and pga_aggregate_target. It means the PGA and SGA
are minimum values
- monitor AMM by "v$memory_dynamic_components",
"v$memory_resize_ops","v$memory_target_advice"
ASMM
- disable memory_target, you should set sga_target
and pga_aggregate_target to use ASMM.
If you need
fix PGA but dynamic SGA size
If AMM
disable pga_aggregate_target is fixed size
- in ASMM sga_target is dynamic, ASMM base on MMON
process and MMON use memory advisor.
- Get minimum value for sga_target, if database in
manual mode
select
((select sum(value) from v$sga)-(select current_size from
v$sga_dynamic_free_memory))
"sga_target"
from dual;
Disable ASMM to manual mode
- when memory_target disable and set sga_target=0,
database will auto tune memory and write to parameter.
- show parameter "pool_size", show
parameter "db_cache_size"
- select * from v$sga_dynamic_components;
沒有留言:
張貼留言