How to Slash Distributed Oracle Query Time with the driving_site Hint

Concept

In everyday work, distributed database queries are performed via DBLINK to access both local and remote tables. Two processing methods exist: (1) fetch remote table data to the local database and join locally, which involves a single network transfer and is the default; (2) send local table data to the remote database, join there, and bring back the result, which may cause larger data movement.

The driving_site hint can force the execution plan to run on the remote side (or local side) when the remote result set is large and the final result set is small, thereby reducing overall network traffic and improving performance.

Case Study

A bank experienced timeouts in its loan‑deposit integration process. The problematic DML statement was:

INSERT INTO UDT_SALS_GL
SELECT T.JYZH, A.ACTACTNO, 'PLAF', A.ACTDRTBA, A.ACTRATVL / 100
FROM CCSACT A
INNER JOIN UDT_FNPT_QYXX T
ON '0' || SUBSTR(T.JYZH, 1, 16) = A.ACTRETAC
WHERE A.ACTDRTBA > 0
AND T.CPLX = 'SALS';

The view ccsact used in the query joins two remote tables ( borms@bssdb and boiss@bssdb) each containing about 235,000 rows, returning the same number of rows. The local table UDT_FNPT_QYXX filters to about 33,500 rows, and after the join only roughly 10,000 rows are inserted.

Without any hint, the insert took 8.17 seconds:

INSERT INTO udt_sals_gl
SELECT t.jyzh, a.actactno,'PLAF', a.ACTDRTBA,a.actratvl/100
FROM ccsact a
INNER JOIN udt_fnpt_qyxx t ON '0'||substr(t.jyzh,1,16) = a.ACTRETAC
WHERE a.actdrtba>0 AND t.cplx='SALS';
-- 10,395 rows created
-- Time: 00:00:08.17

Adding the driving_site hint reduced the execution time to less than one second:

SELECT /*+driving_site(a.t)*/ t.jyzh, a.actactno, 'PLAF', a.ACTDRTBA, a.actratvl/100
FROM ccsact a
INNER JOIN udt_fnpt_qyxx t ON '0' || substr(t.jyzh, 1, 16) = a.ACTRETAC
WHERE a.actdrtba > 0 AND t.cplx = 'SALS';
-- 10,395 rows fetched
-- Time: 00:00:00.79

Note that for DML and DDL statements the driving_site hint is ignored by Oracle; the plan is driven by the database where the target table resides.

Workaround for DML Optimization

To achieve the same optimization for DML, the insert logic is moved into a view on the remote database, and the local database simply selects from that view:

CREATE VIEW vw_udt_sals_gl AS
SELECT t.jyzh AS hkzh,
       a.actactno AS dkzh,
       'PLAF' AS cpm,
       a.ACTDRTBA AS dkye,
       a.actratvl / 100 AS llz
FROM (SELECT trim(t.actactno) AS actactno,
             trim(t.actdrtba) AS actdrtba,
             trim(t.actratvl) AS actratvl,
             trim(a.actretac) AS actretac,
             trim(t.actdlttm) AS actdlttm
      FROM borms t
      INNER JOIN boiss a ON a.actactno = t.actactno AND a.jgh = t.jgh) a
INNER JOIN udt_fnpt_qyxx@cssdb t ON '0' || substr(t.jyzh, 1, 16) = a.ACTRETAC
WHERE a.actdrtba > 0 AND t.cplx = 'SALS';
-- View created.

Then the local insert becomes:

INSERT INTO udt_sals_gl
SELECT * FROM vw_udt_sals_gl@bssdb;
-- 10,395 rows created
-- Time: 00:00:00.64

Execution plan screenshots (remote operations highlighted) are shown below:

Optimization Summary

Use the driving_site hint to reduce overall network data transfer.

When the hint target is nested inside a view, specify it as driving_site(V.T), where V is the view alias and T the table alias.

For DML/DDL statements, the hint is ignored; achieve optimization by moving the DML logic into a remote view and accessing it from the local database.