Oracle Database Performance Optimization

In fact, in order to ensure the Oracle database running in the best performance state, the database optimization strategy should be considered before the http://www.aliyun.com/zixun/aggregation/32730.html "> Information system development." The optimization strategy generally includes the server operating system parameter adjustment, the Oracle database parameter adjustment, the network performance adjustment, the application SQL statement analysis and the design and so on several aspects, in which the application analysis and the design is completes before the information system development.


analysis and evaluation of Oracle database performance mainly has database throughput, database user response time two indicators. Database throughput is the number of SQL statements completed by the database in the unit time, and the database user response time is the period from when the user submits the SQL statement to get the result. Database user response time can be divided into system service time and user wait time two items, namely:


Database User Response time = System service time + user wait time


the above formula tells us that there are two ways to obtain satisfactory user response time: One is to reduce the system service time, that is to improve the throughput of the database, and the other is to reduce the user's waiting time, that is, to reduce the conflict rate of users accessing the same database resource.


performance optimization includes the following sections:


Oracle Database Performance optimization: Adjust the design of data structure.


This section is completed before the development of the information system, programmers need to consider whether to use the Oracle database partition capabilities, for frequently accessed database tables need to be indexed, etc.


Oracle Database Performance Tuning Two: Adjusting the application architecture design.


This part is done before the development of information systems, where programmers need to consider what architecture the application uses, whether they use a traditional client/server two-tier architecture, or a three-tier architecture with Browser/web/database. Different application architectures require different database resources.


Oracle Database Performance Optimization Three: Adjust the database SQL statements.


The execution of the application ultimately boils down to the execution of SQL statements in the database, so the efficiency of the execution of the SQL statement ultimately determines the performance of the Oracle database. Oracle Company recommends using the Oracle Statement Optimizer (Oracle Optimizer) and the row lock manager (Row-level Manager) to adjust the optimized SQL statements.


Oracle Database Performance Tuning four: adjust server memory allocations.


memory allocation is optimized in the process of information system operation, the database administrator can adjust the database system global area (SGA area) of the data buffer, log buffer and the size of the shared pool, and can also adjust the program global area (PGA area) size. It should be noted that the SGA area is not the larger the better, the SGA area over the assembly to occupy the memory used by the operating system caused by the virtual memory of the page exchange, which will reduce the system.


Oracle Database Performance Tuning five: adjust hard disk I/O, this step is done before information system development.

The
database administrator can put the data files that make up the same table space on different hard disks to balance the I/O load between the hard disks.


Oracle Database Performance Tuning Six: adjust operating system parameters.


For example, an Oracle database running on a UNIX operating system can adjust the size of the UNIX data buffer pool, the size of the memory used by each process, and so on.


in fact, the above Oracle database performance optimization measures are interrelated. Oracle database performance Degradation performance is basically the user response time is relatively long, users need to wait for a long time. But the reason for the deterioration of performance is varied, sometimes many factors together cause the result of deterioration of performance, which requires the database administrator has a more comprehensive computer knowledge, can be sensitive to the impact of the database performance of the main reason. In addition, good database management tools are important for optimizing database performance.