A simple comparison of two ways to view execution plans explain plan and AUTOTRACE

Both EXPLAIN plan and AUTOTRACE can view the execution plan. It is worth mentioning that the former is only the optimizer by reading the statistics of the data dictionary to make the ' best ' access path judgment, and does not really go to execute the statement , the latter is actually to execute the SQL statement, while the number of access records, execution plan, statistics and other printing out.

The following results are illustrated and the time taken to compare them :

<p>SQL> connect/as SYSDBA connected.</p><p>sql> SET linesize 300;
Sql> SET TIMING on;
Sql> SET PAGESIZE; Sql> EXPLAIN PLAN for SELECT COUNT (*) from Dba_objects, dba_objects;</p><p>explained.</p><p >elapsed:00:00:00.03 <<<<<<<<<<<<<<<<<<<<& lt;<<<<<<<<<<<<<<<<<<<<<<<<<< << consumes only 0.03 seconds sql> sql> SELECT * from TABLE (Dbms_xplan.
DISPLAY); Plan Hash value:2343274122</p><p>------------------------------------------------------------------- ------------------------------
| id  | operation     | name | rows | Bytes | Cost (%CPU) |
time | -------------------------------------------------------------------------------------------------|   0 | SELECT statement    |  |     1 | |  7187k  (5) | 23:57:25 | |   1 |  SORT AGGREGATE     |  |     1 | |& nbsp;     |  | |   2 |   MERGE JOIN cartesian    |   |  2325m| |  7187k  (5) | 23:57:25 | |   3 |    VIEW      | dba_objects | 48221 | |   149   (5) | 00:00:02 | |   4 |     union-all     |  | | |       |  | |*  5 |      filter      |  | | |      |  | |*  6 |        HASH JOIN     |  | 51192 |  4099k|   148   (5) | 00:00:02 | |   7 |        TABLE ACCESS full   | user$ |    |   177 |     2   (0) | 00:00:01 | |*  8 |        TABLE ACCESS full   | obj$ | 51192 |  3949k|   145   (5) | 00:00:02 | |*  9 |       TABLE ACCESS by INDEX ROWID | ind$ |     1 |     8 |     2   (0) | 00:00:01 | |* |        INDEX UNIQUE scan   | i_ind1 |     1 | |     1   (0) | 00:00:01 | |  |      NESTED loops    |  |      1 |    |     1   (0) | 00:00:01 | |  |       INDEX full scan    | i_link1 |     1 |    |     0   (0) | 00:00:01 | |  |       TABLE ACCESS cluster   | user$ |     1 |     3 |     1   (0) | 00:00:01 | |* |        INDEX UNIQUE scan   | i_user# |     1 | |     0   (0) | 00:00:01 | |  |    BUFFER sort     |  | 48221 | |  7187k  (5) | 23:57:25 | |  |     view     | dba_objects | 48221 | |   149   (5) | 00:00:02 | |  |      union-all     |  | | |       |  | |* |       filter      |&NBSP;&NBSP;|&NBSP;|&NBSP;|&NBsp;     |  | |* |        HASH JOIN     |  | 51192 |  4099k|   148   (5) | 00:00:02 | |  20 |  table ACCESS full   | user$ |    |   177 |     2   (0) | 00:00:01 | |* 21 |  table ACCESS full   | obj$ | 51192 |  3949k|   145   (5) | 00:00:02 | |* |        TABLE ACCESS by INDEX rowid| ind$ |     1 |     8 |     2   (0) | 00:00:01 | |* 23 |  index UNIQUE scan   | i_ind1 |     1 | |     1   (0) | 00:00:01 | |  |       NESTED loops    |  |      1 |    |   &nbsP 1   (0) | 00:00:01 | |  |        INDEX full scan    | i_link1 |     1 |    |     0   (0) | 00:00:01 | |  |        TABLE ACCESS cluster   | user$ |     1 |     3 |     1   (0) | 00:00:01 | |* 27 |  index UNIQUE scan   | i_user# |     1 | |     0   (0) |
00:00:01 | -------------------------------------------------------------------------------------------------</p> <p>predicate information (identified by Operation ID):---------------------------------------------------< /p><p>   5-filter ("O". " type# "<>1 and" O "." type# "<>10 OR" O "." type# "=1 and  (select 1 from       " SYS "." ind$ "" I "WHERE" I "." Obj# "=:b1 and (" I "." type# "=1 OR" I "." type# "=2 OR       " I "." type# "=3 OR" I "." type# "=4 OR" I "." type# "=6 OR" I "." type# "=7 OR" I "." type# "=9)) =1)    6-access (" O "." owner# "=" U "." user# ")    8-filter (Bitand (" O ".") FLAGS ", =0) and" O "." NAME "<> ' _next_object ' and       " O "." NAME "<> ' _default_auditing_options_ ' and" O "." Linkname "is NULL"    9-filter ("I". " type# "=1 OR" I "." type# "=2 OR" I "." type# "=3 OR" I "." type# "=4 OR       " I "." type# "=6 OR" I "." type# "=7 OR" I "." type# "=9"   10-access ("I". " obj# "=:B1"   14-access ("L". ") owner# "=" U "." user# ")   18-filter (" O "." type# "<>1 and" O "." type# "<>10 OR" O "." type# "=1 and  (select 1 from       " SYS ".) ind$ "" I "WHERE" I "." Obj# "=:b1 and (" I "." type# "=1 OR" I "." type# "=2 OR       " I "." type# "=3 OR" I "." type# "=4 OR"I". " type# "=6 OR" I "." type# "=7 OR" I "." type# "=9)) =1)   19-access (" O "." owner# "=" U "." user# ")   21-filter (Bitand (" O ".") FLAGS ", =0) and" O "." NAME "<> ' _next_object ' and       " O "." NAME "<> ' _default_auditing_options_ ' and" O "." Linkname "is NULL"   22-filter ("I". " type# "=1 OR" I "." type# "=2 OR" I "." type# "=3 OR" I "." type# "=4 OR       " I "." type# "=6 OR" I "." type# "=7 OR" I "." type# "=9"   23-access ("I". " obj# "=:B1"   27-access ("L". ") owner# "=" U "." user# ") </p><p>58 rows selected.</p><p>elapsed:00:00:00.04</p>

 

<p>SQL> SET autotrace;        Usage:set autot[race] {OFF | On |
Trace[only]} [Exp[lain]] [stat[istics]] sql> SET AUTOTRACE on;
Sql> SELECT COUNT (*) from dba_objects, dba_objects; 2508707569 <<<<<<<<<<<<<<<<<<<<<<< <<<<<<<<<<<<<<<<<<<<<<<<<<< <<<<<count () </p><p>Elapsed:00:01:41.97           <<<<<<<<<<<<<<<<<<<<<<<< <<<<<<<<<<<<<<<<<<<<<<<<<<< <<<< consumption 1 minutes 41.97 seconds </p><p>execution Plan---------------------------------------------------- ------Plan Hash value:2343274122</p><p>-------------------------------------------------------------------------------------------------
| id  | operation     | name | rows | Bytes | Cost (%CPU) |
time | -------------------------------------------------------------------------------------------------|   0 | SELECT statement    |  |     1 | |  7187K  (5) | 23:57:25 | |   1 |  SORT AGGREGATE     |  |     1 | |& nbsp;     |  | |   2 |   MERGE JOIN cartesian    |   |  2325m| |  7187k  (5) | 23:57:25 | |   3 |    VIEW      | dba_objects | 48221 | |   149   (5) | 00:00:02 | |   4 |     union-all     |  | | |       |  | |*  5 |&NBSP;&NBSP;&NBsp;   filter     |  | | |      |   | |*  6 |       HASH JOIN     |  | 51192 |  4099k|   148   (5) | 00:00:02 | |   7 |        TABLE ACCESS full   | user$ |    |   177 |     2   (0) | 00:00:01 | |*  8 |        TABLE ACCESS full   | obj$ | 51192 |  3949k|   145   (5) | 00:00:02 | |*  9 |       TABLE ACCESS by INDEX ROWID | ind$ |     1 |     8 |     2   (0) | 00:00:01 | |* |        INDEX UNIQUE scan   | i_ind1 |     1 | |     1   (0) | 00:00:01 | |  |      NESTED loops    |  |      1 |    |     1   (0) | 00:00:01 | |  |       INDEX full scan    | i_link1 |     1 |    |     0   (0) | 00:00:01 | |  |       TABLE ACCESS cluster   | user$ |     1 |     3 |     1   (0) | 00:00:01 | |* |        INDEX UNIQUE scan   | i_user# |     1 | |     0   (0) | 00:00:01 | |  |    BUFFER sort     |  | 48221 | |  7187k  (5) | 23:57:25 | |  |     view     | dba_objects | 48221 | |   149   (5) | 00:00:02 | |  |      union-all     |  | | |       |  | |* |       filter      |  | | |      |  | |* |         HASH join    |  | 51192 |  4099k|   148   (5) | 00:00:02 | |  20 |  table ACCESS full   | user$ |    |   177 |     2   (0) | 00:00:01 | |* 21 |  table ACCESS full   | obj$ | 51192 |  3949k|   145   (5) | 00:00:02 | |* |        TABLE ACCESS by INDEX rowid| ind$ |     1 |     8 |&NBSP;&NBSP;&NBSP;&NBSp 2   (0) | 00:00:01 | |* 23 |  index UNIQUE scan   | i_ind1 |     1 | |     1   (0) | 00:00:01 | |  |       NESTED loops    |  |      1 |    |     1   (0) | 00:00:01 | |  |        INDEX full scan    | i_link1 |     1 |    |     0   (0) | 00:00:01 | |  |        TABLE ACCESS cluster   | user$ |     1 |     3 |     1   (0) | 00:00:01 | |* 27 |  index UNIQUE scan   | i_user# |     1 | |     0   (0) |
00:00:01 | -------------------------------------------------------------------------------------------------</p><p>predicate information (identified by Operation ID):---------------------------------------------------</p><p>   5-filter ("O". " type# "<>1 and" O "." type# "<>10 OR" O "." type# "=1 and  (select 1 from       " SYS ".) ind$ "" I "WHERE" I "." Obj# "=:b1 and (" I "." type# "=1 OR" I "." type# "=2 OR       " I "." type# "=3 OR" I "." type# "=4 OR" I "." type# "=6 OR" I "." type# "=7 OR" I "." type# "=9)) =1)    6-access (" O "." owner# "=" U "." user# ")    8-filter (Bitand (" O ".") FLAGS ", =0) and" O "." NAME "<> ' _next_object ' and       " O "." NAME "<> ' _default_auditing_options_ ' and" O "." Linkname "is NULL"    9-filter ("I". " type# "=1 OR" I "." type# "=2 OR" I "." type# "=3 OR" I "." type# "=4 OR       " I "." type# "=6 OR" I "." type# "=7 OR" I "." type# "=9"   10-access ("I")."obj#" =:b1)   14-access ("L". " owner# "=" U "." user# ")   18-filter (" O "." type# "<>1 and" O "." type# "<>10 OR" O "." type# "=1 and  (select 1 from       " SYS ".) ind$ "" I "WHERE" I "." Obj# "=:b1 and (" I "." type# "=1 OR" I "." type# "=2 OR       " I "." type# "=3 OR" I "." type# "=4 OR" I "." type# "=6 OR" I "." type# "=7 OR" I "." type# "=9)) =1)   19-access (" O "." owner# "=" U "." user# ")   21-filter (Bitand (" O ".") FLAGS ", =0) and" O "." NAME "<> ' _next_object ' and       " O "." NAME "<> ' _default_auditing_options_ ' and" O "." Linkname "is NULL"   22-filter ("I". " type# "=1 OR" I "." type# "=2 OR" I "." type# "=3 OR" I "." type# "=4 OR       " I "." type# "=6 OR" I "." type# "=7 OR" I "." type# "=9"   23-access ("I". " obj# "=:B1"   27-access ("L". ") owner# "=" U "." user# ") </p><p> Statistics----------------------------------------------------------  15&NBSp Recursive calls    0  db block gets       10208  consistent gets  &nbsp ; 0  physical reads    0  Redo size  519  bytes sent via sql*net to client  492  byt Es received via sql*net from client    2  sql*net roundtrips to/from client    1  sorts (mem Ory)    0  sorts (disk)    1  rows processed <<<<<<<<<<< <<<<<<<<<<<<<<<<<<<<<<< returns 1 result sets </p>

Therefore, EXPLAIN plan often fails to reflect the actual execution plan, especially in the application of bound variables. Plsql/developer F5 can quickly see the execution plan, in fact, is explain plan method.

If you want to know the actual execution plan of the SQL statement, there are several possible ways to do this:

o SET auotrace

o 10046

o Dbms_xplan. Display_coursor or Dbms_xplan. Display_awr

o Query V$sql_plan