Summary of methods to improve the performance of Oracle Database Systems

Introduction: Oracle DatabaseIt is widely used in various fields of society, especially in the Client/Server mode. However, application developers often encounterSystem PerformanceAs the data volume increases significantly, to solve this problem, the entire system is adjusted from the following aspects: database servers, network I/O, and applications, make full use of the performance of Oracle, improve the performance of the entire system, and make Oracle database better serve users. The following describes the detailed solutions.

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

The operating system is suitable for 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 database 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.

1.2.2 Data Dictionary Buffer Adjustment

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.

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.

2. Adjust network I/O in Client/Server mode

Application Processing in the Client/Server environment is distributed between customer applications and database service programs. In the Client/Server environment, the network I/O between the Client and the Server is the bottleneck for improving the performance of the entire system. The less network I/O a customer application causes, the better the performance of applications and the entire system. The most important principle for reducing network I/O is to concentrate application logic on database servers.

2.1 full restrictions on Oracle databases

When creating a table for an application, appropriate integrity constraints should be imposed on some data with special requirements, so that the database itself rather than the application can meet certain conditions. The execution of complete constraints on the database server is optimized in a system mechanism lower than the SQL statement level. It is independent of the client and only runs on the server, you do not need to pass SQL statements between the Client and the Server to effectively reduce the network I/O burden.

2.2 use database Triggers

The complete constraint can only implement some simple data constraints, and there is nothing to do with some complicated transaction processing rules. In this case, it is best not to implement complex program control in applications, instead, database triggers should be used to implement complex transaction rules. Database Triggers can be implemented by the database itself, rather than applications, to constrain data to comply with complex transaction processing rules, and are easy to create, easy to manage, and avoid a large number of network I/O.

2.3 use stored procedures, stored functions, and packages

Oracle stored procedures and stored functions are named collections of PL/SQL functions that can complete certain functions and are stored on the Server. Package is a method that encapsulates related processes and function organizations into a database program unit. These SQL commands are stored on the Server side relative to the process and function of the application. Using Stored Procedures and stored functions, an application does not have to include SQL statements for multiple network operations to perform database server operations, but simply calls stored procedures and stored functions, only the name and output result of the call process are transmitted over the network, which can reduce a lot of network I/O.

3. Application Adjustment

3.1 SQL statement Optimization

The execution speed of SQL statements may vary depending on many factors. However, the main influencing factors are: driving the table, executing the operation sequence, and applying the index. These factors can be indirectly changed by many different methods to achieve optimal execution speed. Here we mainly discuss the optimization principles that should be followed when connecting to multiple tables:

3.2 Create and use views and Indexes

A view can be used to cut down or hide a part of data from columns or rows in a base table. It can also display complex queries involving multiple tables in a view, making application development simple and fast. Using indexes can improve query performance, reduce disk I/O, Optimize Query of data tables, and accelerate SQL statement execution. However, index creation can improve performance at any time. When to create an index, the following principles should be observed: This table is often used for queries on index columns, and this table is not often updated, inserted, or deleted, the number of query results should be controlled to 2% ~ in the original table ~ 4%.

3.3 Use the Oracle array Interface

When a customer application inserts a row or uses a query to request a row from the server, it does not send a network packet with a single row, but uses an array for processing, that is to say, buffer multiple rows to be inserted or multiple rows to be retrieved in the array, and then transmit these arrays online through a few packets. For example, if a given Select statement returns 2000 rows of data, the average size of each row is 40 bytes, the data packet size is 4 kb, And the array size parameter (arraysize) is set to 20, the server sends 100 data packets to the client. If you simply set the (arraysize) to 2000, the same operation only needs to transmit 20 packets. This reduces the amount of network transmission and improves the performance of all applications.

Iv. Summary

When developing applications, we followed the above methods and principles to adjust the system and received satisfactory results. However, it should be noted that the client, network, and server components must be adjusted and synchronized to produce the best performance. Therefore, the system should also be based on the specific circumstances of the system, specific analysis and adjustment. This article provides a detailed explanation of the problem of Oracle database performance improvement. It will be introduced from various aspects and hope to help you.