12.6. Using Execution Plans Properly Execution plans are useful, but mostly to check that the DBMS engine is indeed proceeding as intended. The report from the field that an execution plan represents is a great tool to compare what has been realized to the tactics that were planned, and can reveal tactical flaws or overlooked details. 12.6.1. How Not to Execute a Query Execution plans can be useful even when one has not the slightest idea about what a proper execution plan should be. The reason is that, by definition, the execution plan of a problem query is a bad one, even if it may not look so terrible. Knowing that the plan is bad allows us to discover ways to improve the query, through the use of one of the most sophisticated tools of formal logic, the syllogism, an argument with two premises and one conclusion. This reasoning is as follows: (Premise 1) The query is dreadfully slow. (Premise 2) The execution plan displays mostly one type of actionfor example: full table scans, hash joins, indexed accesses, nested loops, and so forth.
(Conclusion) We should rewrite the query and/or possibly change indexes so as to suggest something else to the optimizer. Coaxing the optimizer into taking a totally different course can be achieved through a number of means: When we have few rows returned, it may be a matter of adding one index, or rebuilding a composite index and reversing the order of some of the columns; transforming uncorrelated subqueries into correlated ones can also be helpful. When we have a large number of rows returned we can do the opposite, and use parentheses and subqueries in the from clause to suggest a different order when joining tables together. In doubt, we have quite a number of options besides transforming correlated sub queries into uncorrelated subqueries and vice versa. We can consider operations such as factorizing queries with either a union or a with clause. The union of two complex queries can sometimes be transformed into a simpler union inside the from clause. Disentangling conditions (trying to make each condition dependent on as few other conditions as possible) is often helpful. Generally speaking, trying to remove as much as possible of whatever imposes a processing order on the query and trying to give as much freedom as possible to the optimizer is the very first thing to do before trying to constrain it. The optimizer must be constrained only when everything else goes wrong. As a last resort, we may remember the existence of optimizer directives and use them very carefully.
12.6.2. Hidden Complexity Execution plans can also prove to be valuable spies in revealing hidden complexity. Queries are not always exactly what a superficial inspection shows. The participation of some database objects in a query can induce additional work that execution plans will bring to light. These database objects are chiefly:
Views Queries may look deceivingly simple. But sometimes what appears to be a simple table may turn out to be a view defined as a very complex query involving several other views . The names of views may not always be distinctive, and even when they are, the name by itself cannot give any indication of the complexity of the view. The execution plan will show what a casual inspection of the SQL code may have missed, and most importantly, it will also tell you if the same table is being hit repeatedly.
Triggers Changes to the database may take an anomalous time simply because of the execution of triggers . These may be running very slow code or may even be the true reason for some locking issues. Triggers are easy to miss, execution plans will reveal them.
The essential value of execution plans is to provide a starting point for performance investigations and to reveal the hidden database operations caused by complex views and triggers. |
