The Tip: add the monitoring hint to your REST API Handler code.
ORDS is basically an Oracle client, running queries and scripts for you. It’s just that in this case the ‘client’ is in your mid-tier, and it’s serving up HTTP requests and giving HTTP responses.
And the responses are generated from having your predefined SQL or PL/SQL.
So, if you want a SQL Monitoring report from a REST API you’re working on…one quick trick is to simply add the appropriate HINT.
Once we call the API, we should have a corresponding report show up in PerfHub.
Now, if I were to make a change in my API, and call it again, I’d get another report.
This requires BOTH the tuning and diagnostic packs. You don’t want to be running this in production, except under very specific circumstances. You don’t want your applications calling these APIs while you’re trying to debug them.
Why? Because you can only have so many reports available at any given time, and running these reports adds overhead to the system.
So my advice – create a DUMMY API to simulate the one you’re actually trying to tune/debug. When you’re done with it, drop the API.