Oracle的SGA

The database has logical structures and physical structures . Because the physical andlogical structures are separate, the physical storage of data can be managed withoutaffecting 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 dataand control information for one Oracle database instance. If multiple users areconcurrently connected to the same instance , then the data in the instance’s SGA isshared among the users. Consequently, the SGA is sometimes called the shared globalarea .An SGA and Oracle processes constitute an Oracle instance. Oracle automaticallyallocates memory for an SGA when you start an instance, and the operating systemreclaims the memory when you shut down the instance. Each instance has its ownSGA.The SGA is read/write. All users connected to a multiple-process database instancecan read information contained within the in stance’s SGA, and several processes writeto 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 ManagementIn previous database releases, a database administrator （DBA） was required tomanually specify different SGA component sizes by setting a number of initializationparameters, including the SHARED_POOL_SIZE , DB_CACHE_SIZE , JAVA_POOL_SIZE, and LARGE_POOL_SIZE parameters. Oracle Database 10g includes theAutomatic Shared Memory Management feature which simplifies the SGA memorymanagement significantly. In Oracle Database 10g, a DBA can simply specify the totalamount of SGA memory availabl e to an instance using the SGA_TARGET initializationparameter and the Oracle Database will au tomatically distribute this memory amongvarious subcomponents to ensure mo st effective memory utilization.When automatic SGA memory management is enabled, the sizes of the different SGAcomponents are flexible and can adapt to the needs of a workload without requiringany additional configuration. The database automatically distributes the availablememory among the various components as required, allowing the system to maximizethe 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 ComponentsWhen you set a value for SGA_TARGET, Oracle Database 10 g automatically sizes themost 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 aORDER BY a.sga_size;针对各个参数的设置和详细的解释我们可以查看Oracle的官方文档：ConceptsEND-lubinsu.更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12AWR报告的使用数据类型不一致导致的SQL不走索引相关资讯 Oracle SGA

Oracle 10G启动时SGA大小（02/16/2015 10:34:47）
Oracle PGA和SGA区别以及latch和（02/10/2014 19:46:58）
Oracle SGA 自动管理特性（sga_ （04/24/2013 13:31:53）

Oracle Library Cache Object （05/10/2014 15:22:07）
Oracle基础教程之设置系统全局区（08/22/2013 14:24:00）

本文评论查看全部评论（0）