Boost Oracle Distributed Queries with the DRIVING_SITE Hint

In Oracle distributed queries, the DRIVING_SITE hint allows you to control whether the join is executed on the remote or the local database, which can significantly reduce the amount of data transferred over the network when the local result set is small.

How the hint works

When the hint is applied, Oracle tries to execute the join on the site specified by the hint. For SELECT statements it can reduce one round‑trip of data movement. The hint is ignored for DML and DDL statements; in those cases Oracle automatically drives the operation from the table’s owning database.

Test environment

Two tables are created: a remote table test_remote on a remote database and a local table test_local on the client database. Both tables are populated with a large number of rows and a rn column generated by rownum:

-- Remote side
create table test_remote as select rownum rn, a.* from user_objects a;
insert into test_remote select * from test_remote; -- repeat until 655360 rows
update test_remote set rn = rownum;
commit;

-- Local side
create table test_local as select rownum rn, a.* from user_objects a;
insert into test_local select * from test_local; -- repeat until 768 rows
update test_local set rn = rownum;
commit;

Performance comparison

Two queries are executed:

Without the hint, Oracle pulls the remote table to the local site and performs a hash join.

select count(*)
from test_local l, test_remote@to_s12 r
where l.rn = r.rn;

Execution time: ~0.93 seconds.

With the hint /*+driving_site(r)*/, the local table is sent to the remote site and the join is performed remotely.

select /*+driving_site(r)*/ count(*)
from test_local l, test_remote@to_s12 r
where l.rn = r.rn;

Execution time: ~0.34 seconds.

EXPLAIN PLAN output shows the first query uses a remote table access followed by a hash join on the local side, while the second query executes the join entirely on the remote side, eliminating the large data transfer.

Average timing over 100 runs

PL/SQL blocks measure the average CPU time for each approach:

-- Remote table pulled to local
DECLARE
  n_sum_time NUMBER := 0;
BEGIN
  FOR i IN 1..100 LOOP
    n_sum_time := n_sum_time + (dbms_utility.get_cpu_time -
      dbms_utility.get_cpu_time);
    SELECT COUNT(*) INTO n_count
    FROM test_local l, test_remote@to_s12 r
    WHERE l.rn = r.rn;
  END LOOP;
  dbms_output.put_line('avg cpu_time:'||(n_sum_time/100));
END;

Result: average ≈ 65.71 (1/100 s).

-- Local table sent to remote
DECLARE
  n_sum_time NUMBER := 0;
BEGIN
  FOR i IN 1..100 LOOP
    n_sum_time := n_sum_time + (dbms_utility.get_cpu_time -
      dbms_utility.get_cpu_time);
    SELECT /*+driving_site(r) */ COUNT(*) INTO n_count
    FROM test_local l, test_remote@to_s12 r
    WHERE l.rn = r.rn;
  END LOOP;
  dbms_output.put_line('avg cpu_time:'||(n_sum_time/100));
END;

Result: average ≈ 0.05 (1/100 s), confirming the hint’s benefit.

Conclusion

When one table is small and the final result set is also small, using DRIVING_SITE to push the small table to the larger table’s site yields a much faster distributed query.