Performance Optimization Technology in the Oracle database runtime stage

In the previous article, we introduced the design stage and the running stage of the Oracle database in the performance optimization technology of the actual Oracle database operations, the following article describes how to adjust the database during the database operation stage. Hope you will have some gains.

Adjust the database during database operation

In the database operation phase, you can adjust the database in two aspects: operating system level adjustment and database level adjustment.

1. OS-level adjustment

The main purpose of implementing OS-level adjustments is to reduce memory swap and paging so that the SGA (System Globle Area) can be stored in memory.

2. Reduce memory Switching

Memory swap swapping) may cause a large memory overhead, it should be minimized. For Oracle databases running on Solaris Unix operating systems, you can use vmstat or sar commands to check and exchange the system-level memory and hard disk I/O usage, adjust parameters such as the size of the unix data buffer pool and the memory size that each process can use.

Vmstat command

It reports the activity of processes, virtual memory, disks, pages, and CPUs on Solaris. The following command will display the summary of what the system does every five seconds:
% Vmstat 5

Sar command

Monitors swap, paging, disk, and CPU activity. The following command is used to display the summary of 10 paging activities every 10 seconds:
% Sar-p 10 10

If the system memory exchange is large and memory saving is required, the following measures can be taken:

1) Avoid running unnecessary system daemon processes or application processes;

2) Reduce the number of database buffers without significantly affecting the data block hit rate to release some memory;

3) Reduce the number of UNIX file buffers, especially when using bare devices ).

3 Control pagination

A small amount of memory paging does not significantly affect the system performance, because applications do not have to be fully stored in the memory. However, too many pages will cause the system performance to decline. To detect too many pages, you can run the measurement during fast response or idle time and compare it with the measurement during slow response. You can solve this problem by using the following methods:

Use vmstat or sar-p monitoring pagination;

Install more memory;

Move some work to another system;

Configure the system core to use less memory;

Keep the SGA in a single shared memory segment.

4. Keep SGA (System Globle Area) in memory

SGA is a global system area for fast access to database data. If SGA needs to be released and allocated frequently, it is impossible to quickly access data. Therefore, the SGA memory is required to reside. At this time, we can reconfigure the UNIX core and adjust some operating system parameters to increase the shared memory.

5. Database-level adjustment

Each Oracle instance is composed of a group of Oracle background processes and a memory zone of SGA. This group of background processes will automatically read and write database data files. Therefore, database performance can be affected by these factors: whether the distribution of various SGA parts is reasonable and whether the use efficiency is normal; whether there are many I/O and lock competitions.

6 SGA allocation and Efficiency

The memory allocated to each instance, that is, the efficiency of SGA, will greatly affect the performance of the database system. SGA consists of the following parts: Shared Pool, data block buffer, redo log buffer, and large pool.

L Shared pool)

Shared Pool Storage Library cache storage shared SQL and PL/SQL zone) and data dictionary cache database object information) and session period information for MTS ). Because the information is frequently accessed by applications, the information must maintain a high hit rate. You can use the following statement to confirm the data hit rate in the Shared Pool:

Library cache:

Select gethitratio from v $ librarycache should be greater than 90%
Select sum (reloads)/sum (pins) from v $ librarycache must be less than 1%

Data Dictionary cache:

Select sum (getmisses)/sum (gets) from v $ rowcache must be less than 15%

Due to the uneven level of the program designer, there may be large anonymous blocks, which will lead to SQL not being reused. Therefore, you need to find a large anonymous block and convert it into a stored procedure for reuse:

 
 

  
  
select * from v$sqlarea where command_type=47 and length(sql_text)>500 
 
 

For SQL objects that are frequently used by some application systems, such as stored procedures, functions, and packages, you can pin them in the memory to prevent the shared pool from being removed because it is too small:

 
 

  
  
Exec dbms_shared_pool.keep (Object Name)
 
 

Data block buffer Db block buffer ):

The data block buffer zone stores the data block content of data files frequently accessed by users and the data content modified by users. The database reads the content in the data file into the memory and reads it directly from the memory the next time it needs to, thus reducing disk I/O and response time. Of course, generally only small data tables, such as common code tables, are cached in the memory.

Since it is impossible to store all data in the data fast buffer, you can use the LRU algorithm to determine which data blocks to remove, but try to ensure a high data hit rate.

The SQL statement for viewing the data block hit rate is:

 
 

  
  
select 1-(phy.value/(cur.value+con.value)) 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 gets' 
 
 

If the hit rate is less than 0.85, You need to allocate more memory for the data block buffer.

Redo Log buffer ):

Redo log buffer stores the redo entries of each DML or DDL statement copied from the user memory area. If the buffer allocation is too small, there is not enough space for redo entries to wait.

7. I/O and Resource Competition

Since many processes need to write data files, I/O adjustment is required to solve the I/O bottleneck problem. If the rational allocation of tablespaces is effectively considered in the design phase, I/O competition can be effectively reduced to a certain extent. During database operation, due to the dynamic growth of data, the space originally allocated to tables or Indexes has been used up, and Oracle will automatically allocate space to these database objects. This dynamic allocation will affect the system performance, so the requirements are as follows:

Avoid dynamic space management

Localized Management of tablespaces to reduce disk competition with data dictionary tablespaces.

In the system design and test run phase, SQL statements with relatively small data volume and low efficiency may not affect the system response time. However, when the system data volume increases to a certain extent, when the system is running, you need to monitor and find out which sqls cannot use indexes effectively or lack indexes, and make corresponding adjustments: create indexes and modify the SQL statement syntax.

In addition, some mechanisms are required in Oracle to ensure the stability and Data Consistency of database objects during use, such as latch and lock. Therefore, competing resources related to these mechanisms will affect the database performance. To reduce resource competition, you can adjust database initialization parameters such as db_block_lru_latches and dml_locks to reduce resource contention and optimize database performance.

Some common performance optimization methods and tools

The Oracle Database System provides some tools and scripts to obtain database performance indicators and optimization methods. For example, use utlbstat. SQL and utlestat. the SQL script obtains the memory and disk I/O of the database for a period of time. It uses the dynamic performance view and data dictionary view to obtain information such as the hit rate and system wait events. Of course, you can also use the Oracle Enterprise Manager graphical tool for monitoring.

Conclusion

The performance adjustment of Oracle databases is very important, but it is also difficult. The database administrator needs to make full use of the rules described above. When creating a database, the database administrator can reasonably design the table space allocation, storage parameters, and memory usage initialization parameters as needed, it will be of great benefit to the database performance in the future. Only by carefully analyzing various performance problems arising during Oracle operation can the Oracle database be operated efficiently and reliably. It should also be pointed out that the performance adjustment of the database is a system project, which involves many aspects. It cannot be determined based on the condition of only one time point to determine the good or bad performance of the database. How to effectively adjust the database administrator needs to go through the repeat process. These require accumulating experience in a large amount of practical work, so as to better tune the database.