学步园

--==============================

--Oracle 10g SGA 的自动化管理

--==============================

/*

    在SGA中每一个单独的组件究竟需要多少内存呢？在Oracle 10g 中可以自动化管理大多数SGA参数。

 

一、什么是SGA

    简言之，系统全局区(SGA)仅仅是在Oracle实例启动时的共享内存结构，提供了实例和控制它行为的一些信息。下面

的表中给出了关于SGA一些特定组件的概要，控制内存大小分配的可变参数，以及这些特定的组件将影响Oracle server

哪些范围，并给出了一个简短的描述。从这个简短的列表中我们可以发现，在没有彻底地理解我们的应用程序后台的工

作状况下，以我们能力去推断并给这些组件中每一个设定内存的大小并不总是最优的选择，因为有大量的选项可供选择。

我们并不希望发生浪费可用内存的情况。

   

二、SGA组件

--组件列表使用原使文档，未翻译

 

SGA COMPONENT	SIZE CONTROLED BY	AREAS OF INFLUENCE	SIMPLE DESCRIPTONS
Shared Pool	SHARED_POOL_SIZE	Library Cache ·  Shared SQL areas ·  Private SQL areas ·  PL/SQL procedures and packages ·  Various control structures	Oracle needs to allocate & deallocate memory as SQL or procedural code is executed based on the individual needs of users' sessions and in accordance to the LRU algorithm.
		Dictionary Cache ·  Row cache ·  Library cache	Highly accessed memory structures that provide information on object structures to SQL statements being parsed.
Java Pool	JAVA_POOL_SIZE	·  Run state ·  Methods ·  Classes ·  Session code ·  Data in JVM	Memory available for the Java memory manager to use for all things Java.
Streams Pool	STREAMS_POOL_SIZE	·  Stream activity	New to Oracle 10g, memory available for stream processing.
Redo Log Buffer	LOG_BUFFER	·  Redo entries	Holds changes made to data and allows for the reconstruction of data in the case of failure.
Database Buffer Cache	DB_2K_CACHE_SIZE DB_4K_CACHE_SIZE DB_8K_CACHE_SIZE DB_16K_CACHE_SIZE DB_32K_CACHE_SIZE DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE	·  Write list ·  LRU list	Holds copies of data requested by SQL and reduces requests to disk by having data in memory. You may have many different buffer caches that help segregate on usage patterns.
Large Pool	LARGE_POOL_SIZE	·  Shared server ·  Oracle XA ·  I/O server processes ·  Backup & restore	For large memory allocations.

          

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

             

 

你可以通过初始化参数查看并控制SGA的大小   。下面是一个查询并提供了输出结果。 */

    select name, value

    from v$parameter

    where name in ('shared_pool_size', 'java_pool_size', 'streams_pool_size',

     'log_buffer', 'db_cache_size', 'db_2k_cache_size', 'db_4k_cache_size',

     'db_8k_cache_size', 'db_16k_cache_size', 'db_32k_cache_size',

     'db_keep_cache_size', 'db_recycle_cache_size', 'large_pool_size');

     

    NAME                      VALUE

    ------------------------- ---------

    shared_pool_size          83886080

    large_pool_size           8388608

    java_pool_size            50331648

    streams_pool_size         54525952

    db_cache_size             25165824

    db_2k_cache_size          0

    db_4k_cache_size          0

    db_8k_cache_size          0

    db_16k_cache_size         0

    db_32k_cache_size         0

    db_keep_cache_size        0

    db_recycle_cache_size     0

    log_buffer                262144

 

    13 rows selected.

/*

 

三、使用-->