Forum Stats

  • 3,750,404 Users
  • 2,250,174 Discussions


ORDS performance for GET Handler | Source type - Media Resource


I am generating a JSON as a Media Resource GET handler.

The GET handler use a function to get already created JSON from DB . It is written like below:

SELECT 'application/json',get_json_db   FROM DUAL;

When i run this SQL in SQL Developer it takes 400 ms. However when the API endpoint is called by client or from POSTMAN it takes around 3.7 secs to receive the JSON.

What measures can be taken to improve the ORDS performance as the query is fine and as already JSON is generated so ORDS should not be doing any more JSON processing.? - please suggest.




  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,906 Employee

    how big is the json doc?

  • User_Z8YBQ
    User_Z8YBQ Member Posts: 9 Red Ribbon

    HI Jeff,

    JSON contains a handful of records (< 20) for this particular handler. This is the simplest of the lot. Most of the GET handlers (around 5-6) are built like this , SQL response is < 1 sec with JSON, so ORDS should not have to do any JSON transformation. If we add 4 secs to each API the user would need to wait min 20-25 secs more to get the response.

    I would rule out the SQL/PLSQL/JSON processing here. As even getting the auth token takes 5-6 secs.

    We have enabled OAUTH for these API's with grant type client credentials.

    Running the below curl request itself takes consistently 5 secs to get a response from the server.

    curl -i -k --user <client id>.:<client secret> --data "grant_type=client_credentials" https://<hostname> /ords/bos_api/oauth/token

    ORDS is running on Weblogic 12c , ver 19.2.0.r1991647



  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,906 Employee

    Ooooooold ORDS (19.2 - that's 2+ years old now)

    Please get 21.2 and see the release notes on the new caching tech.

    Here's what happens when you call an API from ORDS

    • we route the connection to the proper pool
    • we figure out what you're trying to run
    • we figure out the privs required do access said thing
    • we look to see if you have those things - that CAN involve a database connection (Slow! Avoid DB Auth for requests if possible)
    • we look up the code to run to make the API happen
    • we run it
    • we do any JSON / linky stuff and send back the response

    In 21.2 - you can cache the db work for the privs query and the code behind the API query. This can SIGNIFICANTLY speed up your APIs, like 5x.

    Anyways, running a query in ORDS isn't just like running a query in SQL*Plus. Especially if paged, we re-write your query so the results can be paged via analytic functions.