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.
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.
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.
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.
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:
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.
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.
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.
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:
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.
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;
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).
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.
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.
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.
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:
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.