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.

Summary of DBMS_PROXY_SQL Subprograms

This table summarizes the subprograms included in the DBMS_PROXY_SQL package.

• 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 AI Database instance.

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

• DISABLE_READ_ONLY_OFFLOAD Procedure

  This procedure disables query offload for an Autonomous AI 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 AI Database elastic pool leader or for an elastic pool member.

• ENABLE_ROUTING Procedure

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

• REJECT_MAPPING Procedure

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

• REMOVE_MAPPING Procedure

  Run this procedure on the router Autonomous AI 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 AI Database instance.

Syntax

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

Parameters

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 AI 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 AI Database instance must have a corresponding entry on the respective target Autonomous AI Database instance.

ADD_MAPPING Procedure

Run this procedure on a router Autonomous AI 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

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 AI Database instance.

DISABLE_READ_ONLY_OFFLOAD Procedure

This procedure disables query offload for an Autonomous AI 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 AI Database instance.

ENABLE_READ_ONLY_OFFLOAD Procedure

This procedure enables query offload for an Autonomous AI 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

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 AI 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 AI Database instance.

REJECT_MAPPING Procedure

Run on a target Autonomous AI 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

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 AI 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 AI 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

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