DBMS_PROXY_SQL

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

• Summary of DBMS_PROXY_SQL Subprograms
  This table summarizes the subprograms included in the DBMS_PROXY_SQL package.

Summary of DBMS_PROXY_SQL Subprograms

This table summarizes the subprograms included in the DBMS_PROXY_SQL package.

Subprogram	Description
ACCEPT_MAPPING Procedure	Adds a new mapping entry on the target Autonomous Database instance corresponding to the mapping entry for a schema object on the router Autonomous Database instance.
ADD_MAPPING Procedure	Adds a new mapping entry for a schema object in the routing table on the router Autonomous Database instance.
DISABLE_READ_ONLY_OFFLOAD Procedure	Disables query offload for an Autonomous Database elastic pool leader or for an elastic pool member.
DISABLE_ROUTING Procedure	Disables ProxySQL automatic statement routing for an Autonomous Database instance.
ENABLE_READ_ONLY_OFFLOAD Procedure	Enables query offload for an Autonomous Database elastic pool leader or for an elastic pool member.
ENABLE_ROUTING Procedure	Enables the ProxySQL automatic statement routing for an Autonomous Database instance.
REJECT_MAPPING Procedure	Removes an existing mapping entry for a schema object from the target Autonomous Database instance.
REMOVE_MAPPING Procedure	Removes an existing mapping entry for a schema object from the router Autonomous Database instance.

• ACCEPT_MAPPING Procedure
  Run this procedure on a ProxySQL target instance to add a new mapping entry corresponding to the mapping entry for an object on in the routing table on the router Autonomous Database instance.
• ADD_MAPPING Procedure
  Run this procedure on a router Autonomous Database instance to add a new mapping in the routing table.
• DISABLE_READ_ONLY_OFFLOAD Procedure
  This procedure disables query offload for an Autonomous Database elastic pool leader or for an elastic pool member.
• DISABLE_ROUTING Procedure
  Run this procedure on a router instance to disable ProxySQL.
• ENABLE_READ_ONLY_OFFLOAD Procedure
  This procedure enables query offload for an Autonomous Database elastic pool leader or for an elastic pool member.
• ENABLE_ROUTING Procedure
  Run this procedure on an Autonomous Database instance to designate the instance as a router instance and to enable automatic statement routing.
• REJECT_MAPPING Procedure
  Run on a target Autonomous Database instance to remove an existing mapping entry from the acceptance table.
• REMOVE_MAPPING Procedure
  Run this procedure on the router Autonomous Database instance to remove an existing mapping entry for an object from the routing table.

ACCEPT_MAPPING Procedure

Run this procedure on a ProxySQL target instance to add a new mapping entry corresponding to the mapping entry for an object on in the routing table on the router Autonomous Database instance.

Syntax

DBMS_PROXY_SQL.ACCEPT_MAPPING ( 
    object_owner          IN VARCHAR2,
    router_database_ocid  IN VARCHAR2);

Parameters

Parameter	Description
object_owner	Specifies the object owner. This parameter is mandatory.
router_database_ocid	Specifies the OCID of the router Autonomous Database instance. The router_database_ocid value must be supplied in uppercase. This parameter is mandatory.

Example

BEGIN
 DBMS_PROXY_SQL.ACCEPT_MAPPING ( 
    object_owner          => 'DW_USER',
    router_database_ocid  => 'TARGET_DATABASE_OCID');
 END;
/

Usage Notes

• You must run this procedure on the target Autonomous Database instance.

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_PROXY_SQL package.

• Each mapping entry in the routing table on the router Autonomous Database instance must have a corresponding entry on the respective target Autonomous Database instance.

ADD_MAPPING Procedure

Run this procedure on a router Autonomous Database instance to add a new mapping in the routing table.

Syntax

DBMS_PROXY_SQL.ADD_MAPPING ( 
    object_owner   IN VARCHAR2,
    object_name    IN VARCHAR2,
    database_ocid  IN VARCHAR2);

Parameters

Parameter	Description
object_owner	Specifies the object owner. This parameter is mandatory.
object_name	Specifies the name of the object for which automatic statement routing should be enabled. The valid values for object_name parameter are: An object_name to enable object-level routing. An asterisk (*) to enable schema-level routing. This parameter is mandatory.
database_ocid	Specifies the OCID of the target Autonomous Database instance. The database_ocid value must be supplied in uppercase. This parameter is mandatory.

Examples

Example to enable object-level routing:

BEGIN
 DBMS_PROXY_SQL.ADD_MAPPING( 
    object_owner   => 'DW_USER',
    object_name    => 'INVENTORY',
    database_ocid  => 'TARGET_DATABASE_OCID');
END;
/

Example to enable schema-level routing:

BEGIN
 DBMS_PROXY_SQL.ADD_MAPPING( 
    object_owner   => 'DW_USER',
    object_name    => '*',
    database_ocid  => 'TARGET_DATABASE_OCID');
END;
/

Usage Notes

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_PROXY_SQL package.

• You must run this procedure on the router Autonomous Database instance.

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;

DISABLE_ROUTING Procedure

Run this procedure on a router instance to disable ProxySQL.

Syntax

DBMS_PROXY_SQL.DISABLE_ROUTING;

Example

BEGIN
   DBMS_PROXY_SQL.DISABLE_ROUTING;
END;
/

Usage Notes

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_PROXY_SQL package.

• You must run this procedure on the router Autonomous Database instance.

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

ENABLE_ROUTING Procedure

Run this procedure on an Autonomous Database instance to designate the instance as a router instance and to enable automatic statement routing.

Syntax

DBMS_PROXY_SQL.ENABLE_ROUTING;

Example

BEGIN
   DBMS_PROXY_SQL.ENABLE_ROUTING;
END;
/

Usage Notes

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_PROXY_SQL package.

• You must run this procedure on the router Autonomous Database instance.

REJECT_MAPPING Procedure

Run on a target Autonomous Database instance to remove an existing mapping entry from the acceptance table.

Syntax

DBMS_PROXY_SQL.REJECT_MAPPING ( 
    object_owner         IN VARCHAR2,
    router_database_ocid IN VARCHAR2);

Parameters

Parameter	Description
object_owner	Specifies the object owner. This parameter is mandatory.
router_database_ocid	Specifies the OCID of the router Autonomous Database instance. The router_database_ocid value must be supplied in uppercase. This parameter is mandatory.

Example

BEGIN
 DBMS_PROXY_SQL.REJECT_MAPPING ( 
    object_owner          => 'DW_USER',
    router_database_ocid  => 'TARGET_DATABASE_OCID');
 END;
/

Usage Notes

• You must run the procedure on the target Autonomous Database instance.

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_PROXY_SQL package.

REMOVE_MAPPING Procedure

Run this procedure on the router Autonomous Database instance to remove an existing mapping entry for an object from the routing table.

Syntax

DBMS_PROXY_SQL.REMOVE_MAPPING ( 
    object_owner   IN VARCHAR2,
    object_name    IN VARCHAR2);

Parameters

Parameter	Description
object_owner	Specifies the object owner. This parameter is mandatory.
object_name	Specifies the name of the object for which automatic query routing should be disabled. This parameter is mandatory.

Example

BEGIN
 DBMS_PROXY_SQL.REMOVE_MAPPING ( 
    object_owner   => 'DW_USER',
    object_name    => 'INVENTORY');
 END;
/

Usage Notes

• You must run this procedure on the router Autonomous Database instance.

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on the DBMS_PROXY_SQL package.