Oracle Cloud Infrastructure Documentation

DBMS_PROXY_SQL

Describes the procedures to enable and disable query offload for an elastic pool leader or an elastic pool member. This package also provides procedures to enable and manage ProxySQL for statement routing.

Parent topic: Autonomous Database Supplied Package Reference

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.

Parent topic: DBMS_PROXY_SQL

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.