Speed up All Spatial Operations with SPATIAL_VECTOR_ACCELERATION parameter

-

100's of times faster Spatial operators and functions


Last week, I tried to diagnose an application problem which mostly consisted of queries related to spatial operators. When I delved into the issue, I discovered that the SPATIAL_VECTOR_ACCELERATION parameter was set to false on my mission-critical databases, despite the spatial component being used effectively.

Although many bloggers had written about it, i was not aware of this parameter. So I wanted to draw everyone's attention to this parameter.

Turn SPATIAL_VECTOR_ACCELERATION parameter on

 
According to the Vector Performance Acceleration in Oracle Spatial and Graph (Doc ID 1506885.1), In Oracle Database versions 12c and above, significant enhancements have been made to improve vector operations through the utilization of new vector performance acceleration capabilities. These enhancements lead to better index performance, increased efficiency of the geometry engine, optimized secondary filter optimizations for spatial operators, and enhanced CPU and memory utilization for numerous advanced vector functions. 

Leveraging vector performance acceleration proves particularly advantageous when utilizing Oracle Exadata Database Machine and similar high-scale systems.

Q.  Does Oracle Spatial and Graph require a license in addition to the Oracle Database license?
A.  As of December 2019, Oracle Spatial and Graph is included with the Oracle database license with no additional cost.  

Starting with 23c SPATIAL_VECTOR_ACCELERATION will default to TRUE.  Use of the parameter set to FALSE is deprecated. Since my databases were upgraded from 11g, parameter was set to FALSE by default.

If you're upgrading a database that effectively uses SPATIAL to 12c or above, ensure to set it. Include this step in the upgrade process.

Parameter can be changed dynamically with the command provided below.


Daniel Geringer, Spatial Solutions Specialist from Oracle Corporation advises that with the usage SPATIAL_VECTOR_ACCELERATION parameter, New faster algorithms will be available for spatial operators and functions (100’s of times faster - Best Practices, Tips and Tricks With Oracle Spatial and Graph)

Also According to the Oracle® Spatial and Graph Developer's Guide, Effective with Release 12.2, spatial indexes can be system-managed by specifying INDEXTYPE=MDSYS.SPATIAL_INDEX_V2 at index creation. You are strongly encouraged to use this index type for all new spatial indexes you create, regardless of whether the spatial table or the spatial index is partitioned, and you may also want to use it if you decide to re-create legacy spatial indexes. 

The main benefit is simplified spatial index management. This is most beneficial in cases of partitioning, because this new index type eliminates the need for most, if not all, index partitioning management operations.

I dropped all my existing spatial indexes and recreated them with the INDEXTYPE=MDSYS.SPATIAL_INDEX_V2 attribute. Additionally, I utilized the cbtree_index=true parameter for some point data to leverage composite binary indexes with the aim of improving DML and query performance. 

Measuring the effects:

I used the following query to measure performance and determine if it executes hundreds of times faster or not. You can employ this query for general purposes to measure before and after performance metrics; just restructure the CTE section according to your criteria.


In my environment, there were almost 500 SQL statements subject to measurement. Most of them were already performing in milliseconds, so I did not observe an improvement worth mentioning; however, 5 to 10 SQL statements performed 50-70 times better.

Hope it helps.

Comments

Post a Comment

Popular posts from this blog

Using table stats hint to force a direct path read but with a rownum predicate surprise

-
Image
Manipulating table statistics for the statement period On my blog post " Who makes the decisions? I thought it was Optimizer ", I used parallel hint to force a direct path read. This time i will try to make use of table_stats hint to force a direct path read. There is not much documentation about the usage of this hint. Roger Macnicol blog post ( Correct syntax for the table_stats hint  ) offers up some useful information about it. These are the variables play role in direct path read decision.  STT stands for small table threshold = _db_block_buffers * 0,02 =  3875820 * 0,02 = 77516 MTT stands for medium table threshold = _small_table_threshold * 5 = 77516 * 5 = 387580 VLOT stands for Very Large Object Threshold = _db_block_buffers * 5  = 3875820 * 5 = 19379100 I will show how the thresholds effect the direct path decision. We will use nsmtio (non smart IO) event  to trace the direct path or non-direct path/buffered decision.   Before 11.2.0.2 release, the direct path read d
Read more

Oracle Grid Release Update by using Ansible Playbooks

-
Image
Ansible is a friend in need and a friend indeed for DBAs. In this blog post, I will mention about Redhat Ansible and show how you can patch your grid infrastructure by using it. Ansible is an open source, command-line IT automation software application written in Python. It can configure systems, deploy software, and orchestrate advanced workflows to support application deployment, system updates, and more. Ansible's main strengths are simplicity and ease of use. It is a little bit similar to dcli commands in Exadata environments, but much more powerful as you may run playbooks which consist of different tasks. Ansible stands out from other configuration management tools because it doesn't need agents installed on target hosts. Instead, it leverages SSH for connecting to them and executes commands directly through the shell. This means Ansible communicates with remote hosts securely using SSH, eliminating the need for additional software on the managed machines. It's like h
Read more

Oracle Database Release Update by Using Ansible Playbooks

-
Image
Ansible to Rule Them All In the previous post " Oracle Grid Software Patching by using Ansible Playbooks" , I mentioned how Ansible orchestration can be used to make Oracle Grid Software Patching more clean and simple way. Now I will also patch database software by using Ansible Playbooks. In this post,  I will use blt01 host as the ansible host. Target environment is a two node RAC database. (gns01, gns02). I will patch grid software to 19.22 by using out-of-place methodology with no down time.  Although, there are lots of useful blog posts about how you may use Ansible for automating tasks, there is not much about a complete Oracle database software patching.  Ansible is simply installed by "yum install ansible"  from EPEL repository. Add your hosts(gns01, gns02) in the hosts file under /etc/ansible/hosts and create ssh equivalency for oracle and root users. We need:     * OPatch -   p6880880_122010_Linux-x86-64.zip     * 19.22 RU -  p35940989_190000_Linux-x86-64.
Read more