GATHER_PLAN_STATISTICS hint

To gather execution statistics, set statistics_level = ALL at session level or add hint /*+ gather_plan_statistics */ into SQL. 

GATHER_PLAN_STATISTICS hint tells Oracle to collect execution statistics for a SQL statement. These execution statistics are then shown next to the original Optimizer estimates in the execution plan if you use the function DBMS_XPLAN.DISPLAY_CURSOR to display the plan. You also have to set the  FORMAT parameter to 'ALLSTATS LAST' (DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST')). The original Optimizer estimates are shown in the E-Rows column while the actual statistics gathered during execution are shown in the A-Rows column.

If we add the GATHER_PLAN_STATISTICS hint to our simple SQL statement we should be able to see the actual cardinality of each operation at execution time alongside the Optimizer estimates for each cardinality in the plan.

SELECT /*+ GATHER_PLAN_STATISTICS */ p.prod_name, SUM(s.quantity_sold)
FROM         sales s, products p
WHERE      s.prod_id =p.prod_id
AND            p.prod_desc = 'Envoy Ambassador'
GROUP By p.prod_name ;

The execution plan for the query is as follows:

 

The statistics in V$SQL_PLAN_STATISTICS are available for cursors that have been compiled with the STATISTICS_LEVEL initialization parameter set to ALL or using the GATHER_PLAN_STATISTICS hint.