Enable SPATIAL_VECTOR_ACCELERATION in Oracle databases

[Bud]

I've noticed that some organizations forget to enable SPATIAL_VECTOR_ACCELERATION in Oracle databases...now that Oracle Spatial is free.

Oracle Spatial Vector Acceleration: Flick the Switch

So for any of us who use SDO_GEOMETRY, we need to remember to enable that parameter — to make use of it's performance boosting functionality. (It's enabled by default in 21c+.)

I just thought I'd mention that...for those of us who were unaware.

Note:

I've asked the Oracle Spatial team if there's a way to check if SPATIAL_VECTOR_ACCELERATION is enabled (as a non-dba). But I haven't found a solution yet.

[LaurensKusse_hhdelfland]

We changed this setting to TRUE on one of our databases (Oracle 19c) that uses SDO_Geometry. We don't notice any change in performance. 

Does anyone know a test to check the change in performance?

I tried spatial joins and al kinds of "Select by Location''-tasks, but they all take up the same time in a database with SPATIAL_VECTOR_ACCELERATION=True and a database with SPATIAL_VECTOR_ACCELERATION=False.

[Bud]

@LaurensKusse_hhdelfland You could ask on the Oracle forum. You might need to use a SPATIAL tag in your post to get the attention of the Spatial team.

Alternatively, you could ask on GIS Stack Exchange.

[Bud]

By the way, I think the spatial analysis done by Select By Location is performed by ArcObjects in your PC's RAM, not by the database. I had a post about this on GIS stack exchange, but can't find it.

[LaurensKusse_hhdelfland]

Thank you!

I asked Esri support about this. They confirmed that Select By Location is done client-side. 

But I still don't really know if this SPATIAL_VECTOR_ACCELERATION-setting improves anything related to Esri-products.
I just asked Esri support if they know if this might improve the performance of services. Or do you think that this is also handled client-side/service-side and not via the database?

[Bud]

@LaurensKusse_hhdelfland 

Best guess is that only SQL queries that use spatial functions, and where the datatype is SDO_GEOMETRY, would make use of that setting; in other words, only spatial SQL queries that you have written yourself would use that setting. @MarceloMarques might have some insight.

[Bud]

@LaurensKusse_hhdelfland I would be interested in hearing Esri Support's response.

By the way, you might find this interesting: Intended use case for Select Layer by Location — Intersect (DBMS)?

[MarceloMarques]

This is correct, the Oracle SPATIAL_VECTOR_ACCELERATION parameter is only for Oracle Spatial SDO data type.

Only the Oracle SYSDBA will be able to make changes to the parameter.

To grant a non-SYSDBA user permission to view Oracle database parameters in Oracle 19c, you can grant them access to specific dynamic performance views. Here's a step-by-step guide:

Create a view: Create a view based on the dynamic performance views you want the user to access. For example, to grant access to V$PARAMETER:

CREATE VIEW my_parameter_view AS SELECT * FROM v$parameter;

Grant SELECT privilege: Grant the SELECT privilege on the view to the user:

GRANT SELECT ON my_parameter_view TO myuser;

This approach allows the user to query the view without needing SYSDBA privileges.