This is a general topic about any long-running web service or process, but for argument's sake let's assume that we are calling an external AI (generative text) service using REST from within APEX (a button that calls a PL/SQL procedure that in turn calls a web service using APEX_WEB_SERVICE.MAKE_REST_REQUEST).
The problem is that the external service takes 10-20 seconds (or longer) to respond. If we have several users and they all click a button to start this process, then quickly all the connections in the ORDS connection pool used by APEX will be exhausted waiting for the web service call to finish. In the meantime, other users will experience long waits as there are no available connections to handle other page requests.
What are some good strategies to handle this?
I'm thinking the call to the web service could be fired off in a one-time job (dbms_job / dbms_scheduler) to be handled in the background. The database has its own background processes / sessions that will deal with background jobs, and these will not interfere with the connections in the ORDS connection pool.
In fact, APEX's own “Background Process” ("Execution Chain") feature uses the database job scheduler, see https://docs.oracle.com/en/database/oracle/apex/23.1/htmdb/understanding-background-page-processing.html#GUID-4FCFB2D6-EF19-4692-8980-4F9EBED0DBB7 so if using a recent APEX version this is built-in, but for older APEX versions one could “roll your own” background processing to avoid performance problems with the main connection pool.
Thoughts?