使用 gather_plan_statistics hint 产生执行计划

首先sqlplus登陆当前用户
2、执行 运行 sql语句 ，在语句中加入 /*+ gather_plan_statistics */
例子： 执行 sql 语句 select /*+ gather_plan_statistics */ count(employee_id) from hr.employees where department_id=50;
得到结果 ：
COUNT(1)

45
3、执行 查看计划 select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS'));
得到结果：

PLAN_TABLE_OUTPUT

SQL_ID 4dxpaqxz24a4q, child number 0

select /*+ gather_plan_statistics */ count(employee_id) from
hr.employees where department_id=50
Plan hash value: 2271004725

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 | PLAN_TABLE_OUTPUT

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
|* 2 | INDEX RANGE SCAN| EMP_DEPARTMENT_IX | 1 | 45 | 45 |00:00:00.01 | 1 |

Predicate Information (identified by operation id):

2 - access("DEPARTMENT_ID"=50)

20 rows selected.
SQL>

转载于:https://my.oschina.net/u/3635497/blog/3085861