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.

Looking for general REST API debugging advice?

SELECT /*+monitor */ …

Once we call the API, we should have a corresponding report show up in PerfHub.

One call, one report…

Now, if I were to make a change in my API, and call it again, I’d get another report.

And now that you know how it works…

Caveats

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.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Write A Comment