Common Methods for viewing Oracle execution plans-Series 1

Common Methods for viewing Oracle execution plans-Series 1

The SQL Execution Plan actually represents the specific execution steps of the target SQL in the Oracle database, only by knowing whether the execution plan selected by the optimizer is the optimal execution plan in the current situation can you know the next step.

Execution Plan Definition: A combination of all steps for executing the target SQL statement.

First, we will list some common methods for viewing execution plans:

1. explain plan command

In PL/SQL Developer, you can use the shortcut key F5 to view the execution plan of the target SQL statement. However, after pressing F5, the actual backend call is the explain plan command, which is equivalent to encapsulating the command.

How to Use the explain plan:

(1) execute explain plan for + SQL

(2) execute select * from table (dbms_xplan.display );

Prepare the experiment table:

SQL> desc test1;
Name Null Type
-----------------------------------------------------------------------------
T1ID not null number (38)
T1V VARCHAR2 (10)

SQL> desc test2;
Name Null Type
-----------------------------------------------------------------------------
T2ID not null number (38)
T2V VARCHAR2 (10)

Lab:

SQL> set linesize 100

SQL> explain plan for select t1id, t1v, t2id, t2v from test1, test2 where test1.t1id = test2.t2id;

Explained.

The first step is to use the explain plan to explain the target SQL statement. The second step is to use the select * from table (dbms_xplan.display) statement to show the execution plan of the SQL statement.

Here, test2 is used as the drive table and full table scan is performed. test1 is used as the drive table. because it contains the primary key, full index scan is used. Step 4 with ID * on the left indicates that there are predicate conditions. here we can see that both primary key index (access) and filter are used ).

2. DBMS_XPLAN package

(1) select * from table (dbms_xplan.display); -- as described above.

(2) select * from table (dbms_xplan.display_cursor (null, null, 'advanced '));

(3) select * from table (dbms_xplan.display_cursor ('SQL _ id/hash_value', child_cursor_number, 'advanced '));

(4) select * from table (dbms_xplan.display_awr ('SQL _ id '));

(2) select * from table (dbms_xplan.display_cursor (null, null, 'advanced '));

It is mainly used to view the execution plan of the SQL statement that has just been executed in SQLPLUS. First, you can select 'advanced 'as the third parameter ':

Next, the third parameter uses 'all ':

It can be seen that the 'advanced 'record has more information than 'all', mainly because there is one more Outline Data. Outline Data is an internal HINT combination used for fixed execution plans during SQL Execution. This part can be extracted and added to the target SQL statement to fix the execution plan.

(3) select * from table (dbms_xplan.display_cursor ('SQL _ id/hash_value', child_cursor_number, 'advanced '));

The first parameter can be entered with the SQL _id or hash value of the SQL statement. If the executed SQL statement is still in the database cache, you can use the V $ SQL statement to query the SQL statement:

Here, the SQL statement using @ dbsnake can know the one-to-one correspondence between SQL _ID and HASH_VALUE:

Hide Question 1:

There may be some problems here, and the results are not accurate. The problem lies in the algorithm used in this SQL statement. In another blog post, I will explain this problem carefully.

Usage:

SQL> select * from table (dbms_xplan.display_cursor ('1p2fk2v00c865 ', 0, 'advanced '));

Or

Select * from table (dbms_xplan.display_cursor ('20140901', 0, 'advanced '));

You can find the execution plan corresponding to this SQL statement. The content is the same as the 'advanced 'in (2), which is not displayed.

Note that the second parameter child_cursor_number refers to the subcursor number. If no new subcursor is generated, 0 is entered here.

(2) The conclusion is similar to that in (3). The difference is that (2) only view the execution plan for the last SQL Execution. (3) you can view the execution plan for any SQL statement that is still in the database cache.

(4) select * from table (dbms_xplan.display_awr ('SQL _ id '));

(1) explain plan for + SQL is used as the premise, (2) and (3) the premise is that the SQL Execution plan is still in the shared pool, specifically, it is in the database cache. If the shared pool has been switched out by age out, the two methods cannot be used. If the SQL Execution Plan is collected to the AWR database, you can use (4) to query the historical execution plan.

Hide Question 2:

This experiment shows that the use of select * from table (dbms_xplan.display_awr ('SQL _ id') has no results. @ Huang Wei said that AWR may collect top SQL statements, it is possible that the SQL used for testing is not most intensive SQL, but I used the manual snapshot collection executed after alter system flush shared_pool, or was not caught by AWR, which is a strange problem, this will also be carefully described in another blog.

Not complete...

To be continued...