Shulou(Shulou.com)06/01 Report--

AWR report is an important reference for database performance evaluation and optimization. It shows the quantified form of database problems and brings a lot of convenience to DBA. However, there is a lot of content in AWR, so how can we interpret the AWR report in the best way and find out the performance problems of the database most efficiently?

At the just-passed OOW2017 conference, Graham, the father of AWR, made a topic sharing called "AWR Analysis for Admins, Developers and Architects" AWR report analysis that all operators, developers and architects should read. The ppt of the speech has been made public on the official website of oow, so let's briefly interpret the main content of the sharing. I hope it can be of some help and reference to everyone.

Note: the original ppt can follow the data and get the OraNews reply keyword 2017oow.

The following is the interpretation of the ppt

In the real world, the root causes of performance problems are as follows:

1. The database is not used according to the intended design purpose.

2. The architecture or code design of the application is poor.

3. Some bad algorithms in the database may cause problems.

For most people, it is said that the goal of optimization focuses on some small details, such as the poor performance of a certain SQL, the unreasonable setting of a component of shared pool, and so on. Adjustments to these details generally lead to a small performance improvement, and most people are satisfied with it.

The RWP team has been pursuing more than a thousand times the ultimate performance improvement, for them, every performance problem should be found to the root, from the most effective point of view to solve the problem, rather than be satisfied with a small performance improvement.

In their work, general performance optimization involves the following aspects:

Code rewriting, application logic modification, ensuring normal use, bug repair, etc. Through the adjustment and modification of multiple dimensions, the performance of the system is improved a thousand times.

There are many ways to find database performance problems, not just looking at wait event and top SQL. In fact, a lot of the data we need can be obtained from the AWR report. At the same time, we also need to understand the design and implementation principle of the system architecture. In our experience, many performance problems are caused by unreasonable architecture design or logic problems with the application code.

Next, we share how to locate the problem through the interpretation of AWR, what important information we should pay attention to in the AWR report, and make effective use of the data in the report, so as to give full play to the real value of AWR.

First of all, look at the head of the AWR report. The part to pay attention to is shown in the yellow mark in the figure. First of all, we see that there are 4 socket in the system, with a total of 32 cores, and the CPUs shows 64, which should be hyperthreaded. The session value is very high and continues to grow during the sampling time.

Guess: it could be a session leak or a connection storm.

Knowledge point supplement

Session disclosure: session disclosure occurs when the application is disconnected and the corresponding session in the database is still active. For applications, it means the loss of the program. It is usually caused by an exception in the application, and the contact with the database is lost when commit or rollback is not executed properly in the database.

When the session itself is high, the cursor value in each session increases from 8 to 26. This indicates that the cursor is exhausted in the session.

Guess: there may be a problem of cursor leakage.

Let's look at the detailed load information.

When DBtime reaches 260, it means that the number of active sessions at the same time reaches 260, which is greater than the number of CPU cores of the system (32).

Logons is 10.5, and there are 10 + session logins per second, which is very high. Under normal circumstances, the average system may be around 1. This indicates that there is an exception in the system, and it is speculated again that it may be due to session leakage or connection storm, which is consistent with the previous information.

60% of user transactions are being rolled back. (40% of the transactions in the figure should be miswritten, and 40% of the transactions are committed.) this is also abnormal.

Next, let's look at the settings of some parameters in the database.

We see that the size of the block in the database is not the default block 16k. Set cursor_sharing to Force at the same time.

Knowledge point supplement

The cursor_sharing parameter has two options, exact and Force, and the force option means that the optimizer replaces all text values with system-generated binding variables, and if the SQL statement is the same after using the binding variables, the optimizer uses the same execution plan.

Setting the parameter to Force is not recommended in general. This is likely to lead to the risk of SQL injection, and for functions in SQL, in some cases where it is better to use text instead of binding variables, using system-generated bound variables may have a negative impact on the execution plan.

Therefore, it is generally recommended that the system be set to EXACT and only in special cases to Force. For details, please refer to the official website (http://docs.oracle.com/database/122/TGSQL/improving-rwp-cursor-sharing.htm#TGSQL-GUID-6C3AFFA0-21DD-41BC-8DEE-5FC9A58B0954)

The DB_file_multiblock_read_count default corresponds to the maximum I / O size that can be performed efficiently and related to the platform. This is equal to the number of CPUs cores of the system, indicating that there is no problem with IO.

The open_cursors parameter specifies the maximum number of cursors a session can open at a time, with a default value of 50. 0. Now set to 2000, which is very high, indicating that there is an exception in the system.

From which db_recovery_file_dest parameter is set, we can see that the storage type ASM,ASM supports asynchronous IO, and if asynchronous IO is supported, it is not normal for open_cursor to reach 2000.

The default value of DB_Writer_processes is 1 or CPU_count/8, whichever is larger. At this point, it is set to 12, which is larger than the default value and should be adjusted manually.

According to the previous information, the system should be 2 nodes with a recommended value of 50-2-50-150 for RAC,processes. At this point, it reaches 5500, and the default value of sessions is processes*1.5+22=8272, and the value in the figure should also be adjusted manually. The reason for the adjustment is speculated that 8272 is not enough. This is very abnormal.

The next step is the analysis of waiting events. See that most of the system is waiting.

The following is an analytical description of the specific top SQL.

Therefore, based on the above information, it is speculated that the system may be the problem of session leakage and cursor leakage. For session leakage, it is generally caused by the exception of the application, and the conclusion can not be drawn directly through the analysis of the database level, nor can it be solved simply from the database level.

Above, in view of a specific AWR report, we can see which problems need our attention most, which can help us to analyze the problems of the system most effectively. I hope it can be used for reference to everyone.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.