在查看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行。