Unified query offload provides configuration and performance advantages by allowing one or more refreshable clones to handle queries for an elastic pool leader or member that combines the elastic pool feature query offload with Cloud Links target offloading.

Unified query offload allows you to add refreshable clones to accommodate increasing query (read) demand originating from use of Cloud Links on Cloud Links consumers. Offloading such Cloud Links queries from a single producer allows your application to scale horizontally, where you can add refreshable clones to maintain overall system performance. With this configuration, you can adjust resources as needed to satisfy your query request volume.

An advantage of using unified query offload is this feature enables you to set up Cloud Links offload target one time, and have queries automatically routed to any number of refreshable clones without configuration changes. By enabling ProxySQL read only query offloading and combining this with Cloud Links target offloading, you can add or remove refreshable clones and the list of refreshable clones to send queries to is automatically updated, without requiring any manual configuration. When more refreshable clones are added, unified query offload dynamically adjusts to make use of new resources as they are added. In comparison, when you use Cloud Links and you configure offload targets without unified query offload, using Cloud Links target offloading alone, you must manually configure the list of refreshable clones to send queries to. Unified query offload specifies a single Cloud Links offload target that is an elastic pool leader or member, and the target uses ProxySQL to offload queries to any number of refreshable clones.

The following figure shows unified query offload with the following:

• Three Cloud Links consumers: Instance 1, Instance 2, and Instance 3

• A Cloud Links producer that is also an elastic pool leader (this instance could also be an elastic pool member). This instance has query offload enabled.

• Three Elastic Pool refreshable clones for query offloading

Description of the illustration adb_cloud_links_proxy_sql.jpg

As is the case for query offload, with unified query offload the data on the refreshable clones is up to date based on the last refresh time for each refreshable clone. This means when you use unified query offload, you must perform all operations on data that involve DDL, DML or PL/SQL on the elastic pool leader. Then, after a refreshable clone is refreshed, the changes from the instance where is read only offload is enabled are reflected on the refreshable clone.

See Use Refreshable Clones with Autonomous Database for more information.

Describes how to enable unified query offload with Cloud Links for an elastic pool leader or for an elastic pool member.

1. Determine the Cloud Links producer and on this Autonomous Database instance, enable ProxySQL query offload.

   The Cloud Links producer must be either an elastic pool member or an elastic pool leader.

   See Enable Query Offload for detailed steps on enabling ProxySQL query offload.

2. On the cloud links producer register one or more data sets or update the registration for one or more data sets.

   The Cloud Links producer must be either an elastic pool member or an elastic pool leader.

   • Register a data set.

     See Register or Unregister a Data Set for detailed steps on registering a data set.

   • Update the registration for a data set.

     See Update Data Set Registration Attributes for detailed steps on updating the registration for a data set.

   When you register or update a data set, the value of the offload_targets parameter must be one of the following to enable unified query offload:

   • NULL: If you register a data set or update a data set and you specify the offload_targets parameter as NULL, this enables unified query offload.

   • No value: If you register a data set or update a data set and you do not include the offload_targets parameter, this is the same as setting the value to NULL and enables unified query offload (the default value for offload_targets is NULL).

   • You specify values with offload_targets; however, there is no consumer Autonomous Database instance OCID specified that matches an incoming request (and the ANY keyword is not specified). In this case the system directs the query to one of the refreshable clones of the producer (unified query offload is enabled).

   There are two cases where unified query offload is not used and Cloud Links target offloading applies:

   • If you register or update a data set and include the offload_targets parameter and there is a specified instance OCID for a consumer that matches an incoming request, Cloud Links target offload is used. In this case the system uses the refreshable clone specified with the matching mapping (this behavior is the same as described for offload targets with Cloud Links).

   • If the previous case applies or you register or update a data set and include the offload_targets parameter and you specify the ANY keyword, unified query offload is not used. In this case the system uses the refreshable clone specified with the ANY mapping (this behavior is the same as described for offload targets with Cloud Links).

   See Register a Data Set with Offload Targets for Data Set Access and REGISTER Procedure for more information.

Describes how to disable unified query offload.