Oracle Cloud Infrastructure Documentation

DBMS_PROXY_SQL

Describes the procedures to enable and disable query offload for an elastic pool leader.

Parent topic: Autonomous Database Supplied Package Reference

DISABLE_READ_ONLY_OFFLOAD Procedure

This procedure disables query offload for an Autonomous Database elastic pool leader or for an elastic pool member.

Syntax

DBMS_PROXY_SQL.DISABLE_READ_ONLY_OFFLOAD;

Example

EXEC DBMS_PROXY_SQL.DISABLE_READ_ONLY_OFFLOAD;

Parent topic: DBMS_PROXY_SQL

ENABLE_READ_ONLY_OFFLOAD Procedure

This procedure enables query offload for an Autonomous Database elastic pool leader or for an elastic pool member.

Syntax

DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOAD( 
      module_name       IN CLOB, 
      action_name       IN CLOB);

Parameters

Parameter Description

module_name

Specifies a list of module names as a comma-separated list. The list specifies the modules where queries are considered for offload (where a session's module name matches a value in the list).

The default value for this parameter is NULL, which means that a session's module name can be any value and the session is considered for offloading.

action_name

Specifies a list of action names as a comma-separated list. The list specifies the action names where queries are considered for offloading (where a session's action name matches a value in the list).

The default value for this parameter is NULL, which means that a session's action name can be any value and the session is considered for offloading

Usage Notes

  • If both module_name and action_name are specified, a session's module name must match a value in the list of module names and it's action name must match a value in the list of action names for the session to be considered for offload.

  • When query offload is enabled for a 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 (or the name of the elastic pool member).

  • 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.

  • 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;
/

Examples

EXEC DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOAD;


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;
/

Parent topic: DBMS_PROXY_SQL