How to get a real execution plan

In general, we can use the following four methods to get the execution plan of the target SQL:

(1) explain plan command

(2) Dbms_xplan bag

(3) Autotrace switch in Sqlplus

(4) 10046 events

In addition to the fourth method, the execution plan obtained by the other three methods may be inaccurate. The accuracy of the execution plan in the Oracle database is to see if the target SQL is actually executed, and the execution plan for the actual executed SQL is accurate, and the reverse may not be allowed. Note that the principle of judgment here is not strictly applicable to the autotrace switch, as all execution plans shown using the Autotrace switch may not be allowed, even if the corresponding target SQL is actually executed.

We will use the above principles to determine which of the other three methods in addition to the 4th one are subject to the implementation plan, and which methods of implementation may not be allowed.

For the execution plan obtained using the first method (explain plan), because the target SQL is not actually executed at this time, it is possible that the execution plan obtained with this method is not allowed, especially when the target SQL contains the bound variable. With the default turn on BIND variable snooping (bind peeking), target SQL with bound variables using explain plan to get the execution plan is just a semi-finished product, and Oracle then snooping on the SQL's bound variable to get the specific values of these binding variables, At this point, Oracle is likely to be adjusted to the implementation plan of the above-mentioned semi-finished product, and once the adjustment is made, the execution plan obtained using the explain Plan command is not allowed.

For the second approach, for different scenarios, you can choose one of the following four ways:

SELECT * FROM table (Dbms_xplan.display)

SELECT * FROM table (Dbms_xplan.display_cursor (Null,null, ' advanced ')

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

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

Obviously, the execution plan for executing select * from table (dbms_xplan.display) may be inaccurate because it only has the execution plan to view the target SQL obtained using the explain Plan command, and the target SQL is not actually executed at this time , so the execution plan that you get with it may not be allowed. The execution plan obtained using the remaining three methods is accurate, because the target SQL has been actually executed.

For the use of the third method (autotrace switch in sqlplus), you can choose to perform one of the following three ways to turn on the autotrace switch

Set Autotrace on (set Antot on)

Set Autotrace traceonly (set Autot trace)

Set Autotrace traceonly explain (set Autot trace exp)

In these three ways, when using set Autotrace on and set Autotrace traceonly, the target SQL has been actually executed, because it was actually executed, so set autotrace on and set Autotrace In the case of traceonly we can see the actual resource consumption of the target SQL. When using Set Autotrace traceonly explain yes, if the SELECT statement is executed, the SELECT statement is not actually executed by Oracle, but if you are executing a DML statement, the situation is different. The DML statement at this point is actually executed by the actual Oracle.

We are now going to prove the idea of set Autotrace traceonly explain. Execute the following SQL normally once:

Sql> Select COUNT (*) from EMP where ename= ' JAMES ';

COUNT (*)

---------- 1

As you can see from the following query results, the value of executions for the above SQL corresponds to 1, which means that Oracle has just performed one of these SQL

Sql> Select Sql_text,executions from V$sqlarea where Sql_text like ' select COUNT (*) from emp% ';

Sql_text Executions

-------------------------------------------------------------------------------- ----------

Select COUNT (*) from EMP where ename= ' JAMES ' 1

Now empty the shared pool

Sql> alter system flush Shared_pool;

System altered.

As you can see from the following query results, the shared cursor for the above SQL is now out of the shared pool.

Sql> Select Sql_text,executions from V$sqlarea where Sql_text like ' select COUNT (*) from emp% ';

No rows selected

Execute the above SQL after opening Autotrace traceonly explain mode in the current session

Sql> set Autotrace traceonly explain;

Sql> Select COUNT (*) from scott.emp where ename= ' JAMES '

2;

Execution Plan

----------------------------------------------------------

Plan Hash value:2083865914

---------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT |  | 1 | 6 | 3 (0) | 00:00:01 |

|  1 | SORT AGGREGATE |  | 1 | 6 |       |  |

|* 2 | TABLE ACCESS full| EMP | 1 | 6 | 3 (0) | 00:00:01 |

---------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-filter ("ename" = ' JAMES ')

We re-query V$sqlare

Sql> Select Sql_text,executions from V$sqlarea where Sql_text like ' select COUNT (*) from scott.emp% ';

Sql_text Executions

-------------------------------------------------------------------------------- ----------

Select COUNT (*) from scott.emp where ename= ' JAMES ' 0

As you can see from the query results above, the executions for this SELECT statement is 0, which means that Oracle just did parse the SELECT clause but did not actually execute them. Proof of the above view (when using Set Autot trace exp, if you are executing a SELECT statement, the SELECT statement is not actually executed by Oracle)

Next, execute the following DML statement in the current session:

Sql> Delete from scott.emp where ename= ' JAMES ';

1 row deleted.

Execution Plan

----------------------------------------------------------

Plan Hash value:161811703

---------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

---------------------------------------------------------------------------

| 0 | DELETE STATEMENT |  |     1 | 13 | 3 (0) | 00:00:01 |

|  1 | DELETE | EMP |  |  |       |  |

|* 2 | TABLE ACCESS full| EMP |     1 | 13 | 3 (0) | 00:00:01 |

---------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-filter ("ename" = ' JAMES ')

As you can see from the query results, the above DML statements have been really executed:

Sql> Select COUNT (*) from scott.emp where ename= ' JAMES ';

COUNT (*)

----------

0

Sql> Select Sql_text,executions from V$sqlarea where sql_text like ' delete from scott.emp% ';

Sql_text Executions

-------------------------------------------------------------------------------- ----------

Delete from scott.emp where ename= ' JAMES ' 1

From the above example we can see the use of Set Autotrace traceonly explain after executing the DML statement, the DML statement is actually executed by Oracle, so the use of set Autotrace On,set autotrace Traceonly and set Autotrace traceonly explain to get the execution plan for DML statements, be careful because these DML statements are actually executed.

It is particularly stated here that although the target SQL was actually executed after using the set Autot command, all use the Set autotrace command (including set Autotrace On,set autotrace Traceonly,set Autotrace Traceonly explain) The resulting execution plan is probably not allowed because the execution plan shown using the Set autotrace command is derived from the call to the explain plan command.

Let's take a look at an execution plan that was obtained using the explain Plan command and the set autotrace command is not an instance of the target SQL Real execution plan. Create a test table T1 and insert some data:

Sql> CREATE TABLE T1 as select * from Dba_objects;

Table created.

sql> INSERT INTO T1 select * from T1;

87205 rows created.

Sql> commit;

Commit complete.

Now the amount of data in table T1 is over 170,000.

Sql> Select COUNT (*) from T1;

COUNT (*)

----------

174410

Create a B-tree index of a single-key value on the column object_id of table T1 idx_t1

Sql> CREATE index idx_t1 on T1 (object_id);

Index created.

Collect a statistic for table T1

sql> exec dbms_stats.gather_table_stats (ownname=> ' SYS ',tabname=> ' T1 ', estimate_percent=>100,cascade= >true);

PL/SQL procedure successfully completed.

Create two binding variables x and y, assigning them values of 0 and 100000, respectively

sql> var x number;

sql> var y number;

Sql> exec:x=0;

Sql> exec:x:=0;

PL/SQL procedure successfully completed.

Sql> exec:y:=100000;

PL/SQL procedure successfully completed.

Use explain plan to generate the following SQL execution plan:

Sql> explain plan for select COUNT (*) from T1 where object_id between:x and:y;

explained.

Sql> select * FROM table (dbms_xplan.display);

Plan_table_output

-------------------------------------------------------------------------------------

Plan Hash value:2351893609

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT |    | 1 | 5 | 3 (0) | 00:00:01 |

|  1 | SORT AGGREGATE |    | 1 | 5 | |    |

|* 2 | FILTER |    |    |    | |    |

|* 3 | INDEX RANGE scan| Idx_t1 |   436 | 2180 | 3 (0) | 00:00:01 |

-----------------------------------------------------------------------------

Plan_table_output

-------------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-filter (To_number (: Y) >=to_number (: X))

3-access ("object_id" >=to_number (: X) and "object_id" <=to_number (: Y))

Rows selected.

As can be seen from the above results, the execution plan that is obtained using the explain Plan command shows that the target SQL goes to the index IDX_T1 index range scan.

But what is the actual situation? We actually execute the SQL:

Sql> exec:x:=0;

PL/SQL procedure successfully completed.

Sql> exec:y:=10000;

PL/SQL procedure successfully completed.

Sql> Select COUNT (*) from T1 where object_id between:x and:y;

COUNT (*)

----------

19610

Sql> select * FROM table (Dbms_xplan.display_cursor (Null,null, ' advanced '));

Plan_table_output

-------------------------------------------------------------------------------------------

sql_id9dhu3xk2zu531, child number 0

-------------------------------------

Select COUNT (*) from T1 where object_id between:x and:y

Plan Hash value:1410530761

---------------------------------------------------------------------------------

| Id | Operation | Name| Rows| Bytes | Cost (%CPU) | Time|

---------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | |   | 107 (100) | |

|  1 | SORT AGGREGATE |     |     1 | 5 |     | |

Plan_table_output

-------------------------------------------------------------------------------------------

|* 2 | FILTER | | | |     | |

|* 3 | INDEX FAST Full scan|   Idx_t1 |   174k|   851k| 107 (1) | 00:00:01 |

---------------------------------------------------------------------------------

Selected rows.

As you can see from the above display, the execution plan of the target SQL is actually going to be the index IDX_T1 fast full scan, which is the target SQL Real execution plan, and several execution plans just using the explain Plan command are not accurate.

The same method can get the execution plan obtained by using the set Autotrace on method is also not accurate.

How to get a real execution plan