Six Points of Oracle Performance Optimization

Optimization and adjustment of worker ie performance is a system project. The objective is to ensure that the Oracle database runs in the optimal performance state. The database optimization strategy should be considered before the 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, the application analysis and design are completed before the information system development.

Analysis and Evaluation of Oracle 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. Database User response time can be divided into two types: system service time and user wait time, that is, 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.

Oracle performance optimization includes the following parts:

1. Oracle Performance Optimization-Data Structure Adjustment design.

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 indexes are required for frequently accessed database tables.

2. design the application structure for Oracle performance optimization.

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

3. Oracle performance optimization: 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 selects Oracle Optimizer and row-level manager to adjust and optimize SQL statements.

4. Oracle performance optimization: Adjusting server memory allocation.

Memory Allocation is optimized during the information system operation process. The database administrator can adjust the size of the data buffer, log buffer, and shared pool based on the database operation 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 higher the SGA area, the better. The higher the SGA area, the higher the memory occupied by the operating system during the Conference, and the higher the virtual memory, the higher the page switch.

5. Adjust hard disk I/O for Oracle performance optimization.

This step is completed before the 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.

6. Oracle performance optimization: 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 methods 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 reason for performance deterioration is a variety of reasons. Sometimes multiple factors contribute to performance deterioration. This requires the database administrator to have a thorough understanding of the computer, the primary cause of the impact on database performance is sensitive. In addition, good database management tools are also very important for optimizing database performance.

1. Explanation of nine basic Oracle Performance Optimization Methods
2. Key Points of Oracle Performance Optimization
3. Rollback Segment optimization for Oracle Performance Optimization
4. Oracle performance optimization achieved through Partitioning technology
5. Tips for Oracle performance optimization Series