ORDS REST handlers caching

Pavol Babel

Hi,

We are using ORDS 18.2 with Oracle Database 12.1.0.2, PL/SQL blocks exclusively. I have recently noticed ORDS performs quite many roundtrips during every single REST request

1) The first query selects from user_ords_services and is searching for published services for BASE_PATH

2) the second one queries definition from user_ords_modules/handlers/templates

3) the third query checks users_ords_privileges, user_ords_privilege_mappings

Accoring to tcpdump, this adds at least 20-30ms latency because of roundtrips between Application server and DB server. So I'm thinking of cache.caching. I'm not sure if it is was designed to cache REST handlers, but I hope so. Unfortunately my settings does not seem to help

According to log, apex has accepted folliowing parameters:

cache.caching=true

cache.directory=/tmp/apex/cache

cache.duration=hours

cache.expiration=2

cache.monitorInterval=10

cache.procedureNameList=*,*

Our ORDS is deployed on Weblogic 12.2

BR
Pavol Babel

OCM DBA 10g/11g

thatJeffSmith-Oracle

It's for caching files related to apex and i think mod plsql calls

If we cached those security checks,I think bad things could happen.

Pavol Babel

Hi,

Agree with you maybe caching ords roles is not the best idea. However downloading definition od PL/SQL REST every time, querying the definition of  a procedure from LOB column is quite an expensive way (maybe ist is very safe but also ho negative impact). When I want to change definition of REST I can still peform a warm restart of all weblogic nodes

BR

Pavol Babel

Yevon

And all this 3 verifications couldn't be done in just one step? 3 roundtrips between database and ords for every request looks too much latency.

thatJeffSmith-Oracle

right, which is why you should look at version 21.2 or ORDS will now allows you cache the calls where we do the request and privilege look-ups on your requests

In addition to what you can find in the docs, see what @Kris Rice-Oracle has to say on the subject and the results he was able to achieve

https://krisrice.io/2021-07-13-ords-cache/

Yevon

Wow that is amazing, I just tried it and is noticeable thanks!