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

时间:2022-10-01 17:23:39

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