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

Understanding the execution plan is one of the necessary skills to deal with database performance problems, and I will show you how to do it in the following series of articles. We will start with some articles to discuss several ways to obtain execution plans and evaluate which scenarios are most appropriate for execution plans from different sources. After completing the above, we will continue to explain the basics of a simple execution plan in depth, followed by a rule that explains most of the execution plans-a rule that needs to be used with care. It will also focus on situations where the execution plan is not as expected due to the optimizer rewriting our SQL. Some exceptions outside the rules are inevitable, so after some simple plans, we will continue to learn about more complex plans, including updates, deletions, subquery decomposition, and scalar quantum queries. Then continue to learn about distributed queries, the impact of partitioned tables on plans, and parallel execution. These articles will not include all the actions and options in the execution plan, but will contain some actions that are important enough to handle most of the execution plan. What is an execution plan when we write a SQL statement, we may think about how Oracle will produce results. The execution plan is the execution path that Oracle transforms the SQL statement into a series of execution steps and produces the results we want. The way we interpret the plan is the same whether it is the execution plan that is generated before the statement is executed or the plan that is generated when the statement is actually executed. However, the level of detail (and even our confidence in the correct implementation of the plan) varies depending on when and how the execution plan is obtained. For example, there is the following query: selectt1.v1, t2.v1Fromt1, T2 predicate 1.N2 =: b1And t2.id = t1.idAnd t2.n2 between: b2 and: b3; this is a simple two-table association; a single-column association condition with two single-table predicates filtering data. When we look at this statement, we want to answer the following questions-these questions are not necessarily independent of each other: which table will Oracle access first? how does Oracle access that table, through index or table scan? how much data can it get? How will which table be accessed next? In which way does Oracle join two tables? How much data will be generated by joining column predicate conditions? How much data will be filtered by applying predicates after a connection? Will there be a significant difference between Oracle's predictions and actual implementation results? Can we quickly determine why it happened (the previous post) and what is the impact? Is Oracle's implementation strategy in line with our expectations? Can we find out why Oracle doesn't implement what we think is a better strategy? In this example, we assume that Oracle uses the index above N2 to find a small number of rows in T1, then uses nested loop to associate with T2, matches each row of filtered data in T1 with the primary key index on T2, gets the rows that match, and finally discards most of the rows that do not meet the criteria (t2.n2between: B2 and: b3). Here are two execution plans for this query, and we need to know some issues that we must consider before we begin to explain the execution plan. These plans come from the database environment of 11.2.0.4. Predicted execution plan-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | | Time |-| 0 | SELECT STATEMENT | | 32 | 832 | 46 (3 | ) | 00:00:01 | * 1 | FILTER | * 2 | HASH JOIN | | 32 | 832 | 46 (3) | 00:00:01 | | * 3 | TABLE ACCESS FULL | T1 | 32 | 416 | 24 (5) | 00:00 | : 01 | | 4 | TABLE ACCESS BY INDEX ROWID | T2 | 500 | 6500 | 22 (0) | 00:00:01 | | * 5 | INDEX RANGE SCAN | T2_N2 | 45 | | 2 (0) | 00:00:01 |- -Predicate Information (identified by operation id):-1-filter (TO_NUMBER (: B2) = TO_NUMBER (: B2) AND "T2 "." N2 "=: B2) 5-access (" T1 "." N2 "=: B1) 6-access (" T2 "." ID "=" T1 "." ID ") 7-filter ((" T2 "." N2 "> =: B2 AND" T2 "." N2 "select * from table (dbms_xplan.display_cursor) PLAN_TABLE_OUTPUT-SQL_ID b3s1x9zqrvzvc, child number 0BEGIN DBMS_OUTPUT.ENABLE (1000000); END NOTE: cannot fetch plan for SQL_ID: b3s1x9zqrvzvc, CHILD_NUMBER: 0 Please verify value of SQL_ID and CHILD_NUMBER; It could also be that the plan is no longer in cursor cache (check v$sql_plan) 8 rows selected.

I call dbms_xplan.display_cursor () to get the second real execution plan above. This is the path that Oracle actually executes based on the value of my bound variable, the statistics involving the object, and the context of the session. But it still only returns the number of rows estimated by the optimizer at each step, not the number of rows actually obtained at execution time. We will continue our discussion in the next topic.

Although there is still a lot to say about dbms_xplan.display_cursor, there are many ways to use it. But that's all for the introduction, and there's a little reminder. Although it will output the actual execution plan based on the binding variables you provide (in most cases), it is not guaranteed to be consistent in different environments, or the same execution plan will always be the same in the same environment.

If you don't know what the last user did, there are many reasons why you will be deceived by the execution plan. In production systems, the most common ones include:

The real binding variable value

Optimizer environment and object statistics

Name resolution

Although the result of the call to "dbms_xplan.display_cursor ()" is much more realistic than the result of calling "explain plan" and "dbms_xplan.display ()", you still need some wise judgment if you want to make sure that you don't get the wrong execution plan.

Conclusion

Through this article we learned that getting an execution plan is actually very easy, but there are two categories of plans-predictive and real. I also learned that if there are bound variables in the statement, the predicted execution plan is more likely to be a wrong execution plan.

A common and fairly accurate view is that the execution plan on the production library will be consistent with the actual execution plan obtained after the statement is executed in your own environment, but this can only depend on whether your environment is very similar to the environment in which the last user of the production library executed the statement.

The "rows" (bytes) information we get from the real execution plan is still estimated, and we will get the real "volume" data in the next chapter, which will also help us determine why the optimizer choice is not what we expected.

Original link: https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-1-finding-plans/

Original author: Jonathan Lewis

| | translator's introduction |

Lin Jinsen, Walker technology database technology expert, Walker technology database engineer, engaged in Oracle database for many years, rich experience in fault handling, performance tuning, data migration and backup recovery

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.