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.

DRIVING_SITE Hint

If we are joining local table with remote table, then by default, execution will happens at locally, and optimizer wont utilize remote table statistics, it will do full table scan.

  

The DRIVING_SITE hint lets you specify the site where the query execution is performed.  It is best to let cost-based optimization determine where the execution should be performed, but if you prefer to override the optimizer, you can specify the execution site manually.

Example: 

SELECT /*+ DRIVING_SITE(dept)*/ * FROM emp, dept@remote.com dept

WHERE emp.deptno = dept.deptno;