Briefly describe several key points of Oracle database performance optimization

With the popularity of Oracle database, the problem of how to optimize Oracle database is always in the Oracle database for DBAs, with the increasing amount of data and the increasing of traffic and concurrency.

In general, Oracle database operations often encounter the following issues:

1, the database operation is particularly slow;

2, business system operation is particularly slow;

3, Web page open particularly slow;

4, the system can not be run slowly;

5, the server CPU occupies a particularly high;

6, the server memory utilization is particularly high;

...... and other issues.

Database optimization itself is a multidimensional, in the process of database optimization, generally have three aspects, CPU, memory and disk IO, causing the database to run poorly may be because these resources are not enough to use the reasons;

The multidimensional degree of database optimization is mainly embodied in the following aspects, for example, we increase CPU, enhance CPU processing ability, can withstand more concurrency, adjust disk IO storage, the CPU of the response needs to participate in more operations, so the CPU utilization will be increased; In addition, we do redo, Oracle's redo log write slow also affects the submission of some data, resulting in GC buffer busy or some other aspect of the wait, the above is in the Oracle database optimization process needs to be noted.

Determination and measurement of performance optimization objectives

1, the purpose of performance optimization is to make the database response to become faster

(1) What is slow? Where is the slow run? Is there a problem with the network?

(2) is the expectation too high? Is it an application issue or a database problem?

2, the importance of communication

For example, the user reflects the bank counter business reception slow, how should analysis, processing? The first step is to determine the scope of the impact: including the business scope and the affected area: Slow account opening? Slow remittance? Are the individual dots slow or are all the dots slow? The same is true of the database, when the database is optimized, it must be clear that the database is not a problem? Where's the problem? What's the impact?

Some aspects to be aware of for performance optimization

1, the proportion of the database in the business system: the performance of the database affects the performance of the whole business system;

2, the performance of the database is only affecting the entire business system performance chain of a link;

3, CPU, memory, IO resources can affect database performance;

4. The reasonable design of application (business logic) has great impact on database performance;

5, SQL statement of good or bad impact on database performance is huge

6, the physical design of the database (index, anti-normalization, partitioning) has great impact on database performance;

7, the test system and the formal environmental performance may be very large gap;

8. There is a big difference between OLAP and OLTP systems in the lake.

How database optimization should be performed

1, Application layer: reduce application requests;

2, Database layer: Reduce the database internal resource contention;

3, Memory layer: Reduce the number and size of IO;

4, storage layer: Optimize the IO processing ability.

Oracle database optimization still requires a lot of specific methods, the above is only a few major aspects, or need more detailed analysis of database operations personnel.

More optimization methods to enter Otpub's technical classroom: Oracle Database performance optimization http://www.otpub.com/course/detail/index/id/179.html

Briefly describe several key points of Oracle database performance optimization