Shulou(Shulou.com)05/31 Report--

This article is about how Oracle interprets the implementation plan. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

I'll start with a sentence, because I think this one is typical, so let's first use this execution plan to interpret the execution plan.

Then there is an implementation plan that I have adjusted for this sql. This article mainly looks at the part of the implementation plan.

The beginning of the execution plan

First of all, I'm sorry, I forgot the screenshot of sql_id,plan_hash_value. Basically, the first part is the beginning of the execution plan, with sql text and sqlid,plan hash value,child number, so I won't repeat it.

The text of the execution plan

First of all, the origin of this text, in order to see the starts,a-rows,a-time,buffers,0men,1mem... So, use:

Alter sesssion set statistics_level=all

Select * from dbms_xplan.display_cursor (null,null,'advanced allstat')

So this time we are mainly talking about the content of the implementation plan in terms of dbms_xplan package.

Id,operation,name

Let's first give a general introduction to the meaning and origin of these three.

Id: it's actually a sequence number that identifies each step, just like an ID card. This is obtained from the id column of v$sql_plan, and it doesn't represent the order in which they are executed, but I'll talk about the order and principle of reading the execution plan later.

Operation: it's the combination of the operation column and the option column in v$sql_plan. The meaning is very simple, which is the operation of this step.

Name: even the object_name in v$sql_plan is the name of the object for this step.

Now let's explain how to determine the order in which each step is performed.

The formula can be found casually, that is, look at indentation, from left to right, if it is not aligned, it is executed first on the right; to the upper and lower parallel or aligned (next to each other), the upper one is executed first than the lower one, and then continue to look to the right, and then finish when you see the far right. In fact, this does not quite understand what I said, can be combined with Baidu.

I will directly talk about the principle, of course, it is not the principle of the lowest level implementation, and that can not be done yet.

Looking from left to right, the one on the right is executed first because an indent represents a father and son, and the son collates the result set obtained by his own steps and hands it over to Laozi, that's all. So the execution order of 0-8 is 8 7 6 5 4 3 2 10. (I'd like to apologize again. I'm a little stupid. I didn't choose the one with two.

Next to the son's execution plan, so let's talk about the principle first, and then combine this piece with Baidu)

Then when you walk back on the way to find your father, you find that you have id=4, and you look down and find an uncle id=9. At this time, 4 and 9 are juxtaposed, that is, children of the same generation, so there is no hurry to go to 3, but first to see if 9 has a son. There is indeed one, which should be carried out first on the right, which should be 10: 9 first.

But the question is, who will execute 4 or 9 first? Because the juxtaposition is the above execution first, so it is 4 execution first. Then someone asked, what is the order of 9 and 3? 9 and 4 are brothers, 4 is the third son, that is to say, 9 is also the third son, of course, the son does the collection result set first. So now the order is 8-7-6-5-4-10-9-3-2-1-0.

However, when I found id=3, I found that the father also had a son of the same generation, id=11, but this 11 didn't happen to be a bachelor and had no son. Then discharge 3: 11 first. The last 2 / 10 have no brothers, so the final execution order is 8-7-6-5-4-10-9-3-11-2-1-0.

This is very boring to say, easy to read in fact, now I will talk about the main point, how on earth this is realized.

In fact, this is a binary tree, because the first time I wrote a blog, I didn't take into account that I didn't capture the position and parent_id in v$sql_plan, as well as the depth of the tree. I was sorry, so I dictated the graphic performance of these two. It is they who show in the above picture of the implementation plan: the same shrinking progress is the position value.

Similarly, only one shrinking progress is the father-son relationship, which means that both id=3 and id=11 have a parent_id of 2.

Now let's draw this binary tree. The binary tree has only two forks, so you can't see who wants to have three children if they don't abide by family planning.

Look, the number in each block is id. Drawing allows us to look at the execution plan, press id to draw the diagram, and then sort out the execution order of the execution plan.

First of all, the first child draws to the left, and the second child draws next to the right. Look for the typical part of it to show that the child under 3 is 4, but it is found that there is only one indent less than 3, or 9 with the same indentation as 4. The first found 3 so, 3 draw the left, 9 draw the right. And the one below 9 has 10, so 10 draws the left below 9.

Well, someone asked, according to this, 10 and 5 are also parallel, both are one less indented than 4, why not draw 10 below 4, because it is next to the straight line, and you won't go to Lao Wang until you find a father who is not next to you. Therefore, we draw according to this principle and draw the essence of this implementation plan.

Then, how to traverse the tree, traversing in post-order, don't ask why. Traversal after order: first left, then right, then root. That is to say, always find the leftmost node (the rightmost step of finding the first parallel brother next to each other, that is, the brothers who are close to each other, the entrance to the plan), and then look to the right (this is to find the brother, this is why the next brother will be executed first)

If you don't, just look up (find your father, the reason for indenting the steps to the right first).

Finally, let's take a look at the picture above. Is it 8-7-6-5-4-10-9-3-11-2-1-0? If you really don't know how to traverse the post-order, you Baidu encyclopedia, the thief is simple.

Starts

This is the total number of times the real step was executed, and you can see that all the steps above were performed only once.

A-rows

This is the actual number of rows returned by this step, actual rows. In other words, the a-rows of the top id=0 step is the total number of rows returned by this query.

A-time

The real execution time of this step. The a-time of the top id=0 step is the actual execution time of this query.

Buffers

Logical reading, reads is not shown here, but again, reads is physical reading.

0mem,1mem

This refers to the memory size of PGA used by hash join,sort,group by, which I haven't verified yet. It seems that 0mem uses PGA memory and 1mem uses hard disk space.

Qurey Block Name to execute the plan

It may not be noticed here, but I think we should take a look at it for beginners. The so-called query block, to put it bluntly, is the number of select. This part of the content-the previous number is step id.

I use / * + qb_name () * / to fix the query block name here, which is convenient for reading. In fact, if you don't use this, oracle will name the query block itself, and it's easy to understand. It all starts with SEL$, followed by a number. For example, if you look at 10-this line, A@zong2, let's go back to the execution plan and take a look. Id=10 has an index.

You may come up and look at the implementation plan and say, "Oh, I'll go. Whose index is this?" That's what qurey block told you! This queries table an on block zong2. If I name this zong2, oracle will name it SEL$2. Why?! Because to put it simply, this is the second select.

I hope that when the beginners look here, they will look in the original plan and the original sentence line by line.

By the way, here we find an alias that is not in the statement, from$_subquery$_007. Let's go back to step 4 and find that it is VIEW, and then we can see that it is a qiurank1 block. It is very clear that this is a view of the whole subquery of qiurank1. To put it bluntly, it is not a view that really exists in oracle.

As a reminder, don't see view as a view in the execution plan. Subqueries are not expanded and are treated as view.

To sum up, do we find that from this part of the content, you can find out who the specific operation object is, which select belongs to?!

OK, but the use of this part is not over here. Let's move on.

The OUTLINE part of the execution plan

Do you see this / * + * / look a little familiar? yes, this is the hint used in the execution of this statement. In fact, hint is used to control this part. This part actually tells you exactly what was done in the execution plan. Of course, there are some I am not sure about, such as merge (qba > qbb) and no_access. So far, we can only guess roughly, the reason is very simple.

Because I am also a beginner.

Then someone may have to ask, the text of the execution plan plus the predicate conditions to be introduced later, has not already told us how this sql works in the end? Why are you watching this. The key is that generally we pay attention to the implementation plan because there is something wrong with the implementation plan. Therefore, it is not enough for us to know how to implement the plan. Do we also need to know how to modify it so that

It takes the right path according to our ideal posture?! For veteran drivers, people know at a glance what hint to add, but for beginners, let's honestly look at outline to see what hint should be added, or what hint should be modified.

For this picture, for example, I see the penultimate line, the index_rs_asc line, which clearly tells you that one step is to take the index. This step is to go to the x table in the qiurank block. Index, and the index is in RS_ASC:range scan ascending order, indicating that the index is normal to walk from left to right and is an index range scan.

What did you say at the bottom of this line?! Is it true that the x-table index in the qiurank block has been scanned, and then rowid is used to return the table in the way of multi-block scanning such as batched? you can look in the execution plan, which is what id=8,7 does. And if you look up at the line, the leading line, is the order of the hash join the same as that embodied in the execution plan?

Then let's adjust the order behind here, and then add it to the hint of sql. Just like the hint of this sql, can you easily adjust the order of hash join? you say you adjust An and B without looking at this outline, then you adjust the order of the whole block in the hash join. How are you going to adjust it?! Of course, there must be another way, but isn't it?

It is very convenient, after you adjust this piece, and then when fixing the profile, it is also convenient to change the beam and change the post.

So I think we should have a good understanding here. I haven't studied it thoroughly yet. if you read this article, you can exchange and discuss common progress.

Predicate information to execute the plan

There is nothing to say about this seriousness, so it is very straightforward. The previous number or id,access represents the use of the index, which means to enter the index block and run around. Filter is the real filter condition, that is to say, it is the filter condition on the table after returning to the table. I look at the implementation plan step by step.

Although this is very straightforward, it is very important, if you do not look at outline and qurey block, forget it, if you do not look here, then read the implementation plan is actually quite difficult, because you have to put all the basic information of the columns and indexes of the table next to it, and analyze it to sql yourself, you will be very tired. And sometimes, you think the index is right, for example, the last condition of sql.

If you make a mistake on one side of the date, such as adding an extra space, the execution plan is still the same, but predicate information is different. So, if you can see this part, you should take a good look at it.

Column projection to execute the plan

I would like to say a little bit here, although the content is not much, the previous number or id,keys means that there is a sort here (I will add this part later when I learn to understand it).

Dana may not necessarily look here, but for beginners, it is rewarding to take a look. This section is the column for each step operation and the data type of that column. The reason is very simple, but we can learn something from this part and the text of the execution plan, which columns are being operated on in each step, and we see that your real sorting operation begins at 5 after a series of 8-7-6 filtering.

The sorting of analysis functions, before completing the final formation of an overall view, 8 7 6 5 is operated with rowid.

Anyway, if you look at this, you can see the implementation of the plan from another dimension. Still hope that beginners or take a look, I think there is still a harvest.

Note to execute the plan

Needless to say, go straight to the picture I summarized and get to know it for yourself.

Thank you for reading! This is the end of the article on "how to interpret the implementation Plan of Oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.