Buffer Cache
Using Multiple Buffer Pools
Shared Pool
Large Pool
provides large memory allocation for:
a.session memory for the shared server and the Oracle XA interface
b.I/O server processes
c.Oracle database backup and restore operations
d.Parallel query operations
e.Advanced Queuing memory table storage
Redueces potentail fragmentation of shared pool
Is managed by AMM ans ASMM
Is size with LARGE_POOL_SIZE parameter
Java Pool and Streams Pool
实际上加载JAVA类的时候需要共享池的,一般一个JAVA类需要 8K的 shared pool的大小。 运行JAVA程序 实际上需要两块内存: shared pool , java pool
Redo Log Buffer
Oracle Database Memory Parameters
DB_KEEP_CACHE_SIZE , DB_RECYCLE_CACHE_SIZE,DB_nK_CACHE_SIZE仍然需要手工方式进行调整
MEMORY_TARGET非零 SGA_TARGET非零, PGA_AGGREGATE_TARGET 非零 ,则表示在自动调整过程中
SGA的最小值 和 PGA的最小值
如果MEMORY_TARGET零 SGA_TARGET非零, PGA_AGGREGATE_TARGET 非零 , 则总大小是SGA_TARGET+PGA_AGGREGATE_TARGET
How ASMM Works
1.ASMM is based on workload information that MMON captures in the backgroud
2.MMON uses memory advisors
3.Memory is moved to where it is needed the most by MMAN.
4.If an SPFILE is used (which is recommended)
a.Component sizes are saved across shutdowns
b.Saved values are used to bootstrap component sizes
c. There is no need to relearn optimal values
Enabling Automatic Shared Memory Management
PGA
Using the V$PARAMETER View
SGA_TARGET=8G
DB_CACHE_SIZE=0
JAVA_POOL_SIZE=0
LARGE_POOL_SIZE=0
SHARED_POOL_SIZE=0
STREAMS_POOL_SIZE=0
select name,value,isdefault from v$parameter where name like '%size'
Monitoring Automatic Memory Management
select component,current_size,min_size,max_size,last_oper_type from v$memory_dynamic_components;
select start_time,component,initial_size, target_size,final_size from v$memory_resize_ops
select * from v$memory_target_advice;
如果开启内存自动管理,则SGA 默认是 60% MEMORY_TARGET
Memory Tuning Guidelines for the Library Cache
1.Establish formatting conventions for developers so that SQL statements match in the cache
2.Use bind variables
3.Eliminate unnecessary duplicate SQL
4.Consider using CURSOR_SHARING
5.Use PL/SQL when possible
6.Cache sequence numbers
7.Pin objects in the library cache