If you prefer to exercise more direct control over the sizes of individual memory components, you can disable automatic memory management and configure the database for manual memory management. There are two different manual memory management methods for the SGA, and two for the instance PGA.

你可以不开启自动内存管理，为数据库配置手工管理内存，有两种手工管理SGA，两种管理PGA。


The two manual memory management methods for the SGA vary in the amount of effort and knowledge required by the DBA. With automatic shared memory management, you set target and maximum sizes for the SGA. The database then sets the total size of the SGA to your designated target, and dynamically tunes the sizes of many SGA components. With manual shared memory management, you set the sizes of several individual SGA components, thereby determining the overall SGA size. You then manually tune these individual SGA components on an ongoing basis.

在DBA的需求中，有两种手工管理SGA的方法。在自动共享内存管理中，你设置目标和最大容量为SGA，数据库于是设置总的SGA大小到你设计的目标，并且动态调整多个SGA组件的大小。
在手工管理共享内存中，你设置多个独自SGA组件的大小，因此决定整个SGA的大小。你那么手工调整这些单独SGA组建。

For the instance PGA, there is automatic PGA memory management, in which you set a target size for the instance PGA. The database then sets the size of the instance PGA to your target, and dynamically tunes the sizes of individual PGAs. There is also manual PGA memory management, in which you set maximum work area size for each type of SQL operator (such as sort or hash-join). This memory management method, although supported, is not recommended.

对于PGA来说，你设置目标大小为PGA在自动PGA内存管理。数据库设置PGA的大小，动态调整独自PGA组建的大小。
也有手工PGA内存管理，你设置最大工作区域为每种SQL操作（例如排序或者哈希链接），这种内存管理方法，尽管支持，不推荐。

About Automatic Shared Memory Management

Automatic Shared Memory Management simplifies SGA memory management. You specify the total amount of SGA memory available to an instance using the SGA_TARGET initialization parameter and Oracle Database automatically distributes this memory among the various SGA components to ensure the most effective memory utilization.

设置SGA_TARGET来实现自动管理SGA

Components and Granules in the SGA

The memory for dynamic components in the SGA is allocated in the unit of granules. Granule size is determined by total SGA size. Generally speaking, on most platforms, if the total SGA size is equal to or less than 1 GB, then granule size is 4 MB. For SGAs larger than 1 GB, granule size is 16 MB. Some platform. dependencies may arise. For example, on 32-bit Windows NT, the granule size is 8 MB for SGAs larger than 1 GB. Consult your operating system specific documentation for more details.

粒度大小由总的SGA大小决定，通常来说，在大部分平台，如果总的SGA大小等于货小于1GB，那么粒度大小是4MB；大于1GB的话，粒度大小是16MB。一些平台可能会高一点。例如在32位windowsNT，粒度大小是8M在SGA
大于1GB。

You can query the V$SGAINFO view to see the granule size that is being used by an instance. The same granule size is used for all components in the SGA.

你可以查询v$SGAINFO 视图来查看粒度的大小，一样粒度大小被使用为所有的组件在SGA中。

Setting Maximum SGA Size

The SGA_MAX_SIZE initialization parameter specifies the maximum size of the System Global Area for the lifetime of the instance. You can dynamically alter the initialization parameters affecting the size of the buffer caches, shared pool, large pool, Java pool, and streams pool but only to the extent that the sum of these sizes and the sizes of the other components of the SGA (fixed SGA, variable SGA, and redo log buffers) does not exceed the value specified by SGA_MAX_SIZE.

SGA_MAX_SIZE参数指定最大大小系统全局区域为整个实例的生存周期中，你可以动态的改变参数来影响 buffer caches, shared pool, large pool, Java pool, and streams pool的大小只有当这些大小的总数和其他组建（fixed SGA, variable SGA, and redo log buffers）的大小的总数不超过他。

If you do not specify SGA_MAX_SIZE, then Oracle Database selects a default value that is the sum of all components specified or defaulted at initialization time. If you do specify SGA_MAX_SIZE, and at the time the database is initialized the value is less than the sum of the memory allocated for all components, either explicitly in the parameter file or by default, then the database ignores the setting for SGA_MAX_SIZE and chooses a correct value for this parameter.

如果你没有指定 SGA_MAX_SIZE,那么数据库选择默认的总的组建大小的值，或者默认在初始时间。如果你没指定SGA_MAX_SIZE，并且在那段时间数据库少于总的大小，或者超过默认参数文件，那么数据库忽略设置SGA_MAX_SIZE，选择正确的值。

Setting SGA Target Size

You enable the automatic shared memory management feature by setting the SGA_TARGET parameter to a nonzero value. This parameter sets the total size of the SGA. It replaces the parameters that control the memory allocated for a specific set of individual components, which are now automatically and dynamically resized (tuned) as needed.

你可以开启自动管理共享内存，通过设置SGA_TARGET为非零值，这个参数设置总的SGA大小。他取代控制独自组建的内存大小，现在可以根据需要自动调整大小。

The STATISTICS_LEVEL initialization parameter must be set to TYPICAL (the default) or ALL for automatic shared memory management to function.

STATISTICS_LEVEL这个参数必须设置为TYPICAL或者ALL为自动管理共享内存的功能。

able 6-1 Automatically Sized SGA Components and Corresponding Parameters

SGA Component	Initialization Parameter
Fixed SGA and other internal allocations needed by the Oracle Database instance	N/A
The shared pool	SHARED_POOL_SIZE
The large pool	LARGE_POOL_SIZE
The Java pool	JAVA_POOL_SIZE
The buffer cache	DB_CACHE_SIZE
The Streams pool	STREAMS_POOL_SIZE

自动管理内存的组建

Table 6-2 Manually Sized SGA Components that Use SGA_TARGET Space

SGA Component	Initialization Parameter
The log buffer	LOG_BUFFER
The keep and recycle buffer caches	DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE
Nonstandard block size buffer caches	DB_nK_CACHE_SIZE

在自动管理内存的组建下，要手工管理的内存组建

In addition to setting SGA_TARGET to a nonzero value, you must set to zero all initialization parameters listed in to enable full automatic tuning of the automatically sized SGA components.

Alternatively, you can set one or more of the automatically sized SGA components to a nonzero value, which is then used as the minimum setting for that component during SGA tuning. This is discussed in detail later in this section.

在另外设置SGA_TARGET为非零值，你必须另外设置零值为table6-1的各个组建，来达到全部调整共享内存组建。

另外，你可以设置一个或者多个自动调整SGA组建为非零值，这为了达到最小分配改组建的功能。

SGA and Virtual Memory

For optimal performance in most systems, the entire SGA should fit in real memory. If it does not, and if virtual memory is used to store parts of it, then overall database system performance can decrease dramatically. The reason for this is that portions of the SGA are paged (written to and read from disk) by the operating system.

为了优化性能在大多数系统中，整个SGA应该匹配真正的内存。如果不是这样的话，如果虚拟内存用来存储它，那么整个数据库性能可能急速下降。理由是这一部分的SGA被分页（读写都来自硬盘）。

Monitoring and Tuning SGA Target Size

监控和调整SGA目标大小

The V$SGAINFO view provides information on the current tuned sizes of various SGA components.

The

Monitoring and Tuning SGA Target Size

The V$SGAINFO view provides information on the current tuned sizes of various SGA components.

The V$SGA_TARGET_ADVICE view provides information that helps you decide on a value for SGA_TARGET.

V$SGAINFO 视图提供信息关于现在各个SGA组建的调整

V$SGA_TARGET_ADVICE 视图 提供信息来帮助你决定SGA_TARGET 的信息。

sys@ORCL> select * from v$sga_target_advice order by sga_size;

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
       150        .5        136          1.1724           1495
       225           .75        116           1           1495
       300         1        116           1           1495
       375          1.25        116           1           1495
       450           1.5        116           1           1495
       525          1.75        116           1           1495                                                                       600         2        116           1           1495

Enabling Automatic Shared Memory Management

1         Run the following query to obtain a value for SGA_TARGET:

SELECT (
   (SELECT SUM(value) FROM V$SGA) -
   (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
   ) "SGA_TARGET"
FROM DUAL;

SELECT (
   (SELECT SUM(value) FROM V$SGA) -
   (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
   ) "SGA_TARGET"
  5  FROM DUAL;

SGA_TARGET
----------
 313159680

2  alter system set sga_max_size=350 scope=spfile;
shut immediate;
startup;
alter system set sga_max_size=350 scope=spfile;

   Do one of the following:  做其中两个之一

1. • For more complete automatic tuning, set the values of the automatically sized SGA components listed in to zero. Do this by editing the text initialization parameter file or by issuing ALTER SYSTEM statements.   为了更加完全的自动调整，其他可以控制SGA组件都设置为零
     

   • To control the minimum size of one or more automatically sized SGA components, set hose component sizes to the desired value. (See the next section for details.) Set the values of the other automatically sized SGA components to zero. Do this by editing the text initialization parameter file or by issuing ALTER SYSTEM statements.     其他可以SGA组件可以非零，可以获得最小的组件的内存大小
     

4  alter system set memory_target=0 scope=both;