I came across a mechanism that can help with slow Oracle queries in some scenarios. I thought I'd share.
If we have a slow Oracle query, we can add the /*+ result_cache */ hint to the SQL. That hint tells Oracle to cache the result of the query until the next time the data is edited.
Query Result Cache in Oracle Database
I think that's a pretty powerful concept. Why re-compute a query every time we use it? Why not save the results until the underlying data changes?
--get st_geometry line startpoint
select /*+ result_cache */
objectid,
sde.st_x(sde.st_startpoint(shape)) as startpoint_x,
sde.st_y(sde.st_startpoint(shape)) as startpoint_y
from
sidewalks --initial load takes 20 seconds due to slow st_geometry functions (14,000 features)
I was quite happy when I figured that out!
With that said, there are significant limitations:
Summary:
As with any performance improvement mechanism, there are lots of tradeoffs...it's useful in some cases, but not others.
As mentioned, there are lots of alternative techniques we can use that have their own pros/cons. Maybe the benefit to this one is that it's dead-easy to implement (just add a line to your query), doesn't require begging the DBA team for stuff, and doesn't require custom code/additional FCs to manage.
I really like the idea of caching, rather than constantly re-computing for no reason.
I submitted a similar idea, but for caching in the application in ArcGIS: Event layers/query layers: Control caching settings for better performance.
Cheers.
https://community.oracle.com/tech/apps-infra/discussion/comment/16848629/#Comment_16848629
"Good news! Spatial geometry ADTs are supported with the result cache mechanism, but you need to use the special optimizer hint: /*+ result_cache(force_spatial) */
For example:SQL> SELECT /*+ result_cache(force_spatial) parallel(2) */ c.market.shape
2 FROM cola_markets_2 c
3 WHERE c.market.name = 'cola_a'
4 ORDER by c.mkt_id;"
Hello Bud,
Just to let you know, Result Cache is at the time of writing an Oracle Enterprise Edition feature.
It is a really handy "silver bullet", but comes with a licence caveat, and a memory caveat (depending on what you're doing).
https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/Licensing-Information.html
As a general guide, don't use hints in your SQL without chatting to your DBA first, hints are the exception, rather than the norm.
For sites or installations that aren't enterprise edition, you may be able to use subqueries, to get a similar effect in some cases.
I also noticed that you put a parallel(2) hint in one of your examples, again, don't do that unless you talk to your DBA first. Hits are the exeption, rather than the norm, and forced parallelism, also has some drawbacks relating to overhead of stitching the results of the parallel threads back together, using hash tables etc.
The hints that you're adding to your SQL are over-riding the optimiser: and 9 times out of 10, the Oracle Optimiser will Optimise, better than the hint, so again, treat them as the exception, rather than the norm.
Chat with your DBA, they'll steer you right.
Cheers - Ron.