ORACLE Database performance optimization Overview

ORACLE DatabaseWhen running at the best performance, it is essential to optimize the performance of the ORACLE database. Database optimization policies should be considered before information system development. The optimization policies generally include Server OS parameter adjustment, ORACLE database parameter adjustment, network performance adjustment, and application SQL statement analysis and design, application Analysis and design are completed before information system development.

Analysis and Evaluation of ORACLE database performance mainly includes two indicators: Database throughput and database user response time. Database throughput refers to the number of SQL statements completed by the database per unit time. Database User response time refers to the period from when the user submits the SQL statement to when the result is obtained. The database user response time can be divided into two types: system service time and user wait time:

Database User response time = system service time + User wait time

The above formula tells us that there are two ways to get a satisfactory response time: one is to reduce the system service time, that is, to increase the database throughput; the other is to reduce the user wait time, this reduces the conflict rate of user access to the same database resource.

Performance Optimization includes the following parts:

One of ORACLE database performance optimizations: the design of adjusting the data structure.

This part is completed before the development of the information system. Programmers need to consider whether to use the Partition Function of the ORACLE database and whether to create indexes for frequently accessed database tables.

ORACLE Database performance optimization 2: adjust the application structure design.

This part is also completed before the development of information systems. In this step, programmers need to consider what kind of architecture the application uses. It uses the traditional Client/Server two-layer architecture, or use Browser/Web/Database's three-tier architecture. Different Application architectures require different database resources.

ORACLE Database performance optimization 3: Adjusting Database SQL statements.

The execution of the application is ultimately attributed to the execution of SQL statements in the database. Therefore, the execution efficiency of SQL statements ultimately determines the performance of the ORACLE database. ORACLE recommends ORACLE Optimizer and row-level manager to adjust and optimize SQL statements.

ORACLE Database performance optimization 4: Adjust the server memory allocation.

Memory Allocation is optimized during the operation of the information system. The database administrator can adjust the size of the data buffer, log buffer, and shared pool based on the database running status in the SGA region of the database system; you can also adjust the size of the global PGA area of the program. Note that the larger the SGA area, the better. The higher the SGA area, the higher the memory occupied by the operating system during the Conference, resulting in Page Swap of virtual memory. This will reduce the system.

ORACLE Database performance optimization 5: Adjusting hard disk I/O is completed before information system development.

The database administrator can store data files in the same tablespace on different hard disks to achieve I/O load balancing between hard disks.

ORACLE Database performance optimization 6: adjusting operating system parameters.

For example, an ORACLE database running on a UNIX operating system can adjust the size of the UNIX data buffer pool and the memory size that each process can use.

In fact, the above ORACLE database performance optimization measures are interrelated. The deterioration of ORACLE database performance is basically because the user response time is relatively long and the user needs to wait for a long time. However, the causes of performance deterioration are diverse. Sometimes multiple factors contribute to performance deterioration. This requires the database administrator to have comprehensive computer knowledge, the primary cause of the impact on database performance is sensitive. In addition, good database management tools are also important for optimizing database performance.

We have introduced so much about ORACLE database performance optimization. I hope that after learning the above, we will be able to easily solve the problem of ORACLE database performance optimization in future work, I believe everyone has gained some benefits from the content mentioned above.