Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Improve query performance using the Query Result Cache hint

User_1871Jul 8 2022 — edited Jul 8 2022

The Query Result Cache hint seems like a useful mechanism: Oracle Base: Query Result Cache.
But it's a shame it isn't supported for object types like SDO_GEOMETRY.
13.56 RESULT_CACHE Clause
Can't use Query Result Cache in query with varray (or object types)
With that said, the mechanism could still be useful for:
Queries that are non-spatial.
Queries that use SDO_GEOMETRY functionality, but don't output geometry columns.
For example, output spatial information as X and Y number columns, instead of an SDO_GEOMETRY column. Then use the GIS application to display the XYs as points — via something like an XY Event Layer in ArcGIS. (XY Event Layers are surprisingly fast.)
Unfortunately, that only helps us if the spatial information is points. We can't create an event layer for lines, polygons, or multi-part points.
More info:
Making Oracle queries faster: Cache query results using an SQL hint
What's the intended use case for the Query Result Cache hint?

Does anyone have any experience with the Query Result Cache hint? Can you think of any other workarounds for using it with spatial data?
This post has been answered by Rick Anderson-Oracle on Sep 27 2022
Jump to Answer

Comments

User_1871

It would be great if people could upvote this Oracle Groundbreakers idea:
Query Result Cache - Support collection and object datatypes

Rick Anderson-Oracle
Answer

Sorry for my late response - it's been a very busy summer...
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;

MARKET.SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINAT
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR
AY(1, 1, 5, 7))
1 row selected.
This functionality has been available since the 19c release.
FYI
Rick

Marked as Answer by User_1871 · Apr 3 2023
2 people found this helpful
Rick Anderson-Oracle

Note: There is a 32k limit on the size of the spatial geometry, but caching larger geometry objects probably does not make sense anyway...
Rick

_jum

This hint sounds very exiting.
Tried it out immediately with ORACLE 19.16.0, but with low success.
Changed the number of rows in the test table and tried the script multiple times, with different results.
Sometimes the "cached" statements where a little faster, sometimes the "normal" ones. Maybe the number of points exceeds the memory limit, but this is the amount we would need in practice.
Is there an error in my skript, can you confirm the results?

SET ECHO OFF
SET TIMI OFF
SET FEED OFF
SET HEAD OFF
SET NEWPAGE 0

DROP TABLE pnts;

EXEC dbms_random.seed (42);

CREATE TABLE pnts AS
 SELECT rownum id, sdo_geometry(3001, NULL, NULL, sdo_elem_info_array(1,1,1,4,1,0)
        , sdo_ordinate_array(round(dbms_random.value(1,1000000),3),round(dbms_random.value(1,1000000),3),round(dbms_random.value(1,1000),1))) pnt
   FROM dual
CONNECT BY level<20000;

ALTER SYSTEM FLUSH SHARED_POOL;

ALTER SYSTEM FLUSH BUFFER_CACHE;

SET TIMI ON

PROMPT .
PROMPT INIT
SELECT max(gt.x) maxx, min(gt.x) minx
     , max(gt.y) maxy, min(gt.y) miny   
  FROM pnts,
       sdo_util.getvertices(pnt) gt;

PROMPT .
PROMPT RESULT_CACHE
SELECT /*+ result_cache(force_spatial) */
       max(gt.x) maxx, min(gt.x) minx  
     , max(gt.y) maxy, min(gt.y) miny   
  FROM pnts,
       sdo_util.getvertices(pnt) gt;

PROMPT .
PROMPT NO RESULT_CACHE
SELECT --/*+ result_cache(force_spatial) */
       max(gt.x) maxx, min(gt.x) minx  
     , max(gt.y) maxy, min(gt.y) miny   
  FROM pnts,
       sdo_util.getvertices(pnt) gt;

PROMPT .
PROMPT RESULT_CACHE
SELECT /*+ result_cache(force_spatial) */
       max(gt.x) maxx--, min(gt.x) minx  
     , max(gt.y) maxy--, min(gt.y) miny   
  FROM pnts,
       sdo_util.getvertices(pnt) gt;

PROMPT .
PROMPT RESULT_CACHE
SELECT /*+ result_cache(force_spatial) */
       --max(gt.x) maxx
       min(gt.x) minx  
       --, max(gt.y) maxy
     , min(gt.y) miny   
  FROM pnts,
       sdo_util.getvertices(pnt) gt;       

PROMPT .
PROMPT NO RESULT_CACHE
SELECT --/*+ result_cache(force_spatial) */
       --max(gt.x) maxx
       min(gt.x) minx  
       --, max(gt.y) maxy
     , min(gt.y) miny   
  FROM pnts,
       sdo_util.getvertices(pnt) gt;                     
       
EXIT; 
  

Results:

.
INIT
♀999935.437    40.287 999992.316    40.466
Elapsed: 00:00:01.20
.
RESULT_CACHE
♀999935.437    40.287 999992.316    40.466
Elapsed: 00:00:01.03
.
NO RESULT_CACHE
 999935.437    40.287 999992.316    40.466
Elapsed: 00:00:01.01
.
RESULT_CACHE
 999935.437 999992.316
Elapsed: 00:00:01.03
.
RESULT_CACHE
   40.287    40.466
Elapsed: 00:00:01.02
.
NO RESULT_CACHE
   40.287    40.466
Elapsed: 00:00:01.02
Rick Anderson-Oracle

Checking...

Rick Anderson-Oracle

My first thought was: Does the result cache benefit a repeat of the same query (I'm using 100,00 rows to check cache effectiveness)?
SQL> SELECT /*+ result_cache(force_spatial) */
2 min(gt.x) minx, min(gt.y) miny
3 FROM pnts, sdo_util.getvertices(pnt) gt;
MINX MINY
---------- ----------
20.717 29.269
1 row selected.
Elapsed: 00:00:11.20
SQL>
SQL> SELECT /*+ result_cache(force_spatial) */
2 min(gt.x) minx, min(gt.y) miny
3 FROM pnts, sdo_util.getvertices(pnt) gt;
MINX MINY
---------- ----------
20.717 29.269
1 row selected.
Elapsed: 00:00:00.00
On the surface, the cache does seem to be effective. Like any cache, any performance benefit is goodness and your mileage may vary depending on the environment.
Rick

_jum

Hi [Rick Anderson-Oracle](/ords/forums/user/Rick Anderson-Oracle) ,
thanks for the validation!
Your difference for 100.00 rows between 11secs and 0 secs is impressive.
I get the same results in both cases, but need nearly the same time of 5secs too.
/*+ result_cache(force_spatial) */ is even a little slower, this needs further investigation...

RESULT_CACHE
   20.717    29.269
Elapsed: 00:00:05.22

NO RESULT_CACHE
   20.717    29.269
Elapsed: 00:00:05.12
1 - 7

Post Details

Added on Jul 8 2022
#database-key-features, #spatial
7 comments
652 views
1 mention