Understanding Oracle database System Global Area (SGA)

　1 Overview

The monitoring and management of the Oracle SGA area 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 you start an Oracle database, the system first plans a fixed area in the memory to store the user's required data and the required system information during Oracle operation. This region is called the System Global Area (SGA.

SGA contains several important areas:

■ Database Buffer Cache (Database Buffer)

■ Redo Log Buffer (Redo Log Buffer)

■ Shared Pool (Shared area)

■ Others, such as Java pool and Large pool

2.1 Database Buffer Cache (Database Buffer)

The role of the database buffer is 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 Shared Pool (Shared area)

A shared area consists of three parts: Dictionary cache (including definition of data Dictionary, such as table structure and permissions) and 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, only soft Parsing is performed (
Pool to find the same SQL), which will greatly improve the database operation efficiency. Of course, the larger the memory, the better, the larger the shared
In order to maintain the shared structure, the database pool and Oracle have higher management costs. It is recommended that the value range be between 150m-500m. If the system memory is 1 GB, this value can be set to 150 m-200 m; for example:
If the value is 2 GB, the value is set to 250 m-300 m. The value increases by 100 m for every 1 GB of memory increase, but the maximum 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
The process writes it to the disk. The lgwr write condition is: the user submits, 1/3 of the redo log buffer is not written to the disk, and more than 1 m
The SCN Number of the data to be written to the redo log buffer is greater than the scn number of the lgwr record, and 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 query, 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 mb-
Between 30 mb. 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 running status check

3.1 SGA overall check

For the SGA part, we use sqlplus to query:

　　

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

The result of show SGA does not exactly correspond to the initialization parameter configuration of SGA. The following is a detailed explanation of each part:

　　Fixed size

Different Oracle platforms and versions may be different, but it is a fixed value for determining the environment. It stores information about each part of the SGA component, which can be seen as a region for guiding the establishment of SGA.

　Variable Size

Including shared_pool_size, java_pool_size, large_pool_size, and other memory settings and hash table and block header information used to manage memory structures such as data buffers.

Database Buffers

Exponential data buffer, which contains default in 8i
Pool, buffer_pool_keep, and buffer_pool_recycle memory. In 9i, db_cache_size,
Db_keep_cache_size, db_recycle_cache_size, and db_nk_cache_size. Pay attention to the three-part memory in 8i
The sum is db_block_buffers * db_block_size.

　　Redo Buffers

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

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 principles:

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

Check statement:

　　

SQL> select 1-(sum (getmisses)/sum (gets) "data dictionary hit ratio" from V $ rowcache;

Adjustment principles:

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

Check statement:

　　

SQL> select 1-(sum (reloads)/sum (PINs) "library cache hit ratio" from V $ librarycache;

Adjustment principles:

The hit rate should not be lower than 99%.

If the value is too low, increase the size of shared_pool_size.

3.5 log buffer hit rate check

Check 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 principles:

The hit rate should not be higher than 1.

If the value is too high, increase the log_buffer size.

　　4. SGA parameter setting principles

At Oracle8i, SGA
The size of the parameter 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 re-activated. Starting from Oracle9i, DBA
The memory size can be dynamically configured. This technology is called "dynamicsga 」. With dynamic SGA
, SGA can be dynamically planned and adjusted in all its constituent regions without shutting down the database first.

In Oracle9i, SGA_MAX_SIZE can set the maximum memory space occupied by SGA. Note: SGA_MAX_SIZE
Try not to exceed the physical memory size. Otherwise, the virtual memory on the hard disk will be used, leading to poor performance. If the value of SGA_MAX_SIZE is smaller than that of other SGA
If the sum of the values set by the relevant parameters is smaller than the sum of the default values of the relevant parameters, the SGA_MAX_SIZE value is invalid.

In the basic concept section of SGA, we have introduced the size setting principles of each part. Here we will summarize the following with one sentence:

1) it is recommended that the database buffer accounts for 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:
Below: 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 no greater than 70% of the physical memory.