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

时间:2020-12-13 06:03:25

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


Buffer Cache

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


Using Multiple Buffer Pools

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


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


Shared Pool

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

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

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

实际上加载JAVA类的时候需要共享池的,一般一个JAVA类需要 8K的 shared pool的大小。 运行JAVA程序 实际上需要两块内存: shared pool , java pool


Redo Log Buffer

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

Oracle Database Memory Parameters

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

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

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



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



PGA

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


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

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


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