Oracle的SGA

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.