oracle10g/11g 新特性四(管理内存)

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