Oracle Memory parameter tuning settings

The parameters of the tuning function in the Oracle database system are called initialization parameters, and the database administrator needs to properly adjust these initialization parameters to optimize the Oracle system according to the actual situation.

1 introduction of main system parameter tuning

　　

2 allocation of system memory parameters

2.1 Oracle Instance = memory Structure + process structure

Memory structure = SGA + PGA

SGA (System global Zone): the memory area where the user stores database information, which is shared by the database process. It contains the data and control information of the server, mainly including high-speed data buffer, shared pool, redo log buffer, Java pool, large pool and other memory structures.

The SGA setting, theoretically the size of the SGA should account for the 1/3-1/2 of the OS memory.

SGA + PGA + OS memory used < total physical memory

View information about the current system SGA

Select name,bytes/1024/1024 as "Size (M)"
From V$sgainfo;

According to the query information shows that there is currently 148M of available SGA memory, the system's current memory configuration is not optimal, we can actually use the process as appropriate to reallocate memory.

Setting principles for 2.2 SGA

SGA = (Db_block_buffers * db_block_size) + (shared_pool_size + large_pool_size + java_pool_size + log_buffers)

(1) Buffer cache (Database_buffer_cache): primarily stores data written by disk data files

Size: Db_block_buffers * db_block_size

Allocation principle: Buffer cache adjustment, the user process access to all data is through the buffer cache to access, so that part of the hit rate, is critical to performance. The usage of the buffer cache is recorded in the Dynamic performance table V$sysstat, which can be queried to determine how to adjust the activity of the table.

Select Name,value from V$sysstat where name in (' Dbblock gets ', ' consistent gets ', ' physical reads ');
The value of Dbblock gets and consistent is the total number of reads in the request data buffer. The value of physical reads is the number of times a file is read from the disk when the data is requested. The probability of reading from the buffer cache is called the buffer hit rate, and the formula is calculated:

Hit ratio=1-(physical reds/(Dbblock gets+consistent gets)),

Select (phys.value/(Cur.value + con.value)) "Hit RATIO"
From V$sysstat cur, v$sysstat con, V$sysstat phys
where Cur.name = ' db block gets '
and con.name = ' consistent gets '
and phys.name = ' physical reads ';

If hit ratio<60%~70%, you should increase the Db_block_buffers parameter value. Db_block_buffers can adjust the amount of memory allocated to the buffer cache, that is, db_block_buffers can set the number of data blocks that allocate the buffer cache. The total number of bytes in the buffer cache =db_block_buffers the value of the value *db_block_size. The value of Db_block_size represents the number of bytes of data block size, and the V$parameter table can be queried:

Select Name,value
From V$parameter
where name= ' db_block_size ';

After you modify the initialization parameters of the above database, you must close the database before you restart the database for the new settings to work.

(2) Shared pool (shared_pool_size)

Allocation principle: In the shared pool, there are mainly two caches, the library cache and the data Dictionary cache, but they cannot be adjusted individually, only by adjusting the shared_pool_size to proceed.

The Library cache is used to host shared SQL statements and PL/SQL statements and is managed using the LRU (Least recently used) algorithm, which Oracle can use with SQL statements already in the cache without the need for re-parsing. We can query the library cache hit Ratio using the following SQL statement:

Select Gethitratio
From V$librarycache
where namespace = ' SQL area ';

If the result is less than 90%, then the ratio is not high, you need to increase the library cache.

The data Dictionary cache is adjusted to include the structure, user, and entity information about the database. The hit rate of the data dictionary has great impact on the system performance. The usage of the data dictionary buffer is recorded in the Dynamic performance table V$librarycache, which can be queried to determine how to adjust by querying the table for its activity.
Select sum (gets), sum (getmisses)

From V$rowcache;

The gets column is the count of the number of requests for the corresponding item, and the Getmisses column is the number of requests for data that caused the buffer to fail. For frequently accessed data dictionary buffers, sum (getmisses)/sum (gets) <10%. If larger than this percentage, you should consider increasing the capacity of the data dictionary buffer, which is to adjust the initialization parameter shared_pool_size to readjust the amount of memory allocated to the shared pool.

　　

(3) candidates (large_pool_size): Mainly used for database backup manager Ram

Principle: According to the actual situation

(4) Java Pool (java_pool_size): Primarily for Java language development

Principle: According to the actual situation

(5) Log buffer (Log_buffers): Store data modification information

Principle: According to the actual situation

2.3 PGA Program Global Zone

The PGA contains data and control information for a single server process or a single background process, which, contrary to the SGA shared by several processes, is a zone that is used only by one process, and the PGA is reclaimed when the process is created to allocate the terminating process.

(1) Sort_area_size the memory of the user sort

(2) Hash_area_size user hash join, bitmap index

Both of these parameters are PGA in non-MTS mode and are assigned separately for each session, and in addition to the OS + SGA on our server, this two-part size setting must be considered

OS + SGA + number of concurrent execution processes * (sort_area_size + hash_area_size) < 0.7 * Total physical memory of OS

3 instance Configuration

(1) Physical memory size

(2) memory required by the operating system and other applications

(3) Whether the database system uses a file device or a bare device

(4) How many concurrent connections are available

(5) Whether the application is OLTP or OLAP type

The basic distribution principle, db_block_buffers as large as possible, shared_pool_size moderate, log_buffer usually hundreds of KB to 1M on it.

1G memory, 1 CPUs, Db_block_size 8192B

SGA = 1024 * 0.5 = 512--The maximum is typically about half of the OS memory, not more than 60%

(1) databse buffer cache:512 * 40% = 205M Db_block_buffer should be set to: 52352 (409*1024*1024/8192)--Generally sga_max_size 40%

(2) shared_pool_size:563* 40% = 205M--Generally 40% of sga_max_size

(3) log_buffer:128k (number of 128K*CPU)

(4) java_pool_size:4m

(5) large_pool_size:4m

(6) Sort_area_size: According to the actual situation 65k-2m

(7) Sort_area_retained_size: According to the actual situation

Oracle Memory parameter tuning settings