Oracle Performance Tuning – view execution plan

Time:2020-3-24

When optimizing Oracle query, we can find the reason for the slow SQL query and the corresponding optimization method by looking at the explanation plan of SQL statement. Specifically, there are three ways to view the SQL interpretation plan: the explain plan for command, PL / SQL developer, and Oracle SQL developer. Among them, the explain plan for command can be executed through sqlplus. When Oracle SQL developer installs the database, it should have been installed. If not, it can also be downloaded and installed on the Internet.
Oracle Performance Tuning - view execution plan

Explain plan for command

Log in to the database under sqlplus and execute the following command:

1) Explain plan for select * from dual; + ENTER
     2) Select * from table (DBMS [xplan. Display); + ENTER

An example is shown in the figure:
Oracle Performance Tuning - view execution plan

PL/SQL Developer

After writing a section of SQL code in PL / SQL developer, press F5, and PL / SQL developer will automatically open the execution plan window to display the execution plan of the SQL.
In fact, the functions of PL / SQL developer and Oracle SQL developer mentioned below are basically the same, and the usage is almost the same.

Oracle SQL Developer

As shown in the figure, Oracle SQL developer can also view the interpretation plan of the specified SQL. In addition, Oracle SQL developer also provides SQL optimization guidance (for example, which fields to create indexes and which types of indexes to create will improve the query speed), so we can optimize our database according to the optimization guidance.
Oracle Performance Tuning - view execution plan
Oracle Performance Tuning - view execution plan

Performance tuning according to execution plan

View total cost to get the overall impression of resource consumption

Generally speaking, the cost value corresponding to the first line of the execution plan reflects the total estimated cost of running this SQL. The total cost has no practical significance, but it can be compared with the total cost of SQL with the same logic and different execution plans. Generally, the execution plan with low cost is better.

Follow the method from left to right and from top to bottom to understand the implementation steps of the implementation plan

The execution plan shall be indented step by step according to the level. From left to right, the step with the most indents shall be executed first. If the indents are the same, the execution order shall be determined according to the top-down method, and the above steps can be roughly considered as priority. Each execution step has corresponding cost. From the level of single step cost and single step estimation result set (corresponding to rows / cardinality), we can analyze the access mode, connection sequence and connection mode of the table.

How to access the analysis table

There are two ways to access a table: table access full and index scan Scan), if there is a good selective index on the table, but a full table scan is carried out, and it is a full table scan of the large table, it indicates that there may be problems in the access mode of the table; if there is no appropriate index on the large table and a full table scan is carried out, it is necessary to analyze whether the index can be established, or whether a more appropriate table connection mode and connection order can be selected to improve efficiency.

Connection mode and order of analysis table

Table join order: it is the access order of which table is used as the drive table to join other tables.
Table connection method: simply speaking, it is the connection process when two tables get data that meets the conditions.

Be careful:
The execution plan seen here is only the possible execution mode before SQL operation. The actual execution time may be changed due to the different hardware and software environment. Moreover, the high cost execution plan may not run at a certain speed in actual operation. We usually need to combine the execution plan with the actual test run time to determine the quality of an execution plan.