Oracle Database System Global Area (SGA)

Understanding of Oracle Database System Global Area (SGA) 1 Overview of Monitoring and Management of www.2cto.com Oracle SGA is an important part of routine database maintenance. This article describes the basic concepts of SGA, the SGA running status check, and the parameter setting principles of SGA. 2. Basic concepts of SGA when Oracle database is started, the system first plans a fixed area in the memory to store the data required by the user and the system information required during Oracle operation. This region is called the System Global Area (SGA. Www.2cto.com SGA contains several important regions: ■ Database Buffer Cache (Database Buffer) ■ Redo Log Buffer (Redo Log Buffer) ■ Shared Pool (Shared zone) ■ others, for example, the Java pool and Large pool 2.1 Database Buffer Cache (Database Buffer) are used to Cache data blocks read from the Database in the memory. The larger the database buffer, the larger the memory provided for shared data in the memory, which can reduce the required Disk Physical reads. Before 9i, the size of the database buffer is determined by db_block_buffers * db_block_size. The db_block_size parameter is set when the database is created. The db_block_size of the oltp system is generally set to 8 k. In 9i, the database buffer size is determined by db_cache_size, and db_block_buffers in 8i is canceled. The unit of db_cache_size is byte, which directly determines the size of the database buffer, instead of the number of blocks. 2.2 The Shared Pool (Shared area) consists of three parts: Dictionary cache (including definition of data Dictionary, such as table structure and permissions ), library cache (including shared SQL cursor, original SQL code, execution plan, stored procedure, and session information) and Control structure. Its size is controlled by the initialization parameter shared_pool_size. Its function is to cache the parsed SQL statements so that they can be reused and no longer need to be parsed. SQL parsing consumes a lot of CPU resources. If an SQL statement already exists in the Shared pool, it performs soft parsing only (find the same SQL statement in the Shared pool ), this will greatly improve the database operation efficiency. Of course, the larger the memory, the better, the larger the Shared pool, oracle will pay more management overhead to maintain the Shared structure. It is recommended that the value range be between 150M-500M. If the system memory is 1G, this value can be set to 150 M-200 M; if it is 2G, this value is set to 250 M-300 M; each increase of 1G memory, this value is increased by 100 M, but the maximum value of this value should not exceed 500 M. 2.3 Redo Log Buffer (Redo Log Buffer) Log_buffer is the Redo Log Buffer. Any changes to the database are recorded in this Buffer in order, and then written to the disk by the LGWR process. The write conditions for LGWR are: the SCN numbers of data submitted by the user, 1/3 redo log buffers not written to the disk, more than 1 MB of redo log buffers not written to the disk, timeout, and DBWR data to be written are greater than the SCN numbers recorded by LGWR, DBWR triggers LGWR writing. It can be seen that the value of log buffer greater than 1 MB is not significant. The size of the redo log buffer is set by the initialization parameter log_buffer. 2.4 Large pool and Java pool Large pool: used for MTS, parallel queries, and RMAN. If MTS or RMAN is used, large_pool is particularly useful, which can reduce user contention for the share pool. If the application system does not use MTS or RMAN, The large_pool_size can be set to 1 MB. Java pool: After Oracle8I, oracle database has built-in support for java. If the JServer component is selected during database installation, this value can be set to 20-30 m. You can query the v $ option dynamic view. If the value of java is true, JServer is installed, false, and not installed. If the application system does not use the PORTAL, APPS, or RMAN, the Java pool value can be set to 1 MB. If the system needs to install patches, we recommend that you temporarily increase the Java pool value to 50-150 MB. 3 SGA runtime check 3.1 SGA overall check for the SGA part, we query through sqlplus: SQL> show sga Total System Global Area 316899848 bytes Fixed Size 741896 bytes Variable Size 150994944 bytes Database Buffers 163840000 bytes Redo Buffers 1323008 bytes or SQL> select * from v $ sga; name value -------------------- ---------- Fixed Size 741896 Variable Size 150994944 Database Buffers 163840000 Redo Buffers 1323008 Show sga result and SGA initialization parameter configuration The configuration is not exactly one-to-one. The specific explanation of each part is as follows: different platforms of Fixed Size oracle may be different from those of different versions, but it is a Fixed value for determining the environment, it stores the information of various SGA components and can be seen as a guide to build the SGA region. Variable Size contains memory settings such as shared_pool_size, java_pool_size, large_pool_size, and hash table and block header information used to manage memory structures such as data buffers. Database Buffers refers to the data buffer zone, which contains the default pool, buffer_pool_keep, and buffer_pool_recycle memory in 8i. 9i contains db_cache_size, db_keep_cache_size, db_recycle_cache_size, and db_nk_cache_size. Note that the total memory of the three parts in 8i is db_block_buffers * db_block_size. Redo Buffers indicates the actual size of the Redo log buffer, which may be slightly different from the value of log_buffer. 3.2 Data buffer hit rate check statement: SQL> select 1-(phy. value/(cur. value + con. value) "hit ratio" from v $ sysstat cur, v $ sysstat con, v $ sysstat phy where cur. name = 'db block gets' and con. name = 'consistent gets' and phy. name = 'physical reads'; adjustment principle: the hit rate should not be lower than 90%. if the value is too low, you should increase the initialization parameter. 8 I adjusted db_block_buffer and 9i adjusted db_cache_size. 3.3 Dictionary cache Hit rate check statement: SQL> SELECT 1-(SUM (getmisses)/SUM (gets) "Data Dictionary Hit Ratio" FROM v $ rowcache; Adjustment Principle: the hit rate should not be lower than 95%. if the value is too low, increase the size of shared_pool_size. 3.4 Library cache Hit rate check statement: SQL> SELECT 1-(SUM (reloads)/SUM (pins) "Library cache Hit Ratio" FROM v $ librarycache; Adjustment Principle: the hit rate should not be lower than 99%. if the value is too low, increase the size of shared_pool_size. 3.5 check the Log buffer hit rate statement: SQL> select (req. value * 5000)/entries. value "Ratio" from v $ sysstat req, v $ sysstat entries where req. name = 'redo log space requests' and entries. name = 'redo entries'; adjustment principle: the hit rate should not be higher than 1. if the value is too high, increase the log_buffer size. 4. When the SGA Parameter Setting principle is Oracle8i, the size of the SGA is set by some parameters in the initialization parameter file, the most troublesome thing is that after each parameter adjustment, it takes effect only after the database is reactivated. DBAs can dynamically configure the memory size from Oracle9i. This technology is called "dynamicSGA 」. With dynamic SGA, various SGA components can be dynamically planned and adjusted without shutting down the database first. In Oracle9i, SGA_MAX_SIZE can set the maximum memory space occupied by SGA. Note that SGA_MAX_SIZE should not exceed the physical memory size as much as possible. Otherwise, it will be used to the virtual memory on the hard disk, leading to poor performance. If the value of SGA_MAX_SIZE is smaller than the sum of the values set by other SGA-related parameters or smaller than the sum of the default values of each related parameter, the value of SGA_MAX_SIZE is invalid. In the basic concept section of SGA, we have introduced the size setting principles for each part. Here we will use a single sentence to sum up: 1) We recommend that the database buffer occupy 20%-30% of the physical memory. Physical memory can be less than 1 GB, accounting for 20%, or 25% between 1 GB and 2 GB. Physical memory can be more than 2 GB, accounting for 30%, or even higher. 2) It is recommended that the shared area be between 150M-500M. If the system memory is 1G, this value can be set to 150 M-200 M; if it is 2G, this value is set to 250 M-300 M; each increase of 1G memory, this value is increased by 100 M, but the maximum value of this value should not exceed 500 M. 3) We recommend that you set the redo log buffer to 1 MB. 4) if the application system does not use MTS or RMAN, the Large pool size can be set to 1 MB. Otherwise, set it to 16 Mb. 5) if the application system does not use RMAN, the Java pool value can be set to 1 MB. Otherwise, set it to 50 MB. If the system needs to install patches, we recommend that you temporarily increase the Java pool value to 50-150 MB. After the above parameters are adjusted, the size of the SGA must be calculated. The general principle is that the size of the SGA should be less than half of the physical memory. The SGA calculation method is as follows: SGA = DB_BLOCK_BUFFERS * DB_BLOCK_SIZE + SHARED_POOL_SIZE + LOG_BUFFER. At the same time, make sure that (SGA + sort_area_size * sesson quantity) is not greater than 70% of the physical memory.