Common ways to view Oracle SQL execution plans

There are many ways to view the SQL execution plan

There are three types of methods I use

Sql> explain plan for
2 Select * from scott.emp where ename= ' KING ';

has been explained.

The first of the most commonly used

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

PlanHash value:3956160932--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost (%CPU)|Time|--------------------------------------------------------------------------|   0 | SELECTSTATEMENT|      |     1 |     - |     3(0)| xx:xx: on ||*  1 |  TABLEACCESS Full|Emp|     1 |     - |     3(0)| xx:xx: on |--------------------------------------------------------------------------predicate information (identified byoperation ID):---------------------------------------------------   1 -Filter ("Ename"='KING'13 rows have been selected. 

The second Kind

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

PlanHash value:2637181423---------------------------------------------------|Id|Operation|Name|E-Rows|---------------------------------------------------|   0 | DELETESTATEMENT|             |        ||   1 |  DELETE            |plan_table$|        ||*  2 |   TABLEACCESS Full|plan_table$|      1 |---------------------------------------------------predicate information (identified byoperation ID):---------------------------------------------------   2 -Filter ("statement_id"=:1) Note-----   -Dynamic sampling used forThis statement ( Level=2)   -Warning:basicPlan Statistics  notAvailable. These is onlyCollected when:       *Hint'Gather_plan_statistics'  isUsed forThe statementor       *Parameter'Statistics_level'  is Set  to ' All', at sessionorSystem Level26 rows have been selected. 

Third Kind

Sql> select * FROM table (Dbms_xplan.display (null,null, ' advanced-projection '));

PlanHash value:3956160932--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost (%CPU)|Time|--------------------------------------------------------------------------|   0 | SELECTSTATEMENT|      |     1 |     - |     3(0)| xx:xx: on ||*  1 |  TABLEACCESS Full|Emp|     1 |     - |     3(0)| xx:xx: on |--------------------------------------------------------------------------Query Block Name/Object Alias (identified byoperation ID):-------------------------------------------------------------   1 -sel$1 /Emp@SEL$1Outline Data-------------  /*+ begin_outline_data Full (@ "sel$1" "EMP" @ "sel$1") outline_leaf (@ "sel$1") all_rows db_version      (' 11.2.0.1 ') Optimizer_features_enable (' 11.2.0.1 ') ignore_optim_embedded_hints end_outline_data*/predicate information (identified byoperation ID):---------------------------------------------------   1 -Filter ("Ename"='KING'32 rows have been selected. 

Common ways to view Oracle SQL execution plans