Oracle Cloud Infrastructure Documentation
Try Free Tier

Offload Queries from Elastic Pool Leader to Member Refreshable Clones

When you have heavy read workloads, where a relatively small amount of data is scanned by multiple queries, you can offload queries (reads) from an elastic pool leader to Refreshable Clones.

Parent topic: Use and Manage Elastic Pools on Autonomous Database

About Query Offload for an Elastic Pool Leader

Provides information about using query offload with an elastic pool and describes the query offload features.

Elastic pool query offload provides performance benefits by allowing one or more Refreshable Clones to handle queries for an elastic pool leader. This feature also allows you to add Refreshable Clones to accommodate increasing query (read) demand. Offloading queries allows your application to scale horizontally, where you can add Refreshable Clones to maintain overall system performance as needed to satisfy your query request volume.

When query offload is enabled, queries are submitted to the Elastic Pool Leader and one or more available Refreshable Clones become candidates for query offloading. In addition, when more Refreshable Clones are added, query offload dynamically adjusts to make use of the new resources.

One use case for query offload is to enable the feature during peak hours to take load off of the Elastic Pool Leader. During quiet hours, you can disable query offload to perform maintenance operations such as refreshing the Refreshable Clones.

By default query offload considers queries from any session. Alternatively you can offload queries from a list of sessions that you specify by module or action name.


Description of autonomous-elastic-pool-leader-query-offload.png follows

Data on the Refreshable Clones is up to date based on the last refresh time for each refreshable clone. This means, when query offload is enabled you perform all DDL, DML and PL/SQL operations on the elastic pool leader. Then, after a Refreshable Clone is refreshed, the changes are reflected on the Refreshable Clone.

See Use Refreshable Clones with Autonomous Database for more information.

Query Offload Features

  • Dynamic Addition: Refreshable Clones may be added as members of the elastic pool at any time. Query offload dynamically adjusts to make use of new members.

  • Dynamic Removal: Refreshable Clones may be removed as members of the elastic pool at any time. Query offload dynamically adjusts to stop offloading queries to a Refreshable Clone that has been removed from the elastic pool.

  • Session Based Sticky Offload: Query offload is sticky within a session, meaning if a query in a session has been offloaded to a particular Refreshable Clone, then query offload uses the same Refreshable Clone to offload subsequent queries in the same session.

  • One-to-One Service Mapping: There is a one-to-one mapping between the service used in the elastic pool leader by the original query and the service used in a Refreshable Clone for any offloaded query. For example, if a session is connected to the MEDIUM service, then query offload for that session also uses the MEDIUM service on the Refreshable Clone.

  • Determine if Session is Offloaded: Using a SYS_CONTEXT query you can determine if a session runs on the elastic pool leader or is offloaded to a Refreshable Clone.

DBA_PROXY_SQL Views

Use the DBA_PROXY_SQL views DBA_PROXY_SQL_ACTIONS and DBA_PROXY_SQL_MODULES to display the list of modules or actions that are configured for query offload. You must query these views from a session that is not enabled for query offload.

Enable Query Offload for an Elastic Pool Leader

Describes how to enable query offload for an elastic pool leader.

The following are requirements for enabling query offload:

  • You can enable query offload for an elastic pool leader with no Refreshable Clones. After you enable query offloading you can add Refreshable Clones and the query offload feature dynamically adjusts to make use of the refreshable clones.

  • A Refreshable Clone that is a candidate for query offload must:

    • Have the elastic pool leader as its source database and be in the same region as the elastic pool leader.

    • Be an elastic pool member.

To enable query offload:

  1. Verify that the Autonomous Database instance is an elastic pool leader.

    Use the following query to verify that an instance is an elastic pool leader:

    SELECT sys_context('userenv', 'is_elastic_pool_leader') FROM DUAL;

    This query should return YES.

    Note

    There may be a delay of up to 15 minutes for the sys_context value to reflect the current value for the elastic pool leader, if the elastic pool has recently been created or modified.
  2. Enable query offload for an elastic pool leader.

    There are two choices: you can enable query offload for queries from any session or limit query offload to the sessions you specify by module or action name.

    • To enable query offload for all sessions run DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOAD without parameters. For example: 

      EXEC DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOAD;

    • To enable query offload for specific sessions by module name or action name, run DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOAD with the module_name or action_name parameters.

      See Enable Query Offload for an Elastic Pool Leader for Named Modules or Actions for more information.

When query offload is enabled for a session you can find the name of the Refreshable Clone to which queries are offloaded. For example, from the session run this query:

SELECT sys_context('userenv', 'con_name') FROM DUAL;

If queries are not being offloaded to a Refreshable Clone this query shows the name of the elastic pool leader.

See SYS_CONTEXT for more information.

See ENABLE_READ_ONLY_OFFLOAD Procedure for more information.

Parent topic: Offload Queries from Elastic Pool Leader to Member Refreshable Clones

Enable Query Offload for an Elastic Pool Leader for Named Modules or Actions

Describes how to enable query offload for an elastic pool leader for sessions with named modules or actions.

To enable query offload for specific sessions by module name or action name:

  1. Verify that the instance is an elastic pool leader.

    Use the following query to verify that an instance is an elastic pool leader:

    SELECT sys_context('userenv', 'is_elastic_pool_leader') FROM DUAL;

    This query should return YES.

    Note

    There may be a delay of up to fifteen minutes for the sys_context value to reflect the current value for the elastic pool leader, if the elastic pool has recently been created or modified.
  2. Use the routines SET_ACTION or SET_MODULE in DBMS_APPLICATION_INFO to set the module name and or the action name in the current session.

    For example

    CREATE or replace PROCEDURE add_employee( 
  name VARCHAR2, 
  salary NUMBER, 
  manager NUMBER, 
  title VARCHAR2, 
  commission NUMBER, 
  department NUMBER) AS 
BEGIN 
  DBMS_APPLICATION_INFO.SET_MODULE( 
    module_name => 'add_employee', 
    action_name => 'insert into emp'); 
  INSERT INTO emp 
    (ename, empno, sal, mgr, job, hiredate, comm, deptno) 
    VALUES (name, emp_seq.nextval, salary, manager, title, SYSDATE, 
            commission, department); 
  DBMS_APPLICATION_INFO.SET_MODULE(null,null); 
END;

    See DBMS_APPLICATION_INFO for more information.

  3. Enable query offload for an elastic pool leader and specify the eligible sessions by module name or by action name (or by both module name and action name).

    For example:

    DECLARE 
   mod_values clob := to_clob('["mod1", "mod2"]');
   act_values clob := to_clob('["act1", "act2"]');
BEGIN 
   DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOAD(
      module_name => mod_values,                                          
      action_name => act_values);
END;
/

When query offload is enabled for a session, from the session you can find the name of the Refreshable Clone to which queries are offloaded. For example:

SELECT sys_context('userenv', 'con_name') FROM DUAL;

If queries are not being offloaded to a Refreshable Clone this query shows the name of the elastic pool leader.

See SYS_CONTEXT for more information.

The views DBA_PROXY_SQL_ACTIONS and DBA_PROXY_SQL_MODULES display the list of modules or actions that are configured for query offload. You must query these views from a session that is not enabled for query offload. See DBA_PROXY_SQL Views for more information.

See ENABLE_READ_ONLY_OFFLOAD Procedure for more information.

Disable Query Offload for an Elastic Pool Leader

Describes how to disable query offload for an elastic pool leader.

Disable query offload on the elastic pool leader.

For example:

EXEC DBMS_PROXY_SQL.DISABLE_READ_ONLY_OFFLOAD;

See DISABLE_READ_ONLY_OFFLOAD Procedure for more information.

Notes for Offloading Queries

Provides additional notes for the query offload feature.

Notes for query offload:

  • Addition or Removal of a Refreshable Clone: When a Refreshable Clone is added as an elastic pool member, there can be a delay of up to fifteen minutes for the addition to be reflected in the elastic pool leader. A newly added Refreshable Clone is not considered as a target for query offloads until the leader is aware of the newly added elastic pool member.

    Similarly, when a Refreshable Clone is removed from the elastic pool, there can be a delay of up fifteen minutes for the removal to be reflected in the elastic pool leader. The removed Refreshable Clone is considered as a target for query offloads until the leader knows that the member has been removed from the elastic pool.

  • Refresh of Refreshable Clone: When a Refreshable Clone is being refreshed, queries that are offloaded to the Refreshable Clone may be delayed.

  • Public and Private Endpoint Support: Query offload is supported for instances on a public endpoint and for instances on a private endpoint.

  • Disconnected Refreshable Clone: If a Refreshable Clone becomes disconnected, it is no longer eligible as a target for query offload. There can be a delay of up to fifteen minutes for the elastic pool leader to detect that a Refreshable Clone has become disconnected and to stop offloading queries to it.

    To ensure that a Refreshable Clone remains available for query offloading, either enable automatic refreshes or periodically manually refresh the Refreshable Clone.

    See Refresh a Refreshable Clone on Autonomous Database for more information.

  • Case Sensitive Module Name and Action Name: When you offload queries from a list of sessions that you specify by module or action name, the module name and action name set with DBMS_APPLICATION_INFO are case sensitive. DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOAD adheres to this case sensitive behavior. For example, when a session has a lowercase module name, or mixed case module name, the case must match in the parameter values when you enable query offload with DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOAD and you include the module_name or action_name parameters.

    You can check the module name and action name for the current session using DBMS_APPLICATION_INFO.READ_MODULE: 

    set serveroutput on;
declare
  l_mod varchar2(50);
  l_act varchar2(50);
BEGIN
DBMS_APPLICATION_INFO.READ_MODULE(l_mod, l_act);
  DBMS_OUTPUT.PUT_LINE('l_mod: ' || l_mod);
  DBMS_OUTPUT.PUT_LINE('l_act: ' || l_act);
END;
/

  • Typical Use Case: One use case for query offload is to enable the feature during peak hours to take load off of the Elastic Pool Leader. During quiet hours, you can disable query offload to perform maintenance operations such as refreshing the Refreshable Clones.

Was this article helpful?