CODEWITHSUNDEEP
oracle-database
GeorgDangl
GeorgDangl

Reputation: 2192

Oracle RESTful Data Service config - keep extproc alive

We're using an Oracle Database with .Net stored procedures. For this to work, there is an entry to "EXTPROC" in the Oracle TNS configuration that handles the requests for the .Net procedures.

When I try to access such a procedure via SQLDeveloper directly from the database, the extproc process is created (if not already present) and then kept alive, this is working as intended since it's configured that way in the Windows registry.

However, when I route a call via ORDS, Oracle RESTful Data Services, it spawns a new process of the extproc instance (for each request) and after serving the request the extproc process is shut down. This makes it very hard to debug (since I cannot attach to the process via remote debugger) and is also a big performance hit, increasing even simple requests duration from 50ms to 4 seconds.

It worked as expected on an 11g database, now it's running on a 12c.

Is there a known issue in ORDS 2.10 or a configuration I have not yet seen that would solve this?

Upvotes: 0

Views: 294

Answers (1)

cdivilly
cdivilly

Reputation: 360

ORACLE REST Data Services is stateless (as is the HTTP protocol), that means each HTTP request retrieves a database connection from a pool, performs the request and when it's done returns the connection to the pool.

This is good for scalability, many many clients can share a limited pool of connections, each client only holds onto a connection for the duration of the corresponding HTTP request, the connection is reset at the end of each HTTP request. A pool of say 20 connections can serve hundreds and thousands of HTTP requests.

SQL Developer is stateful, when you create a connection in SQL Developer that connection is held by SQL Developer until you close the connection or the database kicks you out. If for the sake of argument your database can only accept 20 connections at a time, then if 20 folks are using SQL Developer and a 21st tries to connect, then they are out of luck.

Note that it is important that ORDS 'resets' each connection once it is finished with it and returns it to the pool. If this was not done then there would be information leakage across requests, one request might see state belonging to a previous state, which may cause bugs or constitute a security breach.

I'm guessing (I've never tried an external stored procedure) that this resetting of session state has the effect of blowing away the EXTPROC state, and causing a process to be created for each ORDS request.

I don't see a way around this apart from re-implementing the procedure in PL/SQL.

Upvotes: 2

Related Questions