How to analyze the execution plan of an SQL statement

Press CTRL+M in SQL Server, then execute a SQL statement that you need to analyze, and there will be an Execultion Plan next to the result box, just look at it.

After writing a piece of SQL, you can check the execution plan of the SQL to preliminarily improve the performance of the SQL at runtime, especially in the SQL tuning, we can analyze the SQL performance problem by viewing the execution plan, this article briefly introduces how to view the execution plan of the SQL statement in PLSQL.

That is, if you write an SQL statement, the DBMS starts retrieving from which table, whether to use an index, and if there are multiple indexes, which one to use.

At present, it is generally based on the algorithm of cost repentance, that is, the system estimates the cost of various query methods according to the historical system sliding and brightening information, and then finds out the query step with the least estimated cost for an SQL statement of a certain Birangpeng, which is the execution plan.

Open the PL SQLdeveloper software and make sure that PLSQL can successfully connect to an Oracle database.

Write a piece of SQL ** in PL SQLdeveloper, press F5, or click the "Execute Execution Plan" icon, PL SQLSdeveloper will automatically open the execution plan window, and display the execution plan of the SQL.

You can see that the SQL statement is displayed at the top of the window, and the execution plan is displayed at the bottom. The columns mainly contain descriptions, users, objects, costs, io overheads, etc., and of course, the columns can also be customized. The rows contain the order in which the query logic is executed and information about each step.

The execution order of the content of the execution plan is: according to the steps from left to right, from top to bottom, specifically refers to the implementation plan according to the level of gradual indentation, from left to right, the step with the most indentation is executed first, if the indentation amount is the same, then according to the top-down method to judge the execution order.

You can know which step is more expensive by viewing the cost column of the execution plan, and whether the index in the table is used by viewing the objectname column in the row of the execution plan.

The SQL execution plan refers to what is done step by step when an SQL statement is actually executed in the database

1. First, open a SQL server

in the database management interface.

2. Then select the SQL statement to be executed.

3. Click on the menu in the query menu.

4. After clicking the query menu, a drop-down menu pops up to select the option to display the estimated execution plan.

5. Then click Execute Plan.

6. Duan Xiao can see the relevant data of the relevant execution plan.

SQL execution plan is what an SQL statement does step by step when it is actually executed in the database.

The execution plan is a tree composed of various operators, that is, the presentation form of the sorted operators, which is executed in order from the inside to the outside.

The general execution plan format is:

op1op2

op3op4

op5op6

op7op8

The more indents, the more they are executed first, and the top of the same stool is executed first, and the lower ones are executed later, and the upper and lower ones are given higher priority than the inside and outside.

op3->op4->op2->op7->op8->op6->opt5->op1

An SQL statement says what you want to get the keys but doesn't tell the server how to do it. For example, with a single SQL statement, you might want to server fetch all the customers who live in Prague. When the server receives this SQL, the first thing it does is not parse it.

If there are no syntax errors, the server will continue to work. The server decides the best way to compute it. The server will choose whether to read the entire client table or use the index faster.

The server compares the resources consumed by all possible methods. The method in which the SQL statement is physically executed is called an execution plan or query plan.

An execution plan is made up of a number of basic operations on the right. For example, traversing an entire table, leveraging indexes, performing a nested loop or hash join, and so on. We'll discuss this in more detail in this series of articles.

All basic digital operations have one output: the result set. Some, like nested loops, have an input.

Others, like hash connections, have two inputs. Each input should be connected to the output of the other basic operations. This is why an execution can be considered a number

Faith and defeat flow from the leaves to the roots. There are many examples of this in the lower part of the article.

The DB Server component that is responsible for processing or calculating the optimal execution plan is called the optimizer. Optimizers work on top of the db resources they reside.

To put it bluntly, the database server will make several sets of execution plans before executing SQL statements! Look at that opportunity to consume less system resources, just use that set of plans!

SQL execution plan refers to the view of a SQL statement in the database when it is actually executed, step by step to distinguish what the balance has done, the operation steps of Guyou to do the database to view the execution plan are as follows:

1. First of all, open a sqlserver database management interface.

2. Then select the SQL statement to be executed.

3. Click on the menu in the query menu.

4. After clicking the query menu, the drop-down menu pops up to select the option to display the estimated execution plan.

5. Then click Execute Plan.

6. You can see the relevant data of the relevant execution plan.

How to obtain the execution plan of an SQL statement.

The display cursor function in the Oracle10G DBMS xplan package is different from the display function, and the display cursor is used to display the real execution plan of the SQL statement, and in most cases, the display of the real execution plan is helpful to better analyze the whole process of the SQL statement, especially the real-time cost of running the SQL statement. It is judged by comparing the estimated IO cost with the IO cost of the real auspicious silver hailstone.

Problems with SQL statements, such as the lack of statistics, the number of SQL statements executed, and the selection of appropriate connection methods based on the size of the actual intermediate result set.

How Do I Obtain the Execution Plan of an SQL Statement?

The display cursor function in the Oracle 10G DBMS xplan package is different from the display function, the display cursor is used to display the actual execution plan of the SQL statement, and in most cases, the display of the real execution plan is helpful to better analyze the full execution of the SQL statement, especially the real-time cost of running the SQL statement. This is determined by comparing the estimated IO with the actual IO cost.

Problems with SQL statements, such as the lack of statistical information, the number of times SQL statements are executed, and the connection mode of the combination is selected according to the size of the actual intermediate result set.

sqlserver2012

First, let's execute an SQL statement, and you can see that there is no execution plan page in the output result column.

Then we click on the query menu, and in the drop-down menu we select the option "Show estimated execution plan", as shown in the image below.

At this time, when you view the output result column, you will see that there are more execution plan pages, as shown in the following figure.

Let's execute two SQL statements, as shown in the figure below, and then we will use these two SQL statements to show how to use the execution plan function.

After we have executed the above SQL statement, we will see the execution plan as shown in the following figure on the execution plan page, and sqlserver has generated the corresponding execution plan for us.

Let's first look at the execution plan of the first SQL statement, as shown in the following figure, which shows the resource consumption of the SQL statement.

Then observe the second execution plan, and you will find that the execution efficiency of the second SQL statement is higher, which is more obvious in the case of a large amount of data.