The 18.4 version product lines of SQL Developer, SQLcl, Data Modeler, and ORDS we’re pretty much limited to bug fixes.
A significant bug fix for ORDS (all of which are listed in the Release Notes) is this gem:
- BUG:27808357 – Enhance performance of AutoREST tables/views
Large schemas and REST enabled tables were seeing not-very-good response times. For example a GET on a /ords/{schema}/{table}/ to get the first 25 rows was taking more than 23 seconds when there was 1,000+ tables in a schema (the customer’s use case.)
But wait, what is this ‘AutoREST’ feature you speak of?
Now in my demos, this would run in a matter of seconds, but even then it was a bit longer than you’d want when thinking in terms of web response times.
But my schemas were SIMPLE.
So in 18.4, I’ve added 1,000 tables (3 columns each) to my schema, REST enabled my STRAVA table, and ran a request for activities WHERE DISTANCE > 0
My rig is a Mac Mini host with browser and ORDS 18.4 going against a Oracle VBOX 18c db on Oracle Linux.
I paged through several sets of 25 record groups and was seeing anywhere from 1-2 seconds on the response.
Now, there is another performance bug logged around our AUTO feature – and that is with AUTO PL/SQL objects in LARGE schemas, think ‘APPS‘ – work is ongoing to improve this as well, so stay tuned.
Another ORDS 18.4 Fix of Note
- BUG:29049176 – Show 403 Forbidden status when REST Service fails due to database user lacking privilege to access objects referenced in the SQL statement
Note that it’s not that we know that table or object exists and you can’t just access it…it’s just that the SQL results in a ORA-00942: table or view does not exist.
Before this change, ORDS would just give you a HTTP 500…ew ew ew.
11 Comments
Thanks this was an interesting post and maybe relevent to an issue I”m having. I’m using auto ords on a PL/SQL package (Oracle public cloud, autonomous database, apex 19c I’m not sure what version ords is or how to check)
I’m finding that the test query I”m executing – selecting from 1 table with 2 rows – runs in a small fraction of a second but through ords it takes 2.5-3.5 secs. Using on premis weblogic using mod pl/sql takes a fraction of a second not very different to sql. This difference in time doesn’t seem to be due to network issues. I”m running the sql query through the apex interface online and the results appear practically immediately.
Any idea what might be causing this?
Is performance just better if Iearn how to manually set up ords rather then using auto ords? The hr example that install by default does run much much faster.
A package to run a query?
Yes I’m using the package-procedure to open a ref_cursor which ords turns into json.
If the question is why am I not using auto rest on the table. There are a few reasons:
I do want to do a little processing of inputs etc before running the query in the future.
It allows me to simplify the call to the proc (which will be developed against by an external party).
Allows me to simplify and customise the output, removing unnecessary (for us) guff .
I think auto rest on table allows insert/delete/update? (but the other reasons are reason enough anyway, I only want to allow select at this point with non authentication)
I’m trying to come close to replicating how we made old rest APIs using mod pl/sql to generate json. For some reason its orders of magnitude faster on an old slow server running application server then on the oracle cloud through ORDS. (Testing done locally and when being called from 2 other continents.) Something must be wrong somewhere?! As noted the HR example on ORDS runs much much faster. Is the performance due to me using auto rest? Is opening the cursor and handing that to ORDS the issue? (your question makes me wonder if this isn’t commonly used?)
Just wondering why you weren’t using a SQL based handler
The auto should be just fine.. There is some overhead as we have to figure out the payload and outputs first…
I’m on vacation till Monday. I’ll take a look then.
Have you tried building a restful service to test your plsql proc there?
Hi Jeff,
I really appreciate you having a look at this. I did make a very similar proc using ords to call the proc without going the auto route. (I’d hoped to avoid this I guess coming from being used to using the pl/sql gateway it seems a nuisance to have to set up the call and list all the parameters ect when sometimes I have a fair number of parameters and procedures. I guess I’m just lazy it wasn’t *that* hard. Just not as convenient as the pl/sql gateway.
Anyway the test did run massively faster then the auto rest call – similar performance as oracle supplied hr example. So maybe that’s just what I should do in the future.
PS I’m using apex_json to create the json not letting ORDS do it. I doubt thats anything to do with the performance difference but I thought I’d mention it. I’ve also set it up as a GET request which really seems to make more sense and be more convenient for what I’m doing.
Otherwise the procs are identical. I don’t think you could pass the ref cursor to ords the same as with the auto technique…
Thanks for those links.
Thanks Jeff.
The reason for the ask is, adopted the habit of reading one blog post per day and have to visit 14 different blog a day.
and your’s blog is one among them.
Have completed upto this (https://www.thatjeffsmith.com/archive/2013/01/preserving-privilege-grants-whilst-editing-views-in-oracle-sql-developer/) so far.
However if you have provide the link to the next and previous blog post, it would be easy for us to save the link to the next post as on when we complete a specific post.
Ok, they’re back! There should be a left and right arrow on the edge of each page margin
Hi Jeff – thanks. this new plugin/themes to this blog is good.
However it lacks the link to the next and the previous blog post (which we had in the previous plugin/themes on this blog) – any possibility to get that available ?
I’m still playing with the settings,etc. Will take a look, and thanks for the feedback