Oracle architecture and backup (5) sga

1. What is SGA (refer to Oracle memory structure-SGA) SGA is a group of shared memory structures allocated to the system, which can contain data or control information of a database instance. If multiple users connect to the same database instance, data in the instance's SGA can be shared by multiple users. When the database instance is started, the SGA memory is automatically allocated;

1. What is SGA (refer to Oracle memory structure-SGA) SGA is a group of shared memory structures allocated to the system, which can contain data or control information of a database instance. If multiple users connect to the same database instance, data in the instance's SGA can be shared by multiple users. When the database instance is started, the SGA memory is automatically allocated;

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

SGA 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 instance, data in the instance's SGA can be shared by multiple users. When the database instance is started, the SGA memory is automatically allocated. When the database instance is closed, the SGA memory is recycled. SGA is the region with the largest memory usage and an important factor affecting database performance.

The SGA area can be read and written. All users logging on to the instance can read the information in SGA. When oracle performs an operation, the service process writes the modified information to SGA.

SGA mainly includes the following data structures:

Buffer Cache)

Redo Log Buffer)

Shared Pool)

Java Pool)

Large Pool)

Stream Pool (Streams Pool --- 10 Gb later)

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: manual PGA Management

Oracle 9i: Automatic PGA memory management and manual shared memory management

Oracle 10g: Automatic Memory Sharing Management

Oracle 11g: Automatic Memory Management

SGA allocation

Three other SGA management methods

8i: the total size of SGA is determined by the sum of all memory components. The size of SGA cannot be defined directly. Modifications to the size of internal components must take effect after the database is restarted. This is called static management of SGA.

9i: the total size of SGA is determined by the initialization parameter SGA_MAX_SIZE. The sum of the base size of each memory cannot exceed this parameter, each memory component of SGA can directly modify the size without restarting the database, so it is called dynamic management of SGA.

10g: The size of SGA can be dynamically managed like 9i, or automatically managed as SGA. You only need to set the initialization parameter SGA_TARGET. The size of each memory component of SGA can be automatically set by the database, the set data comes from the statistics automatically collected by the system. After 9i, the size of the internal components of SGA can be dynamically adjusted or automatically managed by the database. When setting the memory size, the basic unit of allocation is granule ). granule is a continuous virtual memory. The size depends on the size of SGA_MAX_SIZE. If SGA_MAX_SIZE is smaller than 128 M, Granule is 4 M; otherwise, Granule is 16 M. The size of each memory component must be an integral multiple of Granule. The size of the entire SGA is no less than 3 Granule.

9i rules 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

The allocation rule for 10 Gb is

Linux/Unix/Windows

SGA <= 1G granule is 4 M, otherwise it is 16 M

The size of each component of SGA can be dynamically adjusted. The total size cannot exceed the size of the SGA_MAX_SIZE parameter or SGA_TARGET parameter.

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. SGA is a group of shared memory structures allocated to the system. It can contain data or control information of a database instance.

2. SGA mainly includes the following data structures: Buffer Cache, Redo Log Buffer, Shared Pool, and Java Pool), Large Pool (Large Pool), stream Pool (Streams Pool --- 10 Gb later), Data Dictionary Cache, and other information (such as database and instance status information );

3. You can view the sga information in the v $ sgainfo view.

My mailbox: wgbno27@163.com Sina Weibo: @ Wentasy27 public platform: JustOracle (No.: justoracle) IT exchange group: 336882565 (when adding the group verification From csdn xxx) Oracle Exchange discussion group: https://groups.google.com/d/forum/justoracleBy Larry Wen

	@ Wentasy