Use Efficient Statement Routing by Enabling ProxySQL on Autonomous AI Database

ProxySQL on Autonomous AI Database allows you to use multiple Autonomous AI 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 AI 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 AI Database instance as a router instance and one or more Autonomous AI Database instances as 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 AI 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 AI 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 AI Database instance.

• ProxySQL allows you to efficiently manage very large databases by dividing data across multiple Autonomous AI 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.

ProxySQL Routing supports context-aware routing, a mechanism that dynamically chooses the target Autonomous AI Database instance for a table at query runtime.

When a table is eligible to be routed to multiple target Autonomous AI Database instances, the final routing decision is influenced not only by the table static mapping, but also by the structure and context of the query. If such a table appears together with other routed tables at the same level of the query - for example, in the same subquery or join clause - ProxySQL Routing prefers to route it to the same target as those other tables, as long as this is allowed by the mapping rules.

If, after applying these rules, there is still more than one valid target Autonomous AI Database instance, ProxySQL Routing chooses one of the eligible targets at random.

SELECT e.name AS emp_name, d.name AS dept_name 
FROM scott.emp e, scott.dept d 
WHERE e.deptno = d.deptno;

In this example, assume, SCOTT.EMP is mapped to multiple target Autonomous AI Database instances T1 and T2.SCOTT.DEPT is mapped only to target Autonomous AI Database instance T2.This is because SCOTT.EMP and SCOTT.DEPT appear at the same level in the query, ProxySQL Routing routes SCOTT.EMP to T2 to align with the routing of SCOTT.DEPT. This dynamic routing of SCOTT.EMP is only used when the table is explicitly mapped to more than one target. If SCOTT.EMP is not mapped to any target Autonomous AI Database instance, ProxySQL Routing executes the query for SCOTT.EMP on the Router Autonomous AI Database instance itself.

WITH
  sub1 AS (
    SELECT a.c1 r1, b.c1 r2 FROM tab_dup a, tab2 b
  ),
  sub2 AS (
    SELECT a.c1 t1, b.c1 t2 FROM tab_dup a, tab1 b
  )
SELECT * FROM sub1, sub2 ORDER BY 1, 2, 3, 4;

In above example, you will assume TAB_DUP is mapped to both T1 and T2.TAB2 is routed only to T2.TAB1 is routed only to T1. In subquery sub1, TAB_DUP and TAB2 appear together at the same level, so ProxySQL Routing routes TAB_DUP to T2 to co-locate execution with TAB2. In subquery sub2, TAB_DUP appears with TAB1, which is mapped only to T1, so ProxySQL Routing routes TAB_DUP to T1. As a result, TAB_DUP can be routed to different targets in different parts of the same statement, depending on the surrounding query context.

MERGE INTO tab1 t1
USING tab_dup t2
ON (t1.c1 = t2.c1)
WHEN MATCHED THEN
  UPDATE SET t1.c2 = t2.c2;

In the above example, you will assume, TAB_DUP is mapped to both T1 and T2. TAB1 is mapped only to T1.

Here, AB1 and TAB_DUP are referenced at the same level in the MERGE statement. ProxySQL Routing therefore routes TAB_DUP to T1 to align with the location of TAB1. Only the rows of TAB_DUP that are present in T1 participate in the MERGE operation, and updates to TAB1 are based on those values.

Statement routing with ProxySQL is most effective when each SQL statement can be deterministically mapped to a single target Autonomous AI Database instance using schema-level or object-level mappings.

When each SQL statement can be deterministically mapped to a single target and you enable statement routing, the work runs where the referenced objects reside. This pattern is beneficial in terms of query performance for the following database architectures:

• Tenant-per-schema/database layouts

• Regional partitions

• Clear domain boundaries (for example, INVENTORY and BILLING)

When you use statement routing with ProxySQL you can scale very large estates behind a single target Autonomous AI Database instance, without physically consolidating data.

If your analytics require that you combine objects mapped to different target databases in a single ad-hoc query, you should use complementary approaches, rather than relying on statement routing with ProxySQL. Complementary alternative approaches include:

• Use pre-aggregation or materialized views
• Use per-target jobs with a merge step
• Use a reporting store

The following describes a case where statement routing is a good fit and a case where using statement routing is not a good fit:

• An example that is a good fit: a multi-tenant SaaS application where the application includes the tenant_id and each tenant's schema is mapped to its own target Autonomous AI Database instance (schema-level mapping with object_name => '*'). In this case, every request for a tenant is routed to the right target transparently through the router's mapping table.

• An example that is not a good fit: A top level dashboard that issues one SQL statement joining data for "all tenants at once", when those tenants are mapped to different targets. Because statement routing maps statements/objects to specific targets, an application or report with a cross-target join is not a good fit for statement routing. In this type of application, you need to run multiple per-target queries and merge the results or feed a consolidated reporting area instead.

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

• 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.
• Accept Statement Routing on Target Instances
  You must accept statement routing on a target instance to allow mapping from the router instance.

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 and DML with ProxySQL enabled, you need to connect to the router instance to take advantage of automatic statement routing.

Perform these steps on a target Autonomous AI 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 AI 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.

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

   The object_owner parameter specifies the owner.

   The object_name parameter specifies the table name.

   See REMOVE_MAPPING Procedure for more information.

Shows the steps to automatic statement routing by disabling ProxySQL.

BEGIN
 DBMS_PROXY_SQL.DISABLE_ROUTING;
END;
/

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