Oracle的SGA

The database has logical structures and physical structures . Because the physical and

logical structures are separate, the physical storage of data can be managed without

affecting the access to logical storage structures.

Oracle数据库的基本体系结构包括：physical and logical storage structures也就是物理和逻辑存储结构，物理存储结构即数据库，逻辑存储结构即数据库实例。数据库实例由内存结构和后台进程结构组成，这里来学习下内存结构（SGA，PGA）,从下图中我们可以很清晰地理解SGA和PGA：

当实例启动时会分配SGA(System Global Area) 服务器进程建立时会分配PGA(System Global Area)，我们可以这么查看系统全局区：

A system global area (SGA) is a group of shared memory structures that contain data

and control information for one Oracle database instance. If multiple users are

concurrently connected to the same instance , then the data in the instance’s SGA is

shared among the users. Consequently, the SGA is sometimes called the shared global

area .

An SGA and Oracle processes constitute an Oracle instance. Oracle automatically

allocates memory for an SGA when you start an instance, and the operating system

reclaims the memory when you shut down the instance. Each instance has its own

SGA.

The SGA is read/write. All users connected to a multiple-process database instance

can read information contained within the in stance’s SGA, and several processes write

to the SGA during execution of Oracle.

The SGA contains the following data structures:

■ Database buffer cache

■ Redo log buffer

■ Shared pool

■ Java pool

■ Large pool (optional)

■ Streams pool

■ Data dictionary cache

■ Other miscellaneous information

Automatic Shared Memory Management

In previous database releases, a database administrator (DBA) was required to

manually specify different SGA component sizes by setting a number of initialization

parameters, including the SHARED_POOL_SIZE , DB_CACHE_SIZE , JAVA_POOL_

SIZE, and LARGE_POOL_SIZE parameters. Oracle Database 10g includes the

Automatic Shared Memory Management feature which simplifies the SGA memory

management significantly. In Oracle Database 10g, a DBA can simply specify the total

amount of SGA memory availabl e to an instance using the SGA_TARGET initialization

parameter and the Oracle Database will au tomatically distribute this memory among

various subcomponents to ensure mo st effective memory utilization.

When automatic SGA memory management is enabled, the sizes of the different SGA

components are flexible and can adapt to the needs of a workload without requiring

any additional configuration. The database automatically distributes the available

memory among the various components as required, allowing the system to maximize

the use of all available SGA memory.

Note: Do not dynamically set or unset the SGA_TARGET parameter.

This should be set only at startup

Automatically Managed SGA Components

When you set a value for SGA_TARGET, Oracle Database 10 g automatically sizes the

most commonly configured components, including:

■ The shared pool (for SQL and PL/SQL execution)

■ The Java pool (for Java execution state)

■ The large pool (for large allocations such as RMAN backup buffers)

■ The buffer cache

■ The Streams pool

在10g之后，SGA中各个 data structures的大小是自动管理，现在我们只需要关心SGA设置多大，无需再设置共享池，数据缓冲区，Java池等的大小，另外Oracle会根据数据库整体情况给出SGA大小的建议，下面这个是从EM中查看到的SGA内存大小分配建议：

这些建议的值来自下面的视图：

--Oracle给出SGA大小的建议来自这个视图

SELECT * FROM v$sga_target_advice;

--计算公式：

SELECT a.sga_size,

ROUND(100 * (1 - a.estd_db_time_factor) / a.estd_db_time_factor, 2)

FROM v$sga_target_advice a

ORDER BY a.sga_size;

针对各个参数的设置和详细的解释我们可以查看Oracle的官方文档：Concepts

END-lubinsu.