Use Efficient Statement Routing by Enabling ProxySQL on Autonomous Database

ProxySQL enables unified access to multiple Autonomous Databases by routing statements for DML and queries, and eliminates the need to physically consolidate all the data into a single database.

Topics

Parent topic: Use and Manage Elastic Pools on Autonomous Database

About Efficient Statement Routing with ProxySQL on Autonomous Database

ProxySQL on Autonomous Database allows you to use multiple Autonomous Database instances and makes it easy to access and analyze data as if it was stored in just one place.

You can use ProxySQL when you need to work with a large setup that involves multiple Autonomous Databases. ProxySQL gives you unified access to different databases and removes the need to physically move data into one place.

When you enable ProxySQL you designate one Autonomous Database instance as a router instance and one or more Autonomous Database instances are target instances. The router instance includes a routing table that determines how a statement is distributed (mapped) to one or more target instances. A target instance includes an acceptance table. The acceptance table is similar to the routing table and contains entries specifying that the instance accepts statement redirects from the router.

Depending on the routing method you select, statements are automatically mapped from the router instance to one or more target instances. An application connects to the router instance and runs on the router instance and Autonomous Database redirects statements to one or more target instances.

The following are some of the benefits of enabling ProxySQL, as compared to using a single very large Autonomous Database:

  • ProxySQL provides database autonomy and independence for operations such as lifecycle management and admin tasks. For example, using ProxySQL, database operations, including backup and restore, and features such as Autonomous Data Guard are managed independently in each Autonomous Database instance.

  • ProxySQL allows you to efficiently manage very large databases by dividing data across multiple Autonomous Database instances.

There are several terms to know when you work with ProxySQL:

Router Instance: A router instance is designated when you enable ProxySQL. A routing table determines how a statement is distributed (mapped) to one or more target instances. Depending on the specified routing method, statements from the router instance are automatically mapped to one or more target instances.

Target Instance: Target Instance is designated when you create a mapping on the router instance. An acceptance table is added on each target instance.

Routing Table: A routing table contains target mapping entries that specify the instance to which statements are automatically routed.

Acceptance Table: An acceptance table contains entries specifying that the instance accepts statement redirects from the router.

Requirements to Use ProxySQL

The following are ProxySQL requirements:

  • Enabling ProxySQL is only supported for Autonomous Database instances that are in an elastic pool. The router instance and all target instances must be members of the same elastic pool.

  • The router instance and all target instances must be in the same region.

  • The metadata for the objects you are routing must match on router and target instances. It is the application designer's responsibility that the metadata for objects present in a target instance are also present in the router instance. For example, if you have a table named EMPLOYEES in the target instance, then must also have a table with matching metadata named EMPLOYEES in the router instance. The table in the router instance does not have to be empty (metadata only).

Recommendation for Enabling ProxySQL

The instances you add as the router and as targets can be of any Autonomous Database workload type. If there are entries in the routing (mapping) table, then statements on objects specified in those entries are routed to the corresponding target instance(s) regardless of the workload type of the target. Oracle recommends that all theAutonomous Database instances use the Data Warehouse workload type.

Automatic Statement Routing with ProxySQL Enabled

Using ProxySQL you can distribute statements across targets using any of the supported routing methods.

Note:

Whatever routing method (mapping) you decide to use, it is the application schema designer's responsibility to ensure that metadata for objects placed in a target instance are also available in the router instance. For example, if you have a table named EMPLOYEES in the target instance, then must also have a table with matching metadata named EMPLOYEES in the router instance. The table in the router instance does not have to be empty.

  • Schema-Level Routing: All objects of a schema are mapped to a single target instance. You can map multiple schemas to a single target instance. However, the statements for one schema cannot be mapped across multiple target instances, which means that the same schema cannot be used for both schema and object routing.

    To specify Schema-Level Routing you call the DBMS_PROXY_SQL.ADD_MAPPING procedure with the object_name parmeter set to the value "*".


    Description of proxysql-schema.png follows
    Description of the illustration proxysql-schema.png

    In this example, the metadata for objects in schema A are present in both target Autonomous Database 1 and in the router instance, and the metadata for objects in schema B are present in both target Autonomous Database 2 and in the router instance, and so on.

  • Object-Level Routing: Objects in a schema are mapped across multiple target instances.

    To specify Object-Level Routing you call the DBMS_PROXY_SQL.ADD_MAPPING procedure with the object_name parmeter set to a table name.


    Description of proxysql-object.png follows
    Description of the illustration proxysql-object.png

    In this example, the metadata for Table A is present in both target 1 and in the router instance, and the metadata for Table B is present in both target 2 and in the router instance, and so on.

  • Hybrid Routing: Objects of a schema are routed using a combination of Schema-level routing and Object-Level Routing.


    Description of proxysql-hybrid.png follows
    Description of the illustration proxysql-hybrid.png

    In this example, the metadata for Schema A is present in both target Autonomous Database 1 and in the router instance, and the metadata for Table B (in Schema B) is present in both target Autonomous Database 2 and in the router instance, and so on.

Service Mapping with Automatic Statement Routing When ProxySQL is Enabled

When the routing table indicates a mapping to a target database, the same service you are connected to the router database is used for the data access in the target database.

For example, if a session is connected to the HIGH service on the router instance, a statement or statement fragment routed to a target instance also uses the HIGH service. Similarly, if a session is connected to the MEDIUM service on the router instance, a statement routed to a target instance uses the MEDIUM service.

If the service used to connect on the router instance is not available on a target instance, the statement or statement fragment routed to the target instance uses the MEDIUM service (the available services depends on the Autonomous Database workload type).

Parent topic: Use Efficient Statement Routing by Enabling ProxySQL on Autonomous Database

Enable Statement Routing and Define Schema Mapping

This section explains how to set up ProxySQL for automatic statement routing from a main router to one or more target database instances.

Parent topic: Use Efficient Statement Routing by Enabling ProxySQL on Autonomous Database

Enable Statement Routing and Define Object Mapping from Router to Target Instances

To set up statement routing, you enable ProxySQL on the router instance and define the schema or objects whose statements are mapped (sent) to target databases.

The following are prerequisites to enable automatic statement routing:

  • Create the Autonomous Database instance that you plan to use for the router instance or identify an existing Autonomous Database instance as the router instance.

  • Create the target instances or identify the target instances from existing Autonomous Database instances.

  • Ensure that the router instance and any target instance where you are redirecting queries have matching metadata for any objects you are mapping from the router to a target.

    Whatever routing method (mapping) you decide to use, it is the application schema designer's responsibility to ensure that metadata for objects placed in a target instance are also available in the router instance. For example, if you have a table named EMPLOYEES in the target instance, then must also have a table with matching metadata named EMPLOYEES in the router instance. The table in the router instance does not have to be empty.

  • From the available choices, determine the type of statement routing you want to use: Schema-Level Routing, Object-Level Routing, or Hybrid Routing.

To enable automatic statement routing and add target mapping entries to the routing table:

  1. On an Autonomous Database instance run DBMS_PROXY_SQL.ENABLE_ROUTING to enable automatic statement routing.

    For example:

    BEGIN
   DBMS_PROXY_SQL.ENABLE_ROUTING;
END;
/

    The instance where you run DBMS_PROXY_SQL.ENABLE_ROUTING becomes the router instance. This enables automatic statement routing and creates the routing table.

    See ENABLE_ROUTING Procedure for more information.

  2. Run DBMS_PROXY_SQL.ADD_MAPPING to define the mapping from the router to a target. This creates an entry in the routing table that specifies the target instance to which queries are automatically routed.

    Example to enable object-level routing:

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

    Example to enable schema-level routing:

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

    Use the following query to retrieve the database OCID:

    SELECT json_value(cloud_identity,'$.DATABASE_OCID') 
   FROM v$pdbs;

    The object_owner parameter specifies the object's owner.

    The object_name parameter specifies the object.

    The database_ocid parameter specifies the target instance OCID. The database_ocid parameter value must be in uppercase. You can determine the target instance's OCID by connecting to the target instance as ADMIN and running the following query: 

    SELECT json_value(cloud_identity, '$.DATABASE_OCID') FROM v$pdbs;

    See Obtain Tenancy Details for more information.

    This creates a routing entry in the routing table and defines an object mapping to a target instance.

    See ADD_MAPPING Procedure for more information.

  3. Run DBMS_PROXY_SQL.ADD_MAPPING to add additional target mappings, as needed.

    For example:

    BEGIN
 DBMS_PROXY_SQL.ADD_MAPPING ( 
    object_owner   => 'DW_USER_1',
    object_name    => 'CUSTOMERS',
    database_ocid  => 'TARGET3_DATABASE_OCID');
END;
/

You can query DBA_PROXY_SQL_MAPPINGS view to list the records in the routing table. See DBA_PROXY_SQL_MAPPINGS View for more information.

Parent topic: Enable Statement Routing and Define Schema Mapping

Accept Statement Routing on Target Instances

You must accept statement routing on a target instance to allow mapping from the router instance.

To allow (accept) statement redirects from the router instance to a target instance:

  1. On the target instance run DBMS_PROXY_SQL.ACCEPT_MAPPING.

    For example:

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

    The object_owner is the owner for which the target accepts statement routing.

    The router_database_ocid specifies the OCID of the router Autonomous Database instance. This specifies the instance from which the target accepts incoming redirected statement requests.

    The acceptance mapping is one per object owner, so only one acceptance may be required corresponding to multiple object mappings on the router instance.

    You can statement DBMS_PROXY_SQL_ACCEPTED_MAPPINGS view to list the records in the acceptance table. See DBA_PROXY_SQL_ACCEPTED_MAPPINGS View for more information.

    See ACCEPT_MAPPING Procedure for more information.

  2. If there are multiple targets specified in the router mapping, run DBMS_PROXY_SQL.ACCEPT_MAPPING on each target.

    See ACCEPT_MAPPING Procedure for more information.

Parent topic: Enable Statement Routing and Define Schema Mapping

Submit Statements Through ProxySQL

To take advantage of automatic statement routing you need to connect to and submit your statements to the router instance managed by ProxySQL.

When you send queries, including DML statements, with ProxySQL enabled, you need to connect to the router instance to take advantage of automatic statement routing.

Parent topic: Use Efficient Statement Routing by Enabling ProxySQL on Autonomous Database

Stop Routing Statements to a Target Instance

Perform these steps on a target Autonomous Database to stop it from stop it from accepting routed statements from the ProxySQL router.

  1. Run DBMS_PROXY_SQL.REJECT_MAPPING on a target instance to reject statement routing for the specified owner.

    For example:

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

    The object_owner parameter specifies the owner name.

    The router_database_ocid parameter specifies the OCID of the router instance.

    The target Autonomous Database instance where you run this procedure no longer requests routing requests from the specified router instance for the specified object owner.

  2. Run DBMS_PROXY_SQL.REJECT_MAPPING on additional targets as required.

See REJECT_MAPPING Procedure for more information.

Parent topic: Use Efficient Statement Routing by Enabling ProxySQL on Autonomous Database

Remove Schema or Object Mapping from Router Instance

Provides the steps to remove a specific mapping from the router database managed by ProxySQL.

  1. You can use the DBMS_PROXY_SQL.REMOVE_MAPPING procedure to remove a previously added mapping entry for the specified object. For example:

    Example with an table:

    BEGIN
 DBMS_PROXY_SQL.REMOVE_MAPPING ( 
    object_owner   => 'DW_USER',
    object_name    => 'INVENTORY');
 END;
/
    This example removes the mapping entry for the INVENTORY table from the routing table. After running this procedure, queries on the INVENTORY table are not routed to any target Autonomous Database.

    Example: with a schema:

    BEGIN
 DBMS_PROXY_SQL.REMOVE_MAPPING ( 
    object_owner   => 'DW_USER',
    object_name    => '*');
 END;
/
    This example removes the mapping entry for the DW_USER schema from the routing table. After you run this procedure, queries on the DW_USER schema are not routed to any target Autonomous Database.

    The object_owner parameter specifies the owner.

    The object_name parameter specifies the table name.

    See REMOVE_MAPPING Procedure for more information.

Parent topic: Use Efficient Statement Routing by Enabling ProxySQL on Autonomous Database

Disable Statement Routing

Shows the steps to automatic statement routing by disabling ProxySQL.

Run DBMS_PROXY_SQL.DISABLE_ROUTING on the router instance to disable ProxySQL and to disable automatic statement routing to target instances.

For example:

BEGIN
 DBMS_PROXY_SQL.DISABLE_ROUTING;
END;
/

This disables ProxySQL and disables automatic statement routing on the router.

When you disable ProxySQL, the statement mapping entries in the routing table on the router instance are not deleted. This means if you re-enable ProxySQL and the routing table has existing entries, automatic statement routing routes statements to target instances, as specified in the routing table. Use the procedure DBMS_PROXY_SQL.REMOVE_MAPPING if you wan to remove existing routing table entries before you re-enable ProxySQL.

See DISABLE_ROUTING Procedure for more information.

Parent topic: Use Efficient Statement Routing by Enabling ProxySQL on Autonomous Database

Automatic Statement Routing with ProxySQL Notes

Lists limitations and important notes about automatic statement routing when ProxySQL is enabled.

  • The ADMIN user has the privileges to manage ProxySQL. If you want to enable another user, you must grant the following privileges

    • EXECUTE privilege on DBMS_PROXY_SQL package.

    • READ privilege on the DBA_PROXY_SQL_MAPPINGS view.

    • READ privilege on the DBA_PROXY_SQL_ACCEPTED_MAPPINGS view.

  • Automatic statement routing resumes seamlessly when:

    • The target Autonomous Database instance fails over to a local Autonomous Data Guard standby.

    • You perform a switchover for a target Autonomous Database instance to a local Autonomous Data Guard standby.

    However, automatic statement routing stops after a failover or switchover to a cross-region Autonomous Data Guard standby database.

Parent topic: Use Efficient Statement Routing by Enabling ProxySQL on Autonomous Database