Using the SQL query execution plan layer on VST diagrams

Performance optimization is an area in which everyone would like to become a great master. If we talk about specialists in the field of working with databases, then we all come as novices and at the beginning of a career spend a lot of time studying the basics, trying to understand the art of setting up database servers and applications to improve performance. However, and as you go deeper into the topic, performance optimization does not become easier.

With the development of technology, the introduction of modern "flexible" approaches, "continuous integration" in the field of databases, the need for faster response to requests from end users is only intensified. In the current conditions of the spread of mobile devices, it is almost always necessary to make changes to data processing systems in order to speed up the exchange of data with users' native or WEB applications.

We are seeing the constant emergence and introduction of new technologies. It's great! At the same time, the existing “old” technologies require a lot of attention and time to support. "Ocean" of data, "sea" of databases, more distributed systems. Less time left to tweak and optimize. Shrinking windows for modification, maintenance, and modification makes it difficult to increase the continuity of systems on existing equipment.
')
In the area of ​​tuning database optimization, there are often situations when it is difficult to choose the “right” solution. In such cases, you have to rely on various tools that help assess the situation and find ways to improve it. Having mastered such tools, it often becomes easier to find the best solution if a similar situation arises later.
In support of this thought, I will cite a translation of a curious article from the blog bulldba.com/db-optimizer

---

In the new releases of DB Optimizer from Embarcadero, starting with version 3.0, there is a great new feature: put a VST explain plan request on the diagram!

[Translator's Note:
Visual SQL Tuning Visual Optimization (VST) turns textual SQL code into a graphical SQL diagram, shows indexes and constraints in tables and views using statistical information, and join operations used in SQL statements, such as direct and implied Cartesian products. and many-to-many relationships. ]

Take for example the following query:

SELECT COUNT (*) FROM a, b, c WHERE b.val2 = 100 AND a.val1 = b.id AND b.val1 = c.id; 

Indexes are created for columns b.id and c.id. In the DB Optimizer window, this query looks like this:



Red lines of links of this type in accordance with the definitions say that the relationship may be of the "many-to-many" type.
The question is: “What is the best execution plan for this query?”

One of the possible optimal plans for the execution of this "query tree" may be as follows:

1. Start with the most selective filter.
2. JOIN with subordinate tables, if possible
3. If not, then perform a JOIN with a top-level table.

In this diagram, the only filter. It is indicated by the green F symbol on table B. This table has the query criteria “b.val2 = 100” in the query.
Well, let's start with table B. Where do we send our plan for further implementation? Who is the "subordinate" and who is the "main"? How to determine? .. Oracle when planning a query encounters the same difficulties. How to understand why Oracle made the decision, and not otherwise? This is where new DB Optimizer features come to the rescue.
DB Optimizer has a super cool way to superimpose a current execution plan on a VST chart (I like it so much!).



Now we see that Oracle starts with table B and connects it with table A. The result is connected with table C. Is this plan optimal?
Leave the existing indices and add a couple of constraints.

 alter table c add constraint c_pk_con unique (id); alter table b add constraint b_pk_con unique (id); 

Let's analyze the query in DB Optimizer again:



Now we can see who is in charge and who is subordinate, based on this, determine the optimal execution plan for the query, which begins with filtering B, then connecting to C, then from A. Let's see how Oracle handles the added integrity constraints.



You see, the execution plan changed due to constraints, and Oracle moved the execution plan from sub-optimal to optimal.

The moral of this story is that to be sure, you need to define integrity constraints in the database, because it contributes to the work of the DBMS query optimizer, but the main thing I wanted to show here is the overlay of the query execution plan on the VST diagram, which makes a comparison of plans much easier. With the help of the adjacent VST diagrams with superimposed query execution plans, you can quickly and easily see the differences.



I plan to write more about this opportunity. This is really great.
Here is another example from the article Jonathan Lewis www.simple-talk.com/sql/performance/designing-efficient-sql-a-visual-approach
In it, Jonathan discusses the query:

 SELECT order_line_data FROM customers cus INNER JOIN orders ord ON ord.id_customer = cus.id INNER JOIN order_lines orl ON orl.id_order = ord.id INNER JOIN products prd1 ON prd1.id = orl.id_product INNER JOIN suppliers sup1 ON sup1.id = prd1.id_supplier WHERE cus.location = 'LONDON' AND ord.date_placed BETWEEN '04-JUN-10' AND '11-JUN-10' AND sup1.location = 'LEEDS' AND EXISTS (SELECT NULL FROM alternatives alt INNER JOIN products prd2 ON prd2.id = alt.id_product_sub INNER JOIN suppliers sup2 ON sup2.id = prd2.id_supplier WHERE alt.id_product = prd1.id AND sup2.location != 'LEEDS') 

Which on the VST diagram looks like this:



There are several filters here, so we need to know which one is the most selective, so let's turn on the statistics on the chart (the blue numbers under the tables are the percentage of filtering, the green numbers above the tables are the number of rows in the table, the numbers on the relationship lines represent the number rows returned by joining these two tables).



Now we can determine the best way to perform optimization. Did Oracle use it?



Did you see an optimizer error?
Dark green indicates where the execution begins. Here - in two places: in the body of the main query and in the subquery. The end point of the query is marked in red.
Another example (Karl Arao):



Here execution begins in 4 places. Notice how the result sets from each start are combined with each subsequent result of joining the tables. The final is marked in red.

---

^{[End of Translation]}

Optimization of database systems in modern environments often requires advanced knowledge, but, as before, for the most part remains an art.

For example, it is practically impossible for a person to test all valid variants of hints and indexes in order to find the most optimal solution. You have to rely on intelligent tools like Embarcadero DB Optimizer, which will guide you along the path of adjustment, and will help you choose the best option from those offered.
In the examples, it was shown how its advanced capabilities helped not only to quickly discover the direction of query optimization, but also to get explanations for the decisions made by the Oracle “regular” optimizer, to find the missing descriptions that provided a more “correct” optimizer's work in the future.
For more information on working with VST diagrams, see the link to the Jonathan Lewis article or on the Embarcadero website.