如何能在执行计划中看到Starts-白红宇

如何能在执行计划中看到Starts

阅读量：2437 次

发布时间：2019-05-10

本文共 5213 字，大约阅读时间需要 17 分钟。

SQL> explain plan for

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

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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(*)

----------

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

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 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(*)

----------

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

--------------------------------------------------------------------------------

-----

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行。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26015009/viewspace-765106/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26015009/viewspace-765106/

你可能感兴趣的文章

想起一句话：”多加一层，就可以把问题解决了“

查看>>

PostgreSQL Page页结构解析（7）- B-Tree索引存储结构#3

PostgreSQL 源码解读（37）- 查询语句#22（查询优化-grouping_plan...

查看>>

PostgreSQL 源码解读（44）- 查询语句#29（等价类相关数据结构）

查看>>

PostgreSQL 源码解读（48）- 查询语句#33（query_planner函数#9）

查看>>

PostgreSQL 源码解读（45）- 查询语句#30（query_planner函数#6）

查看>>

PostgreSQL 源码解读（47）- 查询语句#32（query_planner函数#8）

查看>>