Principles and methods for adjusting the performance of Oracle Application Systems (1)

Oracle databases are widely used in various fields of society, especially in the Client/Server mode. However, application developers often encounter the problem that the performance of the entire system decreases significantly as the data volume increases, to solve this problem, we need to adjust the entire system from the following aspects: database servers, network I/O, and applications, so as to make full use of the performance of Oracle and improve the performance of the entire system.

1. Adjust the performance of the Database Server

The Oracle database server is the core of the entire system. Its performance directly affects the performance of the entire system. To adjust the performance of the Oracle database server, we mainly consider the following aspects:

1.1 adjust the operating system to suit the running of Oracle Database servers

The Oracle Database Server depends largely on the operating system of the running server. If the operating system cannot provide the best performance, the Oracle database server cannot achieve its expected performance no matter how it is adjusted.

1.1.1. Plan System Resources for the Oracle Database Server

Based on the available resources of existing computers, the principle of planning to allocate resources to Oracle servers is to maximize the use of resources on Oracle servers as much as possible, especially in the Client/Server, try to allow all resources on the Server to run the Oracle service.

1.1.2 adjust the memory configuration in the Computer System

Most operating systems use virtual storage to simulate larger memory on a computer. It is actually a certain disk space on the hard disk. When the actual memory space cannot meet the requirements of the application software, the operating system will use this part of the disk space to replace the page information in the memory, this will cause a large number of disk I/O operations, reducing the performance of the entire server. To avoid excessive use of virtual memory, increase the computer memory.

1.1.3 set the operating system process priority for the Oracle Database Server

Do not adjust the priority of Oracle processes in the operating system, because in the Oracle database system, all backend and front-end database server processes perform equally important tasks and require the same priority. Therefore, during installation, all database server processes run with the default priority.

1.2 Adjust Memory Allocation

The Oracle Database Server retains three basic memory caches, corresponding to three different types of data: database cache, Dictionary cache, and buffer cache. The database cache and dictionary cache form a shared pool. The shared pool and the buffer cache form the system full-Process Zone (SGA ). SGA is a full-Process System zone for quick access to database data. If SGA needs to be released and allocated frequently, it cannot achieve quick access to data, therefore, the SGA should be placed in the primary memory instead of in the virtual memory. Memory adjustment mainly refers to adjusting the size of the memory structure consisting of SGA to improve system performance. Because the memory structure requirements of the Oracle database server are closely related to applications, therefore, the memory structure should be adjusted before the disk I/O adjustment.

1.2.1 Library Buffer Adjustment

The database buffer contains private and shared SQL and PL/SQL zones. the hit rate of the database buffer is determined by comparing the hit rate. To adjust the database buffer, you must first understand the activity of the database buffer. The activity statistics of the database buffer are kept in the Dynamic Performance Table v $ librarycache data dictionary, you can query the table to learn about its activities and determine how to adjust it.

Select sum(pins),sum(reloads) from v$librarycache;

The Pins column provides SQL statements, PL/SQL blocks, and the total number of times the accessed object is defined; the Reloads column provides implicit Analysis of SQL and PL/SQL blocks or errors that occur in the Library Buffer during object definition reloading. If sum (pins)/sum (reloads) ≈ 0, the hit rate of the database buffer is appropriate. If sum (pins)/sum (reloads)> 1, you need to adjust the initialization parameter shared_pool_size to re-adjust the amount of memory allocated to the shared pool.

1.2.2 adjustment of Data Dictionary Buffer

The Data Dictionary Buffer contains the structure, user, and entity information about the database. The data dictionary hit rate has a significant impact on system performance. The usage of the Data Dictionary Buffer is recorded in the Dynamic Performance Table v $ librarycache. You can query this table to learn about its activity and determine how to adjust it.

Select sum(gets),sum(getmisses) from v$rowcache;

The Gets column is the statistics on the number of requests for the corresponding item; The Getmisses column is the number of requests for the data that causes a buffer error. For frequently accessed data dictionary buffers, sum (getmisses)/sum (gets) <10% ~ 15%. If the percentage is greater than this, you should consider increasing the capacity of the Data Dictionary Buffer, that is, you need to adjust the initialization parameter shared_pool_size to re-adjust the amount of memory allocated to the shared pool.

1.2.3 adjustment of buffer cache speed

All data accessed by a user process is accessed through the buffer cache. Therefore, the hit rate of this part is crucial to performance. The usage of the buffer cache is recorded in the Dynamic Performance Table v $ sysstat. You can query this table to learn about its activity and determine how to adjust it.

Select name,value from v$sysstat where name in ('dbblock gets','consistent gets','physical reads');

The values of dbblock gets and consistent gets are the total number of reads in the request data buffer. Physical reads is the number of times files are read from the disk when data is requested. The possibility of reading from the buffer cache is called the buffer hit rate. formula:

Hit Ratio=1-(physical reds/(dbblock gets+consistent gets))

If the Hit Ratio is <60% ~ 70%, the parameter value of db_block_buffers should be increased. 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 allocated to the buffer cache. The total number of bytes in the buffer cache = the value of db_block_buffers * db_block_size. The value of db_block_size indicates the number of bytes of the data block size. You can query the v $ parameter table:

select name,value from v$parameter where name='db_block_size';

After modifying the initialization parameters of the above database, you must first shut down the database and restart the database to make the new settings take effect.