GATHER_PLAN_STATISTICS hint and dbms_xplan

In order to get detailed information about how many rows are being returned in each step of the execution plan, the hint GATHER_PLAN_STATISTICS comes in very handy, see the simple example below:

SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.ename  = 'FRANK';

 You actually have to run the above query, to be able to see that run stats.


SET LINE 200 pages 80

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      1 |00:00:00.01 |       8 |
|   1 |  NESTED LOOPS                |         |      1 |        |      1 |00:00:00.01 |       9 |
|   2 |   NESTED LOOPS               |         |      1 |      1 |      1 |00:00:00.01 |       8 |
|*  3 |    TABLE ACCESS FULL         | EMP     |      1 |      1 |      1 |00:00:00.01 |       6 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |      1 |00:00:00.01 |       1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------

The output above shows the estimated rows returned (E-Rows), the actual rows returned (A-Rows) and the actual time (A-Time) spent during each step of the execution.