DRIVING_SITE HINT

Introduction

In this article, we explore the Oracle SQL hint DRIVING_SITE as part of our series on Oracle hints. The DRIVING_SITE hint allows you to control the site (or database) that drives the execution of a query in a distributed database environment. This is particularly useful when working with queries that access tables or views located on remote databases.

In a distributed system, Oracle must decide where to start processing the query and how to access the data. The DRIVING_SITE hint gives you explicit control over which site should start executing the query, potentially improving performance in cases where one site has better resources, fewer rows, or lower latency than another.  As always, test, test, test.

How Oracle Handles Distributed Queries

When working with distributed databases (across multiple physical sites or Oracle databases), Oracle’s optimizer must decide which site should "drive" the execution of the query.  

By default, Oracle will evaluate the query on the site it deems optimal based on factors like:

• Data size

• Indexes

• Network cost
  
  

However, in some scenarios, you may want to influence this decision to optimize performance. For example, if one remote site has a smaller table or a more selective index, you may want to instruct Oracle to begin the query at that site to reduce data transfer over the network.

The DRIVING_SITE hint helps control this behavior by specifying which site should start the query execution.

Using the DRIVING_SITE Hint Example

Consider the following example where you want to control the execution of a distributed query between two databases

SELECT /*+ DRIVING_SITE(d) */

    e.employee_id,

    e.first_name,

    e.last_name,

    d.department_name

FROM employees e

JOIN departments@hrdb d

  ON e.department_id = d.department_id

WHERE d.location_id = 1700

/

In this query, the hint DRIVING_SITE(dept) tells Oracle to use the departments table, which resides on the remote database hrdb,as the driving site.

By using this hint, Oracle will push the execution of the query to the remote database where the departments table is located, performing the filtering and data retrieval there before sending the results back to the local site for further processing.

In distributed  database systems, this can significantly reduce the amount of data transferred between sites and improve performance, especially if the remote site has selective data or better resources for processing.

Execution Plan Example

Here’s an example of an execution plan after applying the DRIVING_SITE hint

-------------------------------------------------------------------------------------------------

| Id  | Operation                        | Name               | Rows  | Cost |    Site           |

-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                 |                    |       |      |                   |

|   1 |  REMOTE                          |                    |   100 |   10 | REMOTE_SITE       |

-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - remote query SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, D.DEPARTMENT_NAME

                    FROM EMPLOYEES E, DEPARTMENTS D

                    WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

                    AND D.LOCATION_ID = 1700

Outline Data:

-------------

  /*+

      BEGIN_OUTLINE_DATA

      DRIVING_SITE(@"SEL$1" "D"@"SEL$1")

      END_OUTLINE_DATA

  */

In the execution plan, Oracle processes the query on the remote site (where departments resides), as directed by the DRIVING_SITE hint.  The query is executed remotely, reducing the data transferred over the network.

Note

The plan is not very useful when using the hint to push the execution remotely.  All you see is the statement was executed remotely.  To get the actual query plan, you will need to log onto the remote database, find the SQl and issue a DBMS_XPLAN.DISPLAY_CURSOR

Considerations

Before using the DRIVING_SITE hint, consider the following

• This hint applies only in distributed query environments where multiple remote databases are involved (not applicable for Oracle RAC).

• The hint influences the location where Oracle performs the query execution, which can reduce unnecessary data transfers and improve performance.

• Be cautious of overusing the hint in situations where it may cause unnecessary data movement or processing at a less optimal site.

• Oracle may ignore the hint if it deems the driving site is invalid or if there are constraints that prevent it from using the specified site.

Further Reading

• You can find more information on using the hint in the documentation 

Published on 

Published 8th May 2025