查看Oracle SQL执行计划的常用方式 - Roc.Sun

查看Oracle SQL执行计划的常用方式

在查看SQL执行计划的时候有很多方式

我常用的方式有三种

SQL> explain plan for

2 select * from scott.emp where ename=\'KING\';

已解释。

第一种 最常用的

SQL> select * from table(dbms_xplan.display);

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME"=\'KING\')

已选择13行。

第二种

SQL> select * from table(dbms_xplan.display_cursor(null,null,\'ALLSTATS LAST\'));

Plan hash value: 2637181423

---------------------------------------------------
| Id  | Operation          | Name        | E-Rows |
---------------------------------------------------
|   0 | DELETE STATEMENT   |             |        |
|   1 |  DELETE            | PLAN_TABLE$ |        |
|*  2 |   TABLE ACCESS FULL| PLAN_TABLE$ |      1 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATEMENT_ID"=:1)

Note
-----
   - dynamic sampling used for this statement (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint \'gather_plan_statistics\' is used for the statement or
       * parameter \'statistics_level\' is set to \'ALL\', at session or system level


已选择26行。

第三种

SQL> select * from table(dbms_xplan.display(null,null,\'ADVANCED -PROJECTION\'));

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / EMP@SEL$1

Outline 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 by operation id):
---------------------------------------------------

   1 - filter("ENAME"=\'KING\')

已选择32行。