Oracle architecture and backup (5) -- SGA

ArticleDirectory

• 2. Memory Management
• SGA allocation
• 4. Operation example
• Summary

1. What is SGA (refer to the Oracle memory structure-SGA)

 

SGAIt is a group of shared memory structures allocated to the system. It can contain data or control information of a database instance. If multiple users connect to the same database instanceSGAData can be shared by multiple users. When the database instance is started,SGAMemory is automatically allocated; when the database instance is closed,SGAMemory is recycled.SGAIt is the region with the largest memory usage and an important factor affecting the database performance.

 

 

 

SGAZones can be read and written. All users logging on to the instance can readSGAInOracleWhen performing the operation, the service process writes the modified informationSGA.

 

SGAIt mainly includes the following data structures:

 

Data Buffer (Buffer cache)

 

Redo log buffer (Redo log Buffer)

 

Shared Pool (Shared Pool)

 

JavaPool (Java pool)

 

Large pool (Large pool)

 

Stream pool (Streams pool --- 10gLater)

 

Data Dictionary cache (Data Dictionary Cache)

 

Other information (such as database and instance status information)

 

The SGA is the Oracle structure that is located in shared memory. it contains static data structures, locks, and data buffers. sufficient shared memory must be available to each Oracle process to address the entire SGA.

 

The maximum size of a single shared memory segment is specified by the shmmax Kernel Parameter.

 

The following table shows the recommended value for this parameter, depending on the platform:

 

 

If the size of the SGA exceeds the maximum size of a shared memory segment (shmmax or shm_max), then oracle database attempts to attach more contiguous segments to fulfill the requested SGA size. the shmseg Kernel Parameter specifies the maximum number of segments that can be attached by any process. set the following initialization parameters to control the size of the SGA:

 

Db_cache_size

 

Db_block_size

 

Java_pool_size

 

Large_pool_size

 

Log_buffers

 

Shared_pool_size

 

Alternatively, set the sga_target initialization parameter to enable automatic IC tuning of the SGA size.

 

Use caution when setting values for these parameters. When values are set too high, too much of the physical memory is already Ted to shared memory. This results in poor performance.

 

An Oracle Database configured with shared server requires a higher setting for the shared_pool_size initialization parameter, or a custom configuration that uses the large_pool_size initialization parameter. if you installed the database with Oracle universal installer, then the value of the shared_pool_size parameter is set automatically by Oracle Database Configuration assistant. however, if you created a database manually, then increase the value of the shared_pool_size parameter in the parameter file by 1 kb for each concurrent user.

 

 

2. Memory Management

 

Oracle 8i: ManualPGAManagement

Oracle 9i: AutomaticPGAMemory Management and manual shared memory management

Oracle 10g: Automatic shared memory management

Oracle 11g: Automatic Memory Management

 

SGA allocation

 

SGAThere are three ways to manage

8i:SGAThe total size is determined by the sum of all memory components and cannot be directly defined.SGASize. The modification to the internal component size takes effect only after the database is restarted.SGAStatic management.

 

9i:SGAThe total size is determined by the initialization parameters.Sga_max_sizeMake sure that the sum of the base sizes of each memory cannot exceed this parameter,SGAEach memory component can directly modify the size without restarting the database.SGA.

 

10g : SGA The size can be like 9i Dynamic management can also be implemented. SGA You only need to set initialization parameters. Sga_target ,SGA The size of each memory component can be automatically set by the database. The set data comes from the statistics automatically collected by the system. In 9i Later, SGA The size of internal components can be dynamically adjusted or automatically managed by the database. When setting the memory size, the basic unit of allocation is granularity ( Granule ) . Granule Is a continuous virtual memory, the size depends on Sga_max_size , If Sga_max_size Less 128 m , Granule Is 4 m Otherwise Granule Is16 m . The allocated size of each memory component must be Granule . Whole SGA Minimum 3 Items Granule Size.

 

9iThe rules in are as follows:

Linux/Unix

SGA <= 128 M granule 4 m

SGA> 128 M granule 16 m

 

Windows

SGA <= 128 M granule 4 m

SGA & gt; 128 M granule 8 m

 

10gThe allocation rule in is

Linux/Unix/Windows

SGA <= 1g GranuleIs4 mOtherwise16 m

 

SGAThe size of each component can be dynamically adjusted, and the total size cannot exceed the ParameterSga_max_sizeOrSga_target.

 

4. Operation example

 

SQL> select name, Bytes/1024/1024 MB, resizeable from V $ sgainfo; Name MB res -------------------------------- ---------- --- granule size 4 no matter ---------- --- SQL> show parameter sga_max_size; name type value -------------------------------------------------------------------- sga_max_size big integer 160 msql> alter system set sga_max_size = 1025 M scope = spfile; release E M altered. SQL> startup force; Oracle instance started. total system global area 1090519040 bytesfixed size 1218944 bytesvariable size 1056966272 bytesdatabase buffers 16777216 bytesredo buffers 15556608 bytesdatabase mounted. database opened. SQL> show parameter sga_max_size; Name type value -------------------------------------- ------------------------------------ sga_max_size big integer 1040 m -- all Memory component allocation size must be an integer multiple of granule, And the right 1025 is closest to 1040, so it is 1040 MB. SQL> select name, Bytes/1024/1024 MB, resizeable from V $ sgainfo; Name MB res -------------------------------- ---------- --- granule size 16 no matter ---------- --- SQL> DESC v $ sgainfo; Name null? Type ----------------------------------------- -------- ---------------------------- name varchar2 (32) bytes number resizeable varchar2 (3) SQL> select * from V $ sgainfo; name bytes res limit ---------- --- fixed SGA size 1218316 noredo buffers 2973696 nobuffer cache size 92274688 yesshared pool size 62914560 yeslarge pool size 4194304 yesjava pool size 4194304 yesstreams pool size 0 yesgranule size 4194304 nomaximum SGA size 167772160 nostartup overhead in Shared Pool 37748736 nofree SGA memory available 011 rows selected. SQL> select name, Bytes/1024/1024 MB, resizeable from V $ sgainfo; name MB res buffers ---------- --- fixed SGA size 1.16187668 noredo buffers 2.8359375 nobuffer cache size 88 yesshared pool size 60 yeslarge pool size 4 yesjava pool size 4 yesstreams pool size 0 yesgranule size 4 nomaximum SGA size160 nostartup overhead in Shared Pool 36 nofree SGA memory available 011 rows selected. SQL> show parameter sga_max_size; Name type value =-------------------------------------- sga_max_size big integer 160 msql> alter system set sga_max_size = 1025 M scope = spfile; System altered. SQL> startup force; Oracle instance started. total system global area 1090519040 bytesfixed size 1218944 bytesvariable size 1056966272 bytesdatabase buffers 16777216 bytesredo buffers 15556608 bytesdatabase mounted. database opened. SQL> show parameter sga_max_size; Name type value generation ------------- ---------------------------- sga_max_size big integer 1040 msql> select name, Bytes/1024/1024 MB, resizeable from V $ sgainfo; name MB res buffers ---------- --- fixed SGA size 1.16247559 noredo buffers 14.8359375 nobuffer cache size 16 yesshared pool size 96 yeslarge pool size 16 yesjava pool size 16 yesstreams pool size 0 yesgranule size 16 nomaximum SGA size 1040 nostartup overhead in Shared Pool 32 nofree SGA memory available88011 rows selected. SQL> show parameter shared_p; Name type value parameter ----------- export shared_pool_reserved_size big integer 4mshared_pool_size big integer 0sql> alter system set shared_pool_size = 10 m; System altered. SQL> alter system set sga_max_size = 800 m scope = spfile; System altered. SQL> startup force; Oracle instance started. total system global area 838860800 bytesfixed size 1222192 bytesvariable size 734005712 bytesdatabase buffers 100663296 bytesredo buffers 2969600 bytesdatabase mounted. database opened. SQL> alter system set shared_pool_size = 10 m; System altered. SQL> show parameter shared_p; Name type value parameter ----------- export shared_pool_reserved_size big integer 838860shared_pool_size big integer 12 msql> show parameter sga_max_size; name type value parameter ----------- ------------------------------ sga_max_size big integer 800 msql> show parameter sga_target; Name type value parameter ----------- ~sga_target big integer 160 msql> show parameter shared_pool_size; name type value parameter ----------- export shared_pool_size big integer 12 msql> show parameter large_pool_size; Name type value parameter ----------- export large_pool_size big integer 0sql> show parameter java_pool_size; name type value parameter ----------- export java_pool_size big integer 0sql> show parameter streams_pool_size; Name type value parameter ----------- export streams_pool_size big integer 0sql> show parameter db_cache_size; name type value parameter ----------- mongodb_cache_size big integer 0sql> show parameter sga_target; Name type value parameter ------------- ---------------------------- sga_target big integer 160 msql>

 

Summary

 

1. SGAIt is a group of shared memory structures allocated to the system. It can contain data or control information of a database instance.

 

2. SGAIt mainly includes the following data structures: data buffer (Buffer cache), Redo log buffer (Redo log Buffer), Sharing pool (Shared Pool),JavaPool (Java pool), Large pool (Large pool), Stream pool (Streams pool --- 10g), Data dictionary cache (Data Dictionary Cache), Other information (such as database and instance status information );

 

3.You can useV $ sgainfoViewSGARelated information.

 

My mailbox:Wgbno27@163.com Sina Weibo:@ Wentasy27Public Platform: Justoracle (No.: justoracle)It Communication Group: 336882565 (verify from csdn xxx When adding a group)Oracle discussion group: Https://groups.google.com/d/forum/justoracleBy Larry Wen

	@ wentasy blog is for your reference only, welcome to visit. I hope to criticize and correct any mistakes. If you need to repost the original blog post, please indicate the source. Thank you for the [csdn blog]