Tuning ORDS JDBC Pools on a Live System with JMX: No Restart Required

Oracle REST Data Services (ORDS) relies on the Oracle Universal Connection Pool (UCP) to manage JDBC connections efficiently. In most cases, adjusting connection pool settings—particularly the Max Pool Size—is done through the ORDS configuration layer:

ords config set jdbc.MaxLimit <new value>

A restart then brings the local configuration change into effect. In a typical deployment with multiple ORDS instances behind a load balancer, rolling restarts make this easy and largely transparent.

However, not every environment is set up for easy restarts, and not every situation allows for them. Sometimes uptime requirements, performance testing, or emergency load conditions demand immediate tuning of connection pool behaviour—without recycling the ORDS process.

In this article, we walk through how Java’s JMX (Java Management Extensions) and Attach API can be used to inspect and update ORDS JDBC pool internals at runtime, allowing you to adjust the Max Pool Size dynamically while the instance continues to serve traffic.

Why Adjust UCP Pool Limits Without Restarting ORDS?

Although restarts are the normal path for configuration changes, several real-world scenarios benefit from runtime tuning:

1. Strict SLA Environments

Some deployments run a single ORDS instance—sometimes even in production, although that is never recommended. Restarting may violate uptime guarantees.

2. Sudden Load Spikes

Perhaps unexpected traffic arrives, and the pool is too small. Increasing Max Pool Size instantly alleviates pressure while you evaluate long-term settings.

3. Performance Benchmarking

When running repeatable load tests, restarts introduce variability. Adjusting pools in-place keeps the JVM “warm” and avoids cache resets.

4. Expensive ORDS Startup Contexts

In environments with complex authentication providers, proxies, or metadata hydration, ORDS startup can take longer than ideal. Avoiding that overhead is valuable.

5. Operational Diagnostics

Listing the actual in-memory pools is useful for debugging UCP behaviour, particularly when working with multiple pools created by various ORDS features.

These operational realities make the ability to adjust connection pools live a valuable addition to the ORDS administrator toolkit.

How It Works: JMX + the Java Attach API

Modern Java installations include the Attach API, which allows one JVM to attach to another running JVM, load a Java agent into it, and inspect its internal classes and state.

This makes runtime diagnostics possible without:

  • restarting the process
  • modifying classpaths
  • recompiling anything
  • requiring source code access

Once the agent is loaded, it uses JMX-style introspection and reflection to locate ORDS’s UCP pools, read their attributes, and optionally modify them.

What’s the problem?

There’s really nothing new here. JMX and the Oracle UCP JMX managed beans have been available and documented for years. The utilities for interacting and setting values through JMX such as JConsole, Java VisualVM and Java Mission Control all involve Graphical User Interfaces which means that they can not be used through a basic shell terminal or in a docker container.

JConsole is a GUI and not always easy to run in a container with only shell access.

That’s why I put together the UCPPoolManager.java source file. If you have Java 17 in place to run ORDS, then you have all you need to modify the Maximum Pool Size at runtime.

UCPPoolManager.java

The sample java source code for interacting with the Oracle UCP JMX beans for a specific pool is available at https://gist.githubusercontent.com/pobalopalous/2676390effd70b0c2c1183ff54ca95c0/raw/03d119d9e8f0d2d2c342d0b2d166dc29f9e23c57/UCPPoolManager.java

You can download that source code locally using curl

curl -O https://gist.githubusercontent.com/pobalopalous/2676390effd70b0c2c1183ff54ca95c0/raw/03d119d9e8f0d2d2c342d0b2d166dc29f9e23c57/UCPPoolManager.java

Listing and Updating ORDS JDBC Pools via JMX

The Java class that implements this behaviour is a small standalone utility. You run it directly with the Java interpreter:

java UCPPoolManager.java <PID> [<pool name>] [<new max pool size>]

Examples:

List all JDBC pools

java UCPPoolManager.java 91288

Show statistics for a specific pool

java UCPPoolManager.java 91288 "|default|lo|-2025-11-28T21-14-17.156256Z"

Get the PID for your ORDS instance

So where did I get 91288 as the Process ID for my ORDS instance? Simply by running the Java JPS command line tool and grep for the ords.war. Java JPS (Java Virtual Machine Process Status) is a command-line tool used to list all Java processes running on a system.

jps -ml | grep ords.war

91288 /opt/ords-downloads/ords-25.3.1.289.1312/ords.war serve

Increase the Max Pool Size to 200

java UCPPoolManager.java 91288 "|default|lo|-2025-11-28T21-14-17.156256Z" 200

If a new Max Pool Size is provided, the utility applies the change immediately, without affecting active sessions or request processing.

If no pool name is given, the utility lists all active pools—similar to the ords pool list command.

Diagram 1 — High-Level Flow: Attaching to ORDS at Runtime

+-------------+     Attach API      +-------------------+
|  Utility    | ------------------> |   ORDS JVM        |
|  Process    |                     | (Running Instance) |
+-------------+                     +-------------------+
        |                                    |
        | Load Agent                          |
        |------------------------------------>|
        |                                    |
        |      Agent Introspects UCP Pools   |
        |<------------------------------------|
        |                                    |
        |   Prints Stats / Applies Changes   |
        +------------------------------------+

Code Breakdown: How the JVM Introspection Works

The utility follows a four-stage process to inspect and modify UCP internals.

1. Attach to the Running ORDS JVM

The Attach API connects to the target JVM using its process ID. For that we have an inner class called AutoCloseableResources which will clean up the connection to the VM when it finishes:

    /**
     * Helper class to manage JMX connection resources with try-with-resources
     */
    private static class AutoCloseableResources implements AutoCloseable {
        private final VirtualMachine vm;
        private final JMXConnector connector;
        private final MBeanServerConnection mbsc;
        
        AutoCloseableResources(String pid) throws Exception {
            System.out.println("Connecting to JVM (PID: " + pid + ")...");
            
            this.vm = VirtualMachine.attach(pid);

This lets our code interact with what is running inside the ORDS JVM, giving access to every class already loaded. See com.sun.tools.attach.VirtualMachine java doc

2. Get a Managed Bean Server Connection

Once attached, the Java class checks if a Management Agent is already running and if not, starts it:

String connectorAddress = vm.getAgentProperties().getProperty("com.sun.management.jmxremote.localConnectorAddress");

if (connectorAddress == null) {
   // JMX agent not running, start it
   System.out.println("JMX agent not found, starting it...");
   connectorAddress = vm.startLocalManagementAgent();
}

This agent executes within the ORDS process, allowing it get a JMX connector factory and ultimately a MBeanServerConnection so that our code can:

  • locate UCP pool classes
  • introspect them safely
  • invoke getter/setter methods
  • modify configuration
JMXServiceURL url = new JMXServiceURL(connectorAddress);
this.connector = JMXConnectorFactory.connect(url);
this.mbsc = connector.getMBeanServerConnection();

Note that the agent does not persist beyond the lifecycle of the running JVM. We will use that MBeanServerConnection instance to locate UCP class instances and invoke methods on them.

3. Locate UCP Pools Using JMX Interface

ORDS registers UCP pools in internal static structures. The agent accesses them at runtime by using the UCP JMX interface. First we have to get the main managed bean:

private static ObjectName findManagerMBean(MBeanServerConnection mbsc) throws Exception {
   ObjectName pattern = new ObjectName(UCP_DOMAIN + ":*");
   Set<ObjectName> mbeans = mbsc.queryNames(pattern, null);
        
   for (ObjectName mbean : mbeans) {
       if (mbean.toString().contains(POOL_MANAGER_MBEAN)) {
            return mbean;
       }
   }
   throw new RuntimeException("UniversalConnectionPoolManagerMBean not found");
}

Because the agent runs inside the ORDS process, the correct UCP version is always present—no external libraries needed.

4. Optionally Modify the Max Pool Size

If the user specifies a new Max Limit, the tool calls the setter:

Method setMax = poolClass.getMethod("setMaxPoolSize", int.class);
setMax.invoke(pool, newValue);

UCP applies the change immediately.

No restart required.
No dropped sessions.
No disruption to traffic.

Diagram 2 — Inside JVM: How UCP Pools Are Adjusted

                    +---------------------------+
                    |   ORDS JVM (Target)       |
                    |---------------------------|
                    |  UCP Pool Registry        |
                    |    • pool A               |
                    |    • pool B               |
                    |    • pool C               |
                    |---------------------------|
                              ^
                              |
                      Reflection Lookup
                              |
                        +-----------+
                        |  Agent    |
                        |  Code     |
                        +-----------+
                              |
                     Getter/Setter Calls
                              |
                   +---------------------+
                   | MaxPoolSize Updated |
                   +---------------------+

Conclusion: Opening the Door to Live Pool Tuning

Note that this approach is not limited to ORDS but applies to any Java application using Oracle Universal Connection Pool. There’s nothing really ORDS specific in the utility class.

Being able to inspect and adjust Universal Connection Pools at runtime gives administrators new flexibility, particularly in constrained or high-availability environments. With the help of the Java Attach API and JMX-style introspection, you can safely:

  • list current pool statistics
  • examine their behaviour under load
  • adjust Max Pool Size dynamically
  • avoid restarts and prevent service disruption

This technique isn’t a replacement for proper ORDS configuration management, but it fills a valuable gap when immediate action is needed or when restarts are impractical.

Potential Next Steps

If you want to build on this approach, consider:

✔ Adding Support for Additional UCP Parameters

Such as:

  • Borrow Timeout
  • Initial Pool Size
  • Abandoned Connection Timeout

All of these can be adjusted via reflection just as easily as MaxPoolSize.

✔ Implementing Auto-Scaling Policies

Dynamically grow or shrink pools based on:

  • DB workload
  • request latency
  • CPU saturation
  • wait times

This could move your ORDS topology a step closer to cloud-native elasticity. However, that’s a conversation to have with your DevOps team because they may already have tools and policies in place for auto-scaling.

Zero-Code Vector Search for Oracle Database 26ai

Oracle REST Data Services (ORDS) 26.1.0 is out and one of the new features is Vector Search which we’ll explore in this article. Before diving in, this post assumes you have already worked through:

👉 Getting Started with Vectors in 23ai

That article is the foundation and introduces the CCNEWS dataset (200,000 news headlines) along with embedding generation and SQL-based vector search. The step by step article is easy to follow and even involves using ORDS to define a REST service for performing that vector search. What is also nice about the article is that everything it covers is easy to implement and does not require any external services. In my case, I run all of it locally using ORDS and Oracle AI Database 26ai Free Container Image in docker.

Here, we build directly on that article and show how Oracle REST Data Services 26.1.0 simplifies exposing that Vector Search functionality over REST with no code in Oracle Database 26ai.

1. The CCNEWS Table (From the Prerequisite)

Assuming you have followed all the steps in the article you have a 26ai database with a VECTOR_USER schema that has a CCNEWS table.

CCNEWS table with content

So that table was created with this statement from the original blog post:

create table if not exists CCNEWS (
    id number(10) not null,
    info VARCHAR2(4000),
    vec VECTOR
);

Important: Primary Key Requirement

ORDS Vector Search on REST enabled objects requires a primary key. If not already defined, run this:

ALTER TABLE VECTOR_USER.CCNEWS
ADD CONSTRAINT CCNEWS_PK PRIMARY KEY (ID);

2. Vector Search in SQL (From the Article)

The Database & Cloud Technology article uses Oracle’s VECTOR_DISTANCE function combined with VECTOR_EMBEDDING for a similarity search:

select id, info
from CCNEWS
order by vector_distance(vec, TO_VECTOR(VECTOR_EMBEDDING(doc_model USING 'little red corvette' as data)), COSINE)
fetch approx first 5 rows only;

Key points:

  • VECTOR_EMBEDDING generates the query vector
  • VECTOR_DISTANCE computes similarity
  • COSINE is used as the distance metric
  • FETCH APPROX limits the number of rows returned using an approximate nearest neighbour search

3. The ORDS Handler Approach

To expose that vector_distance based query via ORDS previously, you needed a custom service:

BEGIN
  ORDS.define_service(
    p_module_name    => 'vectorsearch',
    p_base_path      => 'ai/',
    p_pattern        => 'ccnews/:mysentence',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT id,info from CCNEWS 
                         order by vector_distance(
                           vec, 
                           TO_VECTOR(VECTOR_EMBEDDING(doc_model USING :mysentence as data)), 
                           EUCLIDEAN
                         ) 
                         fetch approx first 5 rows only',
    p_items_per_page => 0);  COMMIT;
END;
/

Call it

curl 'http://localhost:8080/ords/vector_user/ai/ccnews/little%20red%20corvette'

That will get you a response like this:

{
   "count" : 5,
   "hasMore" : false,
   "items" : [
      {
         "id" : 142622,
         "info" : "CHICAGO -- Small cars have been getting great copy lately. They're lauded for their anti-gas guzzling efficiency and improved safety features."
      },
      {
         "id" : 79167,
         "info" : "A red car will start on pole as usual, but the probability that Ferrari will end the season with yet another win, their 16th in 18 races, is not quite as grim as it sounds."
      },
      {
         "id" : 182103,
         "info" : "With her red velvet hooded cape, Cybill Shepherd could have been Little Red Riding Hood."
      },
      {
         "id" : 182000,
         "info" : "When it comes to helping the environment or saving money at the pump, it's hard to beat one of the new mini-cars, like the Nissan Versa, Toyota Yaris and Mini Cooper."
      },
      {
         "id" : 57282,
         "info" : "Customized cars that use commercial logos as a creative palette."
      }
   ],
   "limit" : 0,
   "links" : [
      {
         "href" : "http://localhost:8080/ords/vector_user/ai/ccnews/little%20red%20corvette",
         "rel" : "self"
      },
      {
         "href" : "http://localhost:8080/ords/vector_user/metadata-catalog/ai/ccnews/item",
         "rel" : "describedby"
      }
   ],
   "offset" : 0
}

Observations

  • The query string (:mysentence) is passed directly into VECTOR_EMBEDDING
  • Distance metric is explicitly set to EUCLIDEAN
  • ORDS handles binding, but you still manage:
    • Module definition
    • URL structure
    • SQL text

This works—but it involves encoding a query as a service.

4. ORDS 26.1.0: Built-in Vector Search

With ORDS 26.1.0, none of that is required for basic vector search.

Step 1: Enable AutoREST

As the VECTOR_USER run this to REST Enable the CCNEWS table.

BEGIN
  ORDS.enable_object(
    p_object       => 'CCNEWS'
  );
  COMMIT;
END;
/

That will give you a huge range of RESTful services for interacting with the table without any coding.

The OpenAPI V3 document describing the operations now available for the CCNEWS table.

Step 2: Use the Built-in Endpoint

Let’s look at the POST /vectorSearch operation. To perform the Vector Search there are certain values that must be provided in the request body. The ORDS Vector Search documentation describes them all so we will not go into details here.

Performing a Vector Search in this case requires a POST

The full path for the Vector Search operation on the CCNEWS table in the VECTOR_USER will look like this:

POST /ords/vector_user/ccnews/vectorSearch

5. Equivalent Vector Search via ORDS

Instead of embedding generation happening inside SQL, the client supplies the vector:

curl -X POST http://localhost:8080/ords/vector_user/ccnews/vectorSearch \
  -H "Content-Type: application/json" \
  -d '{
    "vector": [/* embedding for "little red corvette" */],
    "vectorColumn": "VEC",
    "columns": ["ID", "INFO"],
    "limit": 5,
    "distanceMetric": "EUCLIDEAN"
  }'

The client will have to know the AI model used for populating the VECTOR data but it has a wider range of options. That includes further filtering of rows in the same manner as what can be achieved through GET on the same table. See Filtering In Queries section of the ORDS documentation.

Key Difference vs Custom Handler Approach

Custom HanderAutoREST Vector Search
Embedding generated in SQL (VECTOR_EMBEDDING)Embedding supplied by client
Custom handler requiredBuilt-in endpoint
SQL embedded in ORDSNo SQL required
URL parameter (:mysentence)JSON payload
No refinement on search criteriaClient can specify order, number of rows to return, specific columns to include in the response and additional filter criteria.
Primary Key Hyperlinks require extra codingAutomatic Primary Key Hyperlinks for each row in the response.

Keep in mind that both approaches can be used at the same time. In some scenarios the client will know the model for the table or have the means to generate a vector array to search with.

Note that as with the initial article, the services provided are not secured and the ORDS instance is not using HTTPS for traffic. That’s fine for this exercise but not for production.

6. Let’s see it in action

Since the client now has to provide the vector to search for, to perform the exact same search one must get an embedding for little red corvette using the same model. Easily done by running:

select VECTOR_EMBEDDING(doc_model USING 'litle red corvette' as data)
The vector representation of ‘little red corvette’

Using that in a cURL command will look like this:

curl --location 'http://localhost:8080/ords/vector_user/ccnews/vectorSearch' \
--header 'Content-Type: application/json' \
--data '{
  "limit": 5,
  "vector": [-3.07053532E-002,5.85529916E-002,-6.74578473E-002,1.2257684E-002,6.316524E-002,-2.40801703E-002,7.64445635E-003,3.26403342E-002,-5.40543646E-002,6.23201914E-002,8.92788619E-002,6.09119842E-003,8.08357634E-003,-2.87142694E-002,-3.36633772E-002,-1.59669053E-002,1.10877827E-001,5.03755994E-002,-3.0524137E-003,2.27314071E-003,-8.80712047E-002,4.54846956E-002,-1.76324453E-002,5.4300325E-003,-7.49083012E-002,-7.84977898E-003,-1.03094578E-001,3.23520638E-002,-5.20449802E-002,-8.81914124E-002,5.09979203E-003,-2.28075747E-004,8.27756822E-002,1.80074794E-003,-2.35566851E-002,-5.18809892E-002,2.15939945E-003,-2.15763152E-002,-4.92988527E-002,4.24234103E-003,-3.89126204E-002,-1.08256757E-001,1.64869614E-002,1.02524489E-001,9.25767645E-002,-2.06774045E-002,-1.8368477E-002,1.33422706E-002,8.78902823E-002,-6.02279529E-002,3.43158655E-002,-9.60535407E-002,-2.74095945E-002,-2.8675979E-002,4.55864891E-002,2.63328925E-002,1.72216333E-002,-1.97178405E-002,6.42947182E-002,-2.79306769E-002,-2.80832332E-002,3.09923105E-002,-2.81780143E-003,-2.21623778E-002,1.51646696E-002,-5.2848449E-003,3.95465866E-002,-3.46338265E-002,-5.43230511E-002,3.11623141E-002,3.56283262E-002,-3.72483805E-002,-3.45825478E-002,-5.79019776E-003,-5.63643761E-002,-3.12294867E-002,8.83353204E-002,-3.03997919E-002,-1.23773552E-002,-5.15865511E-004,-3.59914801E-003,-1.69582665E-002,-7.76734948E-002,-4.63231243E-002,6.63533583E-002,-3.5777919E-002,4.40812893E-002,5.63600361E-002,-5.28740976E-003,5.69755659E-002,-5.50081208E-002,6.87551275E-002,3.28732915E-002,1.58981874E-003,-5.56428321E-002,-7.57648051E-002,5.8034081E-002,-6.68441579E-002,-3.73762697E-002,1.87148571E-001,1.12300172E-001,4.12532054E-002,1.00773335E-001,5.72219677E-003,-3.81047241E-002,3.35666686E-002,7.77937025E-002,4.77051288E-002,-2.15624534E-002,-1.18911706E-001,-2.00342126E-002,7.05829309E-003,-8.89590383E-002,-2.68359855E-002,-6.41334206E-002,-4.22521196E-002,-7.08332583E-002,-5.96234351E-002,-3.76171395E-002,3.2652244E-002,-4.96881641E-002,-7.7103138E-002,-2.48034019E-002,5.12335682E-003,7.085336E-002,-1.47623777E-001,5.37105873E-002,3.64335366E-002,-2.60975622E-002,5.82129369E-003,-8.14416409E-002,6.67800754E-002,2.08732914E-002,1.19046867E-001,5.18846847E-002,9.57678165E-003,-3.64857614E-002,7.76776895E-002,-3.93842794E-002,-9.8264575E-002,-4.7032699E-002,-5.16803861E-002,3.27760428E-002,3.26377489E-002,-6.59162626E-002,-1.05144009E-001,-3.22745182E-002,-5.92551716E-002,-1.13562755E-001,1.25622064E-001,1.14990778E-001,8.03940892E-002,7.66430572E-002,5.89383543E-002,-9.20753181E-003,-3.60060111E-002,-2.22714879E-002,6.28050696E-003,3.42132933E-002,6.13801964E-002,1.75386257E-002,-1.80134606E-002,-3.39894332E-002,-2.67520901E-002,-3.81485149E-002,-1.25639021E-001,2.98075918E-002,-4.81692702E-003,-5.38430316E-003,-2.68271938E-002,-8.80813152E-002,7.9674378E-002,8.1708869E-003,4.49210731E-003,4.93363179E-002,9.08218604E-003,2.98463684E-002,1.67458635E-002,4.30809148E-002,-6.30635628E-003,-3.66868116E-002,2.24808641E-002,-1.97107941E-002,4.19183187E-002,-2.07382943E-002,7.16343299E-002,-1.17987087E-002,2.83470232E-004,5.55337667E-002,3.85905765E-002,2.60068625E-002,2.47582365E-002,5.64134903E-002,7.59846391E-003,-6.95330277E-002,-5.1294744E-002,-2.89460607E-002,3.93225159E-003,2.4446765E-002,-5.46594113E-002,4.43796702E-002,-7.10089058E-002,3.78895737E-002,-7.63406715E-005,3.07969283E-002,-1.99558139E-002,1.54345371E-002,-8.88657868E-002,8.84383637E-003,-2.23286971E-002,-2.45919079E-002,-3.978635E-002,5.16027845E-002,-2.32025366E-002,-2.45015081E-002,7.69026503E-002,-2.96384431E-002,1.53524885E-002,-2.90377047E-002,-2.80109104E-002,4.53478694E-002,-3.58900763E-002,2.99728755E-002,-1.45389412E-033,1.88392568E-002,-6.47298545E-002,1.01908617E-001,-1.31587433E-002,-1.85926091E-002,-2.86066998E-002,-1.33587588E-002,-1.80519139E-003,-1.71739608E-002,-4.6595633E-002,-4.6931956E-002,8.30989629E-002,1.53468493E-002,-5.12199767E-004,1.04117207E-001,-1.2125371E-002,-1.8739434E-002,4.73202318E-002,2.74012014E-002,8.04362372E-002,-2.32837833E-002,-7.19153807E-002,2.11932212E-002,2.4031328E-002,-3.77282277E-002,5.17850779E-002,-6.48160726E-002,3.25367749E-002,-2.89279353E-002,-6.88138753E-002,-2.91798194E-003,-4.56949286E-002,2.95383874E-002,1.14016305E-003,-3.78589332E-002,-6.03643758E-003,2.98500806E-002,-4.85490002E-002,4.10790853E-002,-2.5354486E-002,-9.72109195E-003,-2.7718816E-002,-4.33942713E-002,6.56506717E-002,9.87958163E-004,-6.58345083E-003,5.29988557E-002,-1.82564501E-002,6.05667047E-002,-2.87184888E-003,1.63399044E-003,-8.17960426E-002,2.47597024E-002,3.14391218E-002,-2.03312412E-002,4.33304794E-002,4.25058976E-002,6.37011081E-002,3.73960882E-002,-1.86836459E-002,1.70387905E-002,2.79710498E-002,-3.8476523E-002,-2.18143836E-002,-2.01852974E-002,-2.65123788E-002,-1.21113239E-002,1.73476618E-002,-2.11952161E-002,-2.85385773E-002,-8.24076403E-003,2.16407981E-002,9.9034654E-003,-4.52033617E-002,-1.22422148E-002,1.14305643E-002,9.72609892E-002,4.52578627E-002,4.41269763E-002,-2.55465172E-002,-3.49305272E-002,-5.51500618E-002,5.64764142E-002,1.05597645E-001,-1.18475445E-002,-6.03516959E-002,4.99546118E-002,4.38831076E-002,-4.56738211E-002,4.00517136E-002,1.65134482E-002,8.81506428E-002,-2.3371242E-002,9.05854441E-003,-1.08199259E-002,1.63877298E-032,-1.96534507E-002,6.13839971E-003,2.32135262E-002,-3.43828648E-002,5.41330278E-002,1.27508137E-002,5.94790131E-002,-6.85228733E-003,-9.60054323E-002,-6.71492144E-002,-5.39250374E-002,-1.74073465E-002,1.59162041E-002,-7.3701553E-002,-2.2581771E-002,3.65978405E-002,1.47042209E-002,-7.16396943E-002,7.73388445E-002,-1.16045386E-001,2.4834685E-002,7.23040178E-002,3.32084112E-002,3.31360511E-002,1.10893575E-002,-9.28622484E-002,-4.19785641E-002,2.29144283E-002,9.44261849E-002,-8.33413657E-003,2.91968007E-002,5.88697419E-002,-1.39651537E-001,-6.11314899E-004,2.57693212E-002,-4.38742265E-002,1.74329523E-002,8.57599378E-002,5.69615103E-002,-9.78489816E-002,1.14794098E-001,-1.86840519E-002,-7.20299184E-002,3.69497389E-002,1.7280845E-002,2.3233227E-003,2.18159296E-002,-7.62700588E-002,4.16742787E-002,-3.8183827E-002,2.58755721E-002,4.36592326E-002,-3.85195836E-002,-1.25658726E-002,-6.86033443E-002,-2.30002753E-003,-4.88031134E-002,5.49012981E-002,-8.51705298E-002,2.89389715E-002,-4.43107588E-003,-2.64256331E-003,-2.68490277E-002,2.34105438E-002]
}'

Since it is not easy to manage long cURL commands like this I imported the OpenAPI V3 document from http://localhost:8080/ords/vector_user/open-api-catalog/ccnews/ into Postman..

The CCNEWS operations available to be called from Postman

Making that little red corvette search now looks like this:

The request payload is just two attributes: limit and vector

Everything else, such as what columns to return or what distance metric to use, will default. The response will look like this:

Response includes the matching rows and also a link to the individual row on the table

Runtime error?

If you are getting a ORA-25716: The identifier contains a double quotation mark (") character exception when calling the /vectorSearch service then check that you have defined a primary key for the CCNEWS table.

7. Distance Metric Comparison

Notice an important shift:

  • Old handler used: EUCLIDEAN
  • SQL example used: COSINE
  • ORDS allows either via distanceMetric

Example

{
  "distanceMetric": "EUCLIDEAN"
}

or

{
  "distanceMetric": "COSINE"
}

Impact at Scale (200K Headlines)

  • COSINE → best semantic similarity (recommended for text)
  • EUCLIDEAN → slightly different ranking due to magnitude sensitivity

This is now a runtime choice, not hardcoded in SQL.

8. What Changed Architecturally?

Then

  • SQL handled:
    • embedding generation
    • similarity calculation
  • ORDS:
    • exposed SQL via REST

Now (ORDS 26.1.0)

  • Database:
    • stores vectors
    • performs similarity search
  • Client:
    • generates embeddings
  • ORDS:
    • provides a native vector search endpoint

Final Thoughts

Using the same CCNEWS dataset from the prerequisite article, we’ve moved from:

Custom REST services wrapping SQL

to:

Native vector search exposed automatically via ORDS

With Oracle Database 26ai + ORDS 26.1.0, the requirements are minimal:

  • A VECTOR column
  • A primary key
  • One ORDS.enable_object call

And your dataset becomes:

A fully functional semantic search API

Understanding ORDS db.connectionType: A Practical Comparison of All Four Connection Models

Oracle REST Data Services (ORDS) supports four different ways to create database connection pools, each designed for a specific deployment model or infrastructure environment. While most people are familiar with the common Basic connection settings, the other options—TNS, Custom URL, and Database Tools Connection—offer powerful alternatives when you need more flexibility or cloud-native authentication.

This article provides a concise comparison of all four db.connectionType values, explains when each option makes sense, and lists the configuration settings required for each type.

Why db.connectionType Matters

Every ORDS deployment includes one or more database pools. The connection type determines:

  • how ORDS constructs the JDBC URL
  • where host/service metadata comes from
  • whether credentials are stored locally or remotely
  • what additional ORDS settings are required

Choosing the right connection type simplifies configuration, avoids hard-coding credentials, and ensures ORDS connects using the most appropriate mechanism for the environment.

The Four ORDS Connection Types

1. Basic — Direct Host/Port/Service Connections

This is the simplest and most commonly used configuration. ORDS constructs the JDBC URL automatically based on host, port, and service name.

Use this when:

  • the database is customer-managed (on-prem or IaaS)
  • you want simple, explicit connection parameters
  • you’re not using TNS descriptors or OCI integrations

Required Settings:

db.connectionType=basic
db.hostname=<host>
db.port=<port>
db.servicename=<service>
db.username=<db user>
db.password=<secret>

Note that as a secret, the db.password value is expected to be persisted in a wallet file locally and set using the ORDS command line interface.

2. TNS — Use tnsnames.ora

When you choose the tns connection type, ORDS reads the connect descriptor from a tnsnames.ora file. This allows you to use advanced Oracle Net features such as load balancing, failover, and connect-time routing.

Use this when:

  • your organisation standardises on Oracle Net
  • you need RAC/Service failover descriptors
  • TNS files already exist and are centrally managed

Required Settings:

db.connectionType=tns
db.tnsDirectory=<directory containing tnsnames.ora>
db.tnsAlias=<alias in tnsnames.ora>
db.username=<db user>
db.password=<secret>

Note that as a secret, the db.password value is expected to be persisted in a wallet file locally and set using the ORDS command line interface. Moreover, the directory containing the tnsnames.ora must be accessible to the ORDS runtime instance. This can be over a networked filesystem but must be readable by the ORDS process.

3. Custom URL — Full JDBC URL Control

The customurl connection type gives you complete control of the JDBC URL. ORDS simply uses whatever you provide. This is the most flexible option and the only one suitable for non-Oracle databases.

Use this when:

  • you are connecting to MySQL or another JDBC-compatible database
  • you need URL parameters not supported by Basic or TNS
  • you prefer to manage the full connection string manually

Required Settings:

db.connectionType=customurl
db.customURL=jdbc:oracle:thin:@//host:port/service
# or MySQL, PostgreSQL etc.
db.username=<db user>
db.password=<secret>

Useful for cases like:

db.customURL=jdbc:mysql://10.0.1.23/?sslMode=REQUIRED

Note that as, a secret the db.password value is expected to be persisted in a wallet file locally and set using the ORDS command line interface. ORDS only ships with the Oracle JDBC driver so if using a different JDBC driver the jar files that must be in the lib/ext directory.

4. Database Tools Connection — Cloud-Native Authentication via OCI

This option integrates ORDS with Oracle Cloud Infrastructure Database Tools, allowing ORDS to use an OCI-managed “Database Tools Connection” object. Credentials are stored in OCI Vault, not in ORDS configuration files. See https://docs.oracle.com/en-us/iaas/database-tools/home.htm for more information on this managed service.

This is the most secure and most cloud-native way to connect ORDS to an Autonomous Database or Oracle Base Database running in OCI.

Use this when:

  • ORDS is running inside OCI (Compute, Functions, OKE)
  • you want to avoid storing DB passwords in file systems
  • you want automatic credential rotation via Vault
  • you manage DB access through OCI IAM policies

Required Settings:

When your OCI compute instance is configured in a dynamic group which contains a policy granting it access to the dbtools service…

db.connectionType=databaseToolsConnection
db.databaseToolsConnection=<OCID of Database Tools Connection>

When your not running ORDS on an OCI compute instance but you have the OCI Command Line Interface installed…

db.connectionType=databaseToolsConnection
db.databaseToolsConnection=<OCID of Database Tools Connection>
db.authProvider=oci-profile
db.ociProfile=<only if using oci-profile>

Remember: No hostname, port, service name, or password required — OCI provides everything.

Quick Comparison Table

TypeURL SourceCredentials Stored Locally?Suitable ForNotes
basicHost/port/service nameYesOn-prem & simple setupsMost common
tnstnsnames.ora descriptorYesRAC, complex descriptorsCentralised Net configs
customurlFull JDBC URL provided by youYesNon-Oracle DBs, exotic configsMax flexibility
databaseToolsConnectionOCI Database Tools metadataNoCloud-native deploymentsBest security

Choosing the Right Connection Type

If you’re unsure which to pick, here’s a simple decision flow:

  • Running on OCI and want the best security?
    → Use databaseToolsConnection
  • Need RAC/SCAN/FAN/Load Balancing descriptors?
    → Use tns
  • Connecting to MySQL or need a very specific JDBC URL?
    → Use customurl
  • Standard on-prem or IaaS database with straightforward networking?
    → Use basic

Final Thoughts

ORDS has grown far beyond its early single-connection configuration model. Understanding the four db.connectionType settings helps you choose the best option for your environment—whether that’s a classic on-premises Oracle instance or a fully cloud-native, password-free deployment in OCI.

If you’re standardising ORDS across multiple environments, I recommend experimenting with each type in a test pool. You’ll quickly see which one aligns with your organisation’s approach to connectivity, configuration management, and security.

Find those unused Oracle Database Accounts (Easily!)

Managing Oracle database accounts is an essential part of database security and operational hygiene. One key task is identifying accounts that have not been used for extended periods, such as over 30 days. While queries against DBA_USERS or ALL_USERS provide basic metadata about database accounts, they do not reliably indicate actual usage, particularly for REST-enabled schemas. This is because Oracle REST Data Services (ORDS) and other middleware may connect to the database via a “connect through” mechanism, creating sessions on behalf of the schema without a traditional direct login.

For precise auditing of user activity, Oracle 26ai’s Unified Audit framework offers a reliable solution.

What is a Unified Audit Policy?

Unified Auditing consolidates Oracle’s auditing mechanisms into a single, consistent framework. It allows administrators to define audit policies that track database activity across sessions, users, and even system-level events.

A Unified Audit Policy is a set of rules that specifies which actions are audited. When enabled, it records events in the UNIFIED_AUDIT_TRAIL view, including logins, logouts, DML operations, and privilege usage. This provides a reliable, timestamped record of actual database activity.

Define The Policy

Oracle 26ai ships with a predefined Unified Audit Policy called ORA_LOGIN_LOGOUT. This policy is designed to track user logins and logouts. However, by default it only tracks failures unless your System Administrator has altered the policy definition. So lets verify your audit policy state.

To confirm that Unified Audit is enabled in your database run this query. It should return TRUE.

SELECT VALUE
FROM V$OPTION
WHERE PARAMETER = 'Unified Auditing';

Now check the status of the predefined ORA_LOGIN_LOGOUT policy…

SELECT * FROM audit_unified_enabled_policies where POLICY_NAME = 'ORA_LOGIN_LOGOUT';
The results could look like this indicating that only failed attempts are recorded.

If the predefined ORA_LOGIN_LOGOUT policy is not recording on SUCCESS then define a new policy to achieve that and activate it…

CREATE AUDIT POLICY ALL_LOGONS ACTIONS LOGON;

AUDIT POLICY ALL_LOGONS;

You can verify that policy definition and that it is enabled by running the following queries:

SELECT * FROM audit_unified_enabled_policies where POLICY_NAME = 'ALL_LOGONS';

select * from AUDIT_UNIFIED_POLICIES where policy_name = 'ALL_LOGONS';

You can verify that LOGON activity is now being recorded based on this policy by checking the UNIFIED_AUDIT_TRAIL:

SELECT * from UNIFIED_AUDIT_TRAIL where UNIFIED_AUDIT_POLICIES = 'ALL_LOGONS' order by EVENT_TIMESTAMP_UTC desc;

Refer to Oracle documentation for more details: Unified Audit Policies.

Identifying Inactive Users

Now that you are recording the LOGON activity you can review the audit trail view. To find database accounts that have not logged in for over 30 days, you can query the UNIFIED_AUDIT_TRAIL using the following SQL:

SELECT
    UAT.DBUSERNAME,
    MAX(UAT.EVENT_TIMESTAMP_UTC) AS LAST_LOGIN_UTC
FROM
    UNIFIED_AUDIT_TRAIL UAT,
    ALL_USERS           AU
WHERE
    UAT.ACTION_NAME = 'LOGON'
    AND UAT.DBUSERNAME = AU.USERNAME
GROUP BY
    UAT.DBUSERNAME
HAVING
    MAX(UAT.EVENT_TIMESTAMP_UTC) < SYSTIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '30' DAY
ORDER BY
    LAST_LOGIN_UTC DESC;

Explanation of the query:

  • DBUSERNAME identifies the database account.
  • EVENT_TIMESTAMP_UTC captures the precise UTC timestamp of the last login.
  • MAX(EVENT_TIMESTAMP_UTC) gives the most recent login per user.
  • The join on ALL_USERS ensures you only see database users that still exist.
  • HAVING MAX(EVENT_TIMESTAMP_UTC) < SYSTIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '30' DAY filters accounts that have been inactive for over 30 days.
  • ORDER BY LAST_LOGIN_UTC DESC lists the accounts from least to most recently active.

This approach is far superior to just using DBA_USERS.LAST_LOGIN or ALL_USERS because it captures all session activity, including sessions initiated via REST services like ORDS. Accounts that appear “inactive” in DBA_USERS may, in fact, be used regularly through REST-enabled access.

Why DBA_USERS or ALL_USERS Isn’t Enough

Traditional queries like:

SELECT username, last_login
FROM dba_users;

or

SELECT username
FROM all_users;

have limitations:

  1. No REST context – They only reflect direct database logins, missing sessions created via middleware using “connect through” mechanisms.
  2. Potentially outdated – DBA_USERS.LAST_LOGIN is only updated on direct logins.

Unified Auditing solves all these limitations by taking advantage of UNIFIED_AUDIT_TRAIL, recording every login event centrally, giving administrators a reliable view of database account activity.

Conclusion

By taking advantage of UNIFIED_AUDIT_TRAIL, administrators can:

  • Accurately determine which accounts are truly inactive.
  • Include REST-enabled schemas in their audits.
  • Improve security by identifying accounts that may no longer require access.

If it is enabled for SUCCESS then the ORA_LOGIN_LOGOUT Unified Audit Policy is the recommended approach for identifying inactive database accounts in Oracle 26ai. Otherwise, you will have to define a new policy as detailed in this article.

Enabling and managing the audit policies is a critical best practice for modern Oracle databases, particularly those supporting REST services through ORDS. It is important to also have procedures in place to archive old audit trail records so they do not fill disk space.

What audit policies for ORDS do you have in place for monitoring activity?

Faster Database Authentication with 23ai

Oracle REST Data Services (ORDS) 25.3.0 has just been released, and one of the highlights for developers is a significant performance improvement for Basic Authentication when paired with the latest Oracle 23ai database patch. In this article, we’ll show real-world “before and after” results from requests to an ORDS handler running in the Autonomous Database (ADB) Frankfurt region, demonstrating how ORDS 25.3.0 drastically reduces authentication response times.

Dramatic improvement on all metrics!
All values are milliseconds.

Note: It is recommended to use OAuth for authentication and authorisation rather than Basic Authentication where possible.

Background

Basic Authentication is widely used in ORDS for accessing secured REST endpoints. Note that, one is recommended to use OAuth2 over Basic Authentication for both performance and security reasons. See the ORDS Best Practices document. However, that is not always possible because for some clients their only authentication mechanism available is Basic Authentication. While simple to implement, verifying usernames and passwords can introduce latency, especially under load or in high-frequency request scenarios. Of course the biggest security issue is that one is sending username and password credentials for a database user over the network with every request. Even with HTTPS, it is best to avoid this practice.

For our tests, we used the events query handler from the ORDS sample application. This handler is part of the ORDS Sample Application ( also known as ORDS Concert App ), which you can explore here: ORDS Remix JWT Sample.

All tests were executed against an Autonomous Database instance in the Frankfurt region to ensure realistic production-like conditions.

Test Setup

We compared two configurations:

  • ORDS 25.2.3 (previous version)
  • ORDS 25.3.0 (latest release)
  • Oracle 23ai database with the most recent RU, which contains enhancements specifically for faster ORDS basic authentication. More specifically, that’s RDBMS 23.10 which has been available in Autonomous Database for about a week now.

The test scenario:

  • A k6 script making 100 sequential requests to the ORDS handler
  • 200ms pause between requests to avoid ADB rate limiting (all requests came from the same IP)
  • k6 executed from Dublin, with ORDS and the database hosted in Frankfurt, introducing a consistent network latency
  • The protected service is the ORDS Concert App events handler.

Note: The details of the k6 script and execution environment will be provided in a dedicated section below.

Before: ORDS 25.2.3 Performance

The statistics for ORDS 25.2.3, recorded a few days ago at the same time of day as the latest test, are as follows (all durations in milliseconds):

MetricDuration (ms)
Avg160.448
Min108.257
Med129.354
Max1118.475
P90183.042
P95204.627

Analysis:

  • Average response time was ~160ms
  • Maximum response exceeded 1 second, highlighting potential bottlenecks
  • P95 and P90 values show noticeable tail latency, which can affect user experience

After: ORDS 25.3.0 Performance

After upgrading to ORDS 25.3.0 and applying the latest Oracle 23ai patch, the same test yielded dramatically improved results:

MetricDuration (ms)
Avg59.195
Min51.607
Med54.811
Max273.553
P9065.565
P9568.708

Analysis:

  • Average response time reduced by ~63%
  • Minimum response time shows roughly a 52% improvement, but compared with the 63% average improvement, it suggests that the fastest possible request was already bounded mostly by network latency (Dublin → Frankfurt).
  • Maximum response reduced by ~76%
  • P90 and P95 values significantly lower, resulting in more consistent and predictable performance
  • The improvements are immediately noticeable for real-world API requests

Why the Improvement Happens

The performance gains are made possible by enhancements in Oracle 23ai that were introduced specifically for ORDS:

  • Faster verification of username and password
  • Tight integration as a trusted Oracle database tools application
  • Optimized authentication path within ORDS 25.3.0
The faster authentication for ORDS basic auth was introduced in RDBMS 23.10

In practice, this means that if basic authentication is your only option because of the client or integration tool used for consuming your ORDS REST services you will see a noticeable improvement in response times, even in high-volume scenarios.

One such example is the Oracle Database API for MongoDB where basic authentication is the only option with the MongoDB Wire protocol interface. Improving the connection time for customers using Oracle RDBMS to persist data for their MongoDB applications was the primary driver for this initiative with ORDS and 23ai.

OAuth Bearer Token Performance

The use of OAuth is still recommended over Basic Authentication because it is more secure. It was dramatically faster than Basic Authentication too but as you can see from these OAuth bearer token test runs for the same service there is almost parity on response times now.

MetricDuration (ms)
Avg54.966722 ( 5ms better )
Min38.9794 ( 12ms better )
Med45.7988 ( 9ms better )
Max234.4554 ( 39ms better )
P9074.56998 ( 9ms worse )
P9587.19764 ( 18ms worse )

Observations:

  • Apart from the P90 and P95 metrics the OAuth bearer token is still more performant
  • These tests just involve 1 single client making sequential requests and the performance profile with multiple concurrent requests should be significantly beter.
  • Consider using OAuth JWT Profile with an external identify provider for even faster response times.

k6 Script Details and Execution

Here is the sequential_100.k6 script that was used to record the response times and avoid the Autonomous Database request rate limiting on the OCI Free Tier.

import http from 'k6/http';
import { check, sleep } from 'k6';
import { Trend, Rate } from 'k6/metrics';
import encoding from 'k6/encoding';

// Test configuration
export let options = {
  vus: 1,              // single VU so requests are sequential
  iterations: 1,      // run default() once (we loop 100 times inside)
  thresholds: {
    // Require 100% of requests to be successful (status 200)
    'successful_requests': ['rate==1'],
    // Example response-time threshold (optional) - adjust to your needs
    'response_time_ms': ['p(95) < 500']
  },
};

// Request details. Change these for your environment.
const URL = 'https://mytenancy-my23ai.adb.eu-frankfurt-1.oraclecloudapps.com/ords/ords_concert_app/authuser/v1/events/';
const USER = 'ORDS_CONCERT_APP';
const PASS = 'MyConcertAppDatabaseUserPassword';

// Basic auth header
const AUTH_HEADER = 'Basic ' + encoding.b64encode(`${USER}:${PASS}`);

// Metrics
let responseTimeTrend = new Trend('response_time_ms');
let successRate = new Rate('successful_requests');

export default function () {
  for (let i = 0; i < 100; i++) {
    // Perform request with Authorization header
    let res = http.get(URL, {
      headers: {
        Authorization: AUTH_HEADER,
        'Accept': 'application/json'
      }
    });

    // Record timing (milliseconds)
    responseTimeTrend.add(res.timings.duration);

    // Assert HTTP 200 for each request and update success rate
    const ok = check(res, {
      'status is 200': (r) => r.status === 200
    });
    successRate.add(ok);

    // Log any failures to console for quick debugging
    if (!ok) {
      console.error(`Request #${i + 1} failed — status: ${res.status}, body: ${res.body ? res.body.slice(0,200) : 'empty'}`);
    }

    // Wait 200 ms between requests to respect rate limits
    sleep(0.2);
  }
}

Some details to call out:

At line 7 we define the test options which specify how many concurrent users ( 1 in this case ) and how many requests to send ( 100 in this case ). Also defined here are some minimum success criteria, also known as thresholds, such as all responses must have HTTP 200 status code and no response should take longer than 500 milliseconds.

At line 19 the details of URL as well as username & password are specified. You will change these to match your environment.

At line 27 custom metrics for response time and success rate are defined. These will be the key metrics that we’re interested in.

At line 33, inside a for loop which is executed 100 times the request is sent, thresholds checked, metrics recorded and then a wait for 200ms. The 200ms wait is to avoid rate limiting because all requests are coming from the same IP address. This 200ms wait is not included in the response metrics.

When executed ( before the ORDS 25.3.0 upgrade ) the response for test run looks like this.

When gathering the statistics I ran the sequential_100.k6 5 times and recorded the average of the various metrics. This was to smooth out any intermittent spikes in network or database activity.

A test run of the same k6 script after ORDS 25.3.0 was rolled out shows improvement with all metrics.

Conclusion

ORDS 25.3.0, when paired with Oracle 23ai and its latest patch, provides a remarkable improvement in Basic Authentication performance.

Key takeaways:

  • Average authentication response time dropped from ~160ms to ~59ms
  • Max response time and tail latency drastically reduced
  • Real-world API workloads benefit from more consistent and faster performance
  • If Basic Authentication is your only option you will see improvements but you really should be using OAuth2 where you can.

Developers should consider upgrading to ORDS 25.3.0 and applying the latest Oracle 23ai patch to take full advantage of these optimizations.