Several common ways to view Oracle execution plans

The execution plan of SQL actually represents the specific execution steps of the target SQL within the Oracle database, and as a tuning, only know if the execution plan chosen by the optimizer is the best execution plan in the current situation, then we can know the direction of the next step.

Execution Plan definition: A combination of all the steps to execute a target SQL .

Let's start by listing some common ways to view execution plans:

1. Explain plan command

In PL/SQL developer, you can view the execution plan for the target F5 by using the shortcut key. But actually, after pressing F5, the actual background call is the explain Plan command, which is equivalent to encapsulating the command.

Explain plan how to use:

(1) Execute explain plan for + SQL

(2) Execute SELECT * FROM table (dbms_xplan.display);

Preparation of the experiment sheet:

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)

Experiment :

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 uses the explain plan to explain the target SQL, and the second step uses the SELECT * from table (Dbms_xplan.display) statement to demonstrate the SQL execution plan.

Here test2 as the driver table, a full table scan, test1 as the driver table, because it contains the primary key, so with the index full scan. The fourth step of the left ID with the * sign indicates that there is a predicate condition, where you can see that both the primary key index (access) is used and the filter condition (filter) is used.

2. Dbms_xplan bag

(1) SELECT * FROM table (dbms_xplan.display);--above to illustrate.

(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 '));

Used primarily in sqlplus to view the execution plan that has just executed SQL. First, the third parameter can select ' Advanced ':

Next, the third parameter uses ' all ':

You can see that ' advanced ' records more information than ' all ', mostly one more outline Data. Outline data is primarily an internal hint combination that is used to fix the execution plan when executing SQL, which can be added to target SQL to fix its execution plan .

(3) SELECT * FROM table (dbms_xplan.display_cursor (' Sql_id/hash_value ', Child_cursor_number, ' advanced ');

The first parameter can enter SQL SQL_ID or hash value by using the V$sql query if the executed SQL is still in the library cache:

The SQL of @dbsnake Daniel can be used to know the one by one correspondence between SQL_ID and Hash_value:

Hide Issue 1:

There may be a problem here, the result is not accurate, the problem is in this SQL used in the algorithm, in another blog post will carefully explain the problem .

Use:

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

Or

SELECT * FROM table (dbms_xplan.display_cursor (' 3221627077 ', 0, ' advanced '));

You can find out the execution plan corresponding to this SQL, the content is the same as (2) in the ' advanced ', this does not show.

Note that there is also a second parameter, Child_cursor_number, that refers to the child cursor number, which is written here as 0 if no new child cursor is generated.

(2) and (3) the conclusion is similar, the difference is (2) only for the most recent execution of SQL view execution plan, (3) You can view the execution plan for any SQL that is still in the library cache .

(4) SELECT * FROM table (Dbms_xplan.display_awr (' sql_id '));

(1) is the premise of using explain plan for +sql, (2) and (3) The premise is that the SQL execution plan is also in the shared pool, specifically in the library cache. If you have already swapped out a shared pool by age out, you cannot use either of these methods. If the SQL execution plan is captured in the AWR library, you can use (4) to query the historical execution plan.

Hide Issue 2:

This part of the experiment finds the use of select * FROM table (Dbms_xplan.display_awr (' sql_id ')), and there is no result, @ Yap says it is possible that AWR collects top SQL, which is likely to test SQL not most Intensive SQL, but I am using the ALTER system flush Shared_pool after the manual capture snapshot, or is not caught by awr, the more strange problem, this will be in another blog post carefully explained.

Several common ways to view Oracle execution plans