How to adjust the performance of an Oracle 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. Adjusting the operating system is suitable for the Oracle database server to run the Oracle database server, which depends largely on the operating system of the running server. If the operating system cannot provide the best performance, no matter how you adjust it, oracle Database servers cannot achieve their expected performance.

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.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.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.

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.

2.1 adjust the database Buffer

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.

2.2 adjust the 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 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.

2.3 adjust the cache speed of the buffer

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 hit ratio <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.

3. Adjust disk I/O

The disk I/O speed has an important impact on the entire system. The disk I/O problem can be solved to significantly improve the performance. The main reasons that affect disk I/O performance include disk competition, excessive I/O times, and allocation and management of data block space.

When creating a new file for the Oracle database server, whether it is the data file used by the tablespace or the log file used for data transaction login, the available disk resources on the database server should be carefully considered. If multiple disks exist on the server, files can be distributed and stored on each available disk to reduce competition for database data files and transaction log files, this effectively improves the server performance. For different application systems with their own datasets, you should create different tablespaces to store the data of their respective application systems, and store the data files corresponding to the tablespace on different disks as much as possible, this method of physically storing the tablespace of each application system can eliminate the possibility of two application systems competing for disks. Data Files and transaction log files are stored on different disks, so that disk access to transaction processing does not impede disk access to the corresponding transaction log registration. If multiple disks are available, placing two transaction log members on different disk drives can eliminate the possible disk competition caused by log files. Table data and index data of an application should be stored in different tablespaces, and different types of tablespaces should be stored on different disks as far as possible, this eliminates the disk competition between table data and index data.

4. Adjust the rollback segment of the Database Server

A rollback segment is a storage region in which the database uses the original data values of rows updated or deleted by a transaction. If you want to roll back the changes made by a transaction, the database reads the data before the change from the rollback segment and changes the rows affected by the transaction to their original state. Rollback segments control the database's ability to process transactions, and thus play a key role in the database's success. No matter how well the rest of the database is designed, if it is not properly designed, it will seriously affect the system performance. The principles for creating and adjusting rollback segments are as follows.

4.1 separate rollback segments

A separate rollback segment is to create more than one tablespace for the rollback segment separately, so that the rollback segment is separated from the data dictionary, user data, and index. Since the write of the rollback segment is parallel to the write of data and indexes, separating it can reduce I/O contention. If the rollback segment is not separated from the data, if a tablespace is to be taken offline or undo, The tablespace cannot be taken offline or undo before all rollback segments in the tablespace are taken offline. Once the tablespace is unavailable, all rollback segments in the tablespace cannot be used, which wastes all allocated disk space. Therefore, independent rollback segments make database management easier. The frequent contraction of rollback segments makes it easier for free blocks in the tablespace to form fragments. Separating rollback segments can reduce the fragmentation of the tablespace in the database.

4.2 create rollback segments of different sizes

For some online transaction processing, they often modify a small amount of data frequently. It is advantageous to create many small rollback segments. The entry item of each thing can only be limited to one rollback segment. The rollback segment should be sufficiently large to accommodate a complete transaction. Therefore, a larger rollback segment is required for some large objects. A large amount of rollback information is generated when a transaction is processed offline. An extra large rollback segment is required for processing. Based on these theories, the Oracle database server creates three groups for the preceding three types of transaction processing: small transaction groups, large transaction groups, and large transaction groups with different sizes, in addition, it is dispersed to three different tablespaces, and the group size is the same, it should meet the maximum requirements for processing the group of things.