A typical multi-table engagement connection for complex SQL tuning (SQL TUNING)-induced thinking

Today, while watching Tri Hua's SQL optimization book, he saw a case in which he solved the SQL performance problem, and Tri Hua the teacher successfully positioned the problem and solved it. Here, on the basis of Tri Hua teacher analysis and positioning, do further analysis and reasoning, so that we can study and discuss together, the following is a brief description of the case scenario.

1. SQL statement with performance issues:

Cu

--Note:

1) More than 10 table more complex SQL statements that participate in the connection.

2. Implementation plan for performance issues:

--Note:

1) The planned node 19 has a FTS on table s_evt_act, which is said to have more than 700 watts of data on the table.

2) The planned node 34 has an index range scan on table s_act_emp.

3, no problem of the implementation plan:

--Note:

1) The planned node 23 walks on the table S_evt_act Index unique scan (unique index Scan).

2) in the Plan, node 19 walks on table S_act_emp Index range scan.

4. Analysis:

1) Before the performance problem is resolved, node 19 in the plan has a FTS on s_evt_act, and node 34 has an index range scan on table s_act_emp.

2) After the performance problem is resolved, node 23 in the plan has an index unique scan on s_ect_act, and the original node 19 is pushed back to node 23, and node 19 has an index range scan on table s_act_emp. Access is not changed until the performance problem is resolved, and the original node 34 is pushed forward to node 19.

3) compared with the performance problem before and after the resolution, there are two changes, one is the table s_evt_act from the original FTS into the index unique scan, and was pushed back, and the other is the table s_act_emp before being pushed. The reason for the change is that Optimizer_mode has the original first_rows_10 into All_rows.

4) In response to the two changes before and after the performance problem, we discuss the causes and effects of the changes. Before the performance problem is resolved, because Optimizer_mode is set to First_rows_10, the CBO, in this mode, will not query and calculate the statistics and costs of each participating table individually, in the face of this more complex multi-table connected SQL statement. Instead of giving a rough assessment of the result or default value, why is this, everyone think about it, in fact, even in accordance with this method, it is not necessarily a performance problem, because this mode is the response speed, if the table s_evt_act and other tables of the connection field matching is good enough, then, can also achieve the best response speed, here, the problem is not to go to the FTS, but the table s_evt_act connection field matching, but in this mode, the CBO can not get the exact results of this match. So this is a very risky decision, but that's the way it is, no way. In addition, for the index range scan on table S_act_em is pushed before it is set to All_rows mode, this is the right thing to do anyway, so let's see this SQL statement, just in first_rows_10 mode, the CBO has an error evaluation, This may also be one of the important factors that affect performance.

5) Thus, in all_rows_x mode, especially when the x is lower, complex SQL statements should be careful, perhaps, this model is more suitable for OLTP business, rather than OLAP business.

Personal opinion, for reference only.

Note: This material comes from the SQL optimization book by Tri Hua teacher.

A typical multi-table engagement connection for complex SQL tuning (SQL TUNING)-induced thinking