Boost Oracle Distributed Query Performance with Collocated Inline Views and Hints

Background

Distributed query statements in Oracle are executed on remote databases; the remote‑side SQL is transformed by the optimizer, executed remotely, and the result set is returned to the local database for further processing. The driving_site hint can change the execution location for SELECTs, but it is ineffective for DML statements.

Optimization Goals

Minimize the number of accesses to the same remote database by consolidating objects from the same remote site into a single SQL operation.

Reduce the size of result sets transferred from remote to local by selecting only required columns.

Ensure that the execution plan on the remote side and the join plan with the local side are efficient.

Optimization Methods

1. Use Collocated Inline Views

Group tables that reside in the same remote database into an inline view so that Oracle can execute the view entirely on the remote side and return a single result set. This reduces round‑trips and data volume. Example query plans show a HASH JOIN performed remotely.

2. Understand CBO Handling of Distributed Queries

The Cost‑Based Optimizer (CBO) tries to merge eligible views and test collocated inline view blocks. When it can combine tables from the same remote site into a single query, it does so. However, CBO may be inefficient for queries with GROUP BY, subqueries, or high complexity.

Examples of problematic cases (subquery, grouping) are shown with their execution plans, illustrating that remote tables are accessed separately, causing large data transfers and long runtimes.

3. Apply HINTs, Especially driving_site

The driving_site hint forces the optimizer to execute the plan on the specified site. driving_site(b) pushes the main query to the remote database, turning the remote table into a “local” table and sending only the small result set back. This is beneficial when the remote side holds the large table and the local side holds a small driving table.

Example 1 demonstrates a query on a small local table (≈10k rows) joining a large remote table (≈3M rows). Using driving_site reduces execution time from 50 s to a few seconds.

Note that driving_site is ignored for DML/DDL statements; the plan is driven by the target table’s location.

Example 2 shows a slow mixed query involving a remote view and local tables. By adding driving_site (or restructuring with PL/SQL), the remote side executes the join using indexes, dramatically improving performance.

Summary

Optimizing Oracle distributed queries that involve DBLINKs requires minimizing remote calls, shrinking transferred result sets, and ensuring efficient execution plans. Techniques such as collocated inline views, understanding CBO behavior, and strategically applying driving_site (or other hints) can achieve significant performance gains. When DML/DDL are involved, hints may be ignored, and alternative approaches like materialized views or PL/SQL partitioning should be considered.