SQL> explain plan for
2
2 SELECT *
3 FROM scott.emp
4 WHERE NOT EXISTS (SELECT 0
5 FROM scott.dept
6 WHERE dept.dname = 'SALES' AND dept.deptno = emp.deptno)
7 AND NOT EXISTS (SELECT 0
8 FROM scott.bonus
9 WHERE bonus.ename = emp.ename);
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 734347697
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 522 | 9 (12)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 9 | 522 | 9 (12)| 00:00:01 |
|* 2 | HASH JOIN ANTI | | 9 | 459 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DEPT | 1 | 13 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | BONUS | 1 | 7 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("BONUS"."ENAME"="EMP"."ENAME")
2 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
4 - filter("DEPT"."DNAME"='SALES')
使用advanced也不显示
SQL> explain plan for select * from scott.emp;
Explained
SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 532 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 532 | 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")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
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
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
"EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22],
"EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
"EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
要得到starts要么在SQLPLUS中将statistics_level设置all，并真实执行sql语句
因为display_cursor是从动态性能视图v$session,v$sql,v$sql_plan和v$sql_plan_statistics_all中获取直接计划的使用explain plan for不行
因为它只生成执行计划并没真正地执行而且执行计划是存储在plan_table表中
并使用display来显示
要么在要执行的sql语句中statistics_level 仍然保留’typical’ ，
然后用这个HINT/*+gather_plan_statistics */
SQL> alter session set statistics_level=all;
会话已更改。
SQL> select count(*) from scott.emp;
COUNT(*)
———-
14
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID abj9tmfcs15bm, child number 0
-------------------------------------
select count(*) from scott.emp
Plan hash value: 2937609675
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | INDEX FULL SCAN| PK_EMP | 1 | 14 | 14 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------
已选择14行。
SQL>
SQL> alter session set statistics_level='typical';
会话已更改。
SQL> select /*+gather_plan_statistics */ count(*) from scott.emp;
COUNT(*)
----------
14
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 2vku9s3sb55tz, child number 0
-------------------------------------
select /*+gather_plan_statistics */ count(*) from scott.emp
Plan hash value: 2937609675
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | INDEX FULL SCAN| PK_EMP | 1 | 14 | 14 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------
已选择14行。