12c: Spatial Vector Acceleration

03.05.2015
by Kamil Stawiarski

There is a new feature in Oracle Spatial 12c, called "Spatial Vector Acceleration".

According to Oracle:

One of the most notable improvements with Oracle Spatial and Graph 12c can be seen in performance increases in vector data operations. Enabling the Spatial Vector Acceleration feature (available with the Spatial option) dramatically improves the performance of commonly used vector data operations, such as sdo_distance, sdo_aggr_union, and sdo_inside. With 12c, these operations also run more efficiently in parallel than in prior versions through the use of metadata caching.

The above sentence is quoted in a lot of blogs and in documentation, but I couldn’t find an explanation on how this feature really work and how it can improve the actual performance. Thats why I decided to check it for myself.

I’ve prepared example schema, based on two sources:

  • http://www.codgik.gov.pl/index.php/darmowe-dane/prg.html
  • http://download.geofabrik.de/

I have imported the data, using appropriate Java Classes provided by Oracle in binaries:

view source
print?
1#!/bin/bash
2export clpath=$ORACLE_HOME/jdbc/lib/ojdbc7.jar:$ORACLE_HOME/md/jlib/sdoutl.jar:$ORACLE_HOME/md/jlib/sdoapi.jar
3java -cp $clpath oracle.spatial.util.SampleShapefileToJGeomFeature -h localhost -p 1521 -s rico -u hr -d hr -t roads -f $PWD/roads -r 8307 -g geom -x -180,180 -y -90,90
4java -cp $clpath oracle.spatial.util.SampleShapefileToJGeomFeature -h localhost -p 1521 -s rico -u hr -d hr -t buildings -f $PWD/buildings -r 8307 -g geom -x -180,180 -y -90,90
5java -cp $clpath oracle.spatial.util.SampleShapefileToJGeomFeature -h localhost -p 1521 -s rico -u hr -d hr -t places -f $PWD/places -r 8307 -g geom -x -180,180 -y -90,90
6java -cp $clpath oracle.spatial.util.SampleShapefileToJGeomFeature -h localhost -p 1521 -s rico -u hr -d hr -t waterways -f $PWD/waterways -r 8307 -g geom -x -180,180 -y -90,90
7java -cp $clpath oracle.spatial.util.SampleShapefileToJGeomFeature -h localhost -p 1521 -s rico -u hr -d hr -t railways -f $PWD/railways -r 8307 -g geom -x -180,180 -y -90,90
8java -cp $clpath oracle.spatial.util.SampleShapefileToJGeomFeature -h localhost -p 1521 -s rico -u hr -d hr -t jednostki_ewidencyjne -f $PWD/jednostki_ewidencyjne -r 2180 -g geom

After creating the schema, I’ve executed a simple SQL query:

view source
print?
1SQL> set timing on
2SQL> alter session set spatial_vector_acceleration=false;
3 
4Session altered.
5 
6Elapsed: 00:00:00.00
7SQL> get q
8  1  with v_buildings as
9  2  (
10  3  select b.name, b.geom
11  4  from buildings b, places_pol2 p
12  5  where sdo_inside(b.geom,p.geom)='TRUE'
13  6  and   p.name='Bemowo'
14  7  and   b.name is not null
15  8  )
16  9  select distinct b.name
17 10  from roads r, v_buildings b
18 11  where sdo_filter(r.geom,sdo_geom.sdo_buffer(b.geom,100,0.05,'units=m'))='TRUE'
19 12* and   r.name='Lazurowa'
20SQL> /
21 
22NAME
23------------------------------------------------
24Wydzial Mechatroniki i Lotnictwa
25Wydzial Mechatroniczny
26Orlen
27Akademik Wojskowy nr 1
28Iver Bud
29Wydzial Mechaniczny
30Dom Studencki numer 1
31Dom Studencki numer 2
32Klub "Yogi"
33Biedronka
34 
3510 rows selected.
36 
37Elapsed: 00:00:05.18
38SQL> alter session set spatial_vector_acceleration=true;
39 
40Session altered.
41 
42Elapsed: 00:00:00.00
43SQL> get q
44  1  with v_buildings as
45  2  (
46  3  select b.name, b.geom
47  4  from buildings b, places_pol2 p
48  5  where sdo_inside(b.geom,p.geom)='TRUE'
49  6  and   p.name='Bemowo'
50  7  and   b.name is not null
51  8  )
52  9  select distinct b.name
53 10  from roads r, v_buildings b
54 11  where sdo_filter(r.geom,sdo_geom.sdo_buffer(b.geom,100,0.05,'units=m'))='TRUE'
55 12* and   r.name='Lazurowa'
56SQL> /
57 
58NAME
59------------------------------------------------
60Wydzial Mechatroniki i Lotnictwa
61Wydzial Mechatroniczny
62Orlen
63Akademik Wojskowy nr 1
64Iver Bud
65Wydzial Mechaniczny
66Dom Studencki numer 1
67Dom Studencki numer 2
68Klub "Yogi"
69Biedronka
70 
7110 rows selected.
72 
73Elapsed: 00:00:03.14

OK, so the execution time is not much better in vector acceleration, but again: the schema is not so big and query is quite simple. So how to check what is the true performance benefit when using vector acceleration?

Let’s try to trace SQL with 10046 event:

view source
print?
1SQL> alter session set spatial_vector_acceleration=false;
2 
3Session altered.
4 
5SQL> alter session set tracefile_identifier='novector';
6 
7Session altered.
8 
9SQL> select value from v$diag_info where name='Default Trace File';
10 
11VALUE
12--------------------------------------------------------------------------------
13/u01/app/oracle/diag/rdbms/rico/rico/trace/rico_ora_7659_novector.trc
14 
15SQL> alter session set events '10046 trace name context forever, level 12';
16 
17Session altered.
18 
19SQL> get q
20  1  with v_buildings as
21  2  (
22  3  select b.name, b.geom
23  4  from buildings b, places_pol2 p
24  5  where sdo_inside(b.geom,p.geom)='TRUE'
25  6  and   p.name='Bemowo'
26  7  and   b.name is not null
27  8  )
28  9  select distinct b.name
29 10  from roads r, v_buildings b
30 11  where sdo_filter(r.geom,sdo_geom.sdo_buffer(b.geom,100,0.05,'units=m'))='TRUE'
31 12* and   r.name='Lazurowa'
32SQL> /
33 
34NAME
35------------------------------------------------
36Wydzial Mechatroniki i Lotnictwa
37Wydzial Mechatroniczny
38Orlen
39Akademik Wojskowy nr 1
40Iver Bud
41Wydzial Mechaniczny
42Dom Studencki numer 1
43Dom Studencki numer 2
44Klub "Yogi"
45Biedronka
46 
4710 rows selected.
48 
49SQL> alter session set tracefile_identifier='vector';
50 
51Session altered.
52 
53SQL> select value from v$diag_info where name='Default Trace File';
54 
55VALUE
56--------------------------------------------------------------------------------
57/u01/app/oracle/diag/rdbms/rico/rico/trace/rico_ora_7659_vector.trc
58 
59SQL> alter session set spatial_vector_acceleration=true;
60 
61Session altered.
62 
63SQL> @q
64 
65NAME
66------------------------------------------------
67Wydzial Mechatroniki i Lotnictwa
68Wydzial Mechatroniczny
69Orlen
70Akademik Wojskowy nr 1
71Iver Bud
72Wydzial Mechaniczny
73Dom Studencki numer 1
74Dom Studencki numer 2
75Klub "Yogi"
76Biedronka
77 
7810 rows selected.
79 
80SQL> !cat /u01/app/oracle/diag/rdbms/rico/rico/trace/rico_ora_7659_novector.trc | wc -l
81  126864
82 
83SQL> !cat /u01/app/oracle/diag/rdbms/rico/rico/trace/rico_ora_7659_vector.trc | wc -l
84  102263
85 
86SQL>

WOW. So at first sight we can see that trace file from execution without vector acceleration is much bigger. Subsequent tests, confirmed that this is a deterministic behavior. After a little reading, it has occurred that in trace with spatial_vector_acceleration=true, there is no SELECT statement from spatial index tables (MDRT).

view source
print?
1SQL> !cat /u01/app/oracle/diag/rdbms/rico/rico/trace/rico_ora_7659_novector.trc | grep -c "MDRT"
28
3 
4SQL> !cat /u01/app/oracle/diag/rdbms/rico/rico/trace/rico_ora_7659_vector.trc | grep -c "MDRT"
50

Also, you won’t find the LOB reads in the second trace file.

view source
print?
1SQL> !cat /u01/app/oracle/diag/rdbms/rico/rico/trace/rico_ora_7659_novector.trc | grep -c "PERSISTENT LOB"
21724
3 
4SQL> !cat /u01/app/oracle/diag/rdbms/rico/rico/trace/rico_ora_7659_vector.trc | grep -c "PERSISTENT LOB"
50

So it seems that after turning the vector acceleration on, Oracle uses different mechanisms to access index metadata and LOB data from spatial objects.

I run a few more tests to check if both methods are reading the same set of objects at the low level. To achieve this I executed "STARTUP FORCE" command, before execution of each traced spatial query.

And here are the results for SPATIAL_VECTOR_ACCELERATION=FALSE:

view source
print?
1oracle@ryba:~$ cat /u01/app/oracle/diag/rdbms/rico/rico/trace/rico_ora_8134_novector.trc | grep WAIT | awk '{print $(NF-1)}' | sort -u | awk -F= '{x=x","$2} END {print x}'
2,103,118,124,131,132,14,144,147,18,20366,20397,20398,22,298,299,3,30,300,301,302,303,304,305,308,309,31,311,312,313,314,315,316,323,325,326,327,328,36,37,38,4,42,46,54,55,57,607,609,61,611,62,625,626,629,63,630,634,644,655,658,659,66,662,663,666,667,670,671,674,68,682,686,690,691,694,695,696,697,70,72951,72960,83,83391,83392,83413,83690,83691,83699,83749,83750,83754,84,85,86161,86177,86182,86339,86340,87,89,91813,93274,93279,93300,93347,93516,93519,94
3oracle@ryba:~$ sqlplus / as sysdba
4 
5SQL*Plus: Release 12.1.0.2.0 Production on Sun May 3 20:22:01 2015
6 
7Copyright (c) 1982, 2014, Oracle.  All rights reserved.
8 
9 
10Connected to:
11Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
12With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
13 
14SQL> ed
15Wrote file afiedt.buf
16 
17  1  select object_name, object_type
18  2  from dba_objects
19  3  where owner='HR'
20  4* and data_object_id in (103,118,124,131,132,14,144,147,18,20366,20397,20398,22,298,299,3,30,300,301,302,303,304,305,308,309,31,311,312,313,314,315,316,323,325,326,327,328,36,37,38,4,42,46,54,55,57,607,609,61,611,62,625,626,629,63,630,634,644,655,658,659,66,662,663,666,667,670,671,674,68,682,686,690,691,694,695,696,697,70,72951,72960,83,83391,83392,83413,83690,83691,83699,83749,83750,83754,84,85,86161,86177,86182,86339,86340,87,89,91813,93274,93279,93300,93347,93516,93519,94)
21SQL> /
22 
23OBJECT_NAME            OBJECT_TYPE
24------------------------------ -----------------------
25SYS_LOB0000093516C00009$$      LOB
26PLACES_POL2            TABLE
27MDRT_16CA2$            TABLE
28MDRT_16C73$            TABLE
29BUILDINGS              TABLE
30ROADS                  TABLE
31 
326 rows selected.
33 
34SQL> get bh
35  1  select object_name, count(distinct file# || block#) as cnt
36  2  from v$bh b, dba_objects o
37  3  where b.objd=o.data_object_id
38  4  and   o.owner='HR'
39  5* group by object_name
40SQL> /
41 
42OBJECT_NAME               CNT
43------------------------------ ----------
44SYS_LOB0000093516C00009$$       2
45ROADS                     928
46MDRT_16C73$               109
47BUILDINGS                 684
48MDRT_16CA2$               180
49PLACES_POL2                54

And for SPATIAL_VECTOR_ACCELERATION=TRUE:

view source
print?
1oracle@ryba:~$ cat /u01/app/oracle/diag/rdbms/rico/rico/trace/rico_ora_8449_vector.trc | grep WAIT | awk '{print $(NF-1)}' | sort -u | awk -F= '{x=x","$2} END {print x}'
2,101,103,118,124,131,132,14,18,20366,20397,20398,22,298,299,3,30,300,301,302,303,304,305,308,309,31,311,312,313,314,315,316,323,325,326,327,328,36,37,38,4,42,450,55,57,607,609,61,611,62,625,626,629,63,630,632,634,644,655,658,659,66,662,663,666,667,670,671,674,68,682,683,686,687,688,689,690,691,694,695,696,697,70,72951,72960,83,83391,83392,83413,83690,83691,83699,83749,83750,83754,84,85,86161,86177,86182,86339,86340,87,89,91813,91821,93274,93279,93300,93347,93516,93519,94,95
3oracle@ryba:~$ sqlplus / as sysdba
4 
5SQL*Plus: Release 12.1.0.2.0 Production on Sun May 3 20:25:10 2015
6 
7Copyright (c) 1982, 2014, Oracle.  All rights reserved.
8 
9 
10Connected to:
11Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
12With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
13 
14SQL> ed
15Wrote file afiedt.buf
16 
17  1  select object_name, object_type
18  2  from dba_objects
19  3  where owner='HR'
20  4* and data_object_id in (101,103,118,124,131,132,14,18,20366,20397,20398,22,298,299,3,30,300,301,302,303,304,305,308,309,31,311,312,313,314,315,316,323,325,326,327,328,36,37,38,4,42,450,55,57,607,609,61,611,62,625,626,629,63,630,632,634,644,655,658,659,66,662,663,666,667,670,671,674,68,682,683,686,687,688,689,690,691,694,695,696,697,70,72951,72960,83,83391,83392,83413,83690,83691,83699,83749,83750,83754,84,85,86161,86177,86182,86339,86340,87,89,91813,91821,93274,93279,93300,93347,93516,93519,94,95)
21SQL> /
22 
23OBJECT_NAME            OBJECT_TYPE
24------------------------------ -----------------------
25SYS_LOB0000093516C00009$$      LOB
26PLACES_POL2            TABLE
27MDRT_16CA2$            TABLE
28MDRT_16C73$            TABLE
29BUILDINGS              TABLE
30ROADS                  TABLE
31 
326 rows selected.
33 
34SQL> get bh
35  1  select object_name, count(distinct file# || block#) as cnt
36  2  from v$bh b, dba_objects o
37  3  where b.objd=o.data_object_id
38  4  and   o.owner='HR'
39  5* group by object_name
40SQL> /
41 
42OBJECT_NAME               CNT
43------------------------------ ----------
44SYS_LOB0000093516C00009$$       2
45ROADS                     928
46MDRT_16C73$               109
47BUILDINGS                 684
48MDRT_16CA2$               180
49PLACES_POL2                54
50 
516 rows selected.

So Spatial Vector Acceleration is reading the same data but – apparently – using different mechanisms, which can be much faster in production environments. I can’t wait to test this functionality in real-life environment.

But remember – this option can be used ONLY with Oracle Spatial license. Oracle is recording each usage of this feature:

view source
print?
1SELECT used FROM sdo_feature_usage WHERE feature_name = :name FOR UPDATE

Search:

Category:
choose
All categories 12c Exadata GO internals konferencje Oracle RAC RMAN RUST security tips&tricks tuning

Popular posts

14.06.2016
ORACLE RAC 12c: restore OCR and VOTEDISK

Let’s fuckup the cluster! Of course after this operation, the final state of the processes can look like this: The cssd service will not be able to start, because there are no voting disks: OK, so let’s try to stop the cluster services: Now we will have to start CRS in exclusive mode and start […]

Read more
10.02.2016
How to list files from directory using external table in 12c

I’ve wrote about privilege escalation with external tables in this post: https://blog.ora-600.pl/2014/12/23/simple-technics-of-privilege-escalation-part2-dbasysdba/ This time we will try to list all files within directory object, to which we have no EXECUTE privs. This is possible due to great new feature of database 12c that allows to use metacharacters to match multiple files in external table. I […]

Read more
28.05.2017
How to recover a dropped package from archivelogs – based on research for VOODOO engine

Sorry for long time without post, but we are writing with Marcin Rydz a new heterogenous replication product based on archivelogs and it’s consuming a looooooot of our time 🙂 Yes I know it’s nothing new on the market. There are other cool products and I know they’ll be probably be better. But we want […]

Read more
01.02.2017
back to the basics: ALTER TABLE MOVE vs SHRINK

It’s time for the next article with ODBV visualisation 🙂 This time let’s examine the difference between move and shrink – this is very common question on a lot of trainings. You can find a lot of great articles in the Internet regarding this subject but I think that visualisation really helps to understand what […]

Read more
03.05.2015
12c: Spatial Vector Acceleration

There is a new feature in Oracle Spatial 12c, called "Spatial Vector Acceleration". According to Oracle: One of the most notable improvements with Oracle Spatial and Graph 12c can be seen in performance increases in vector data operations. Enabling the Spatial Vector Acceleration feature (available with the Spatial option) dramatically improves the performance of commonly […]

Read more
30.12.2016
back to the basics: truncate table reuse storage vs drop storage

From time to time I get questions on my trainings, what is the difference between TRUNCATE TABLE and TRUNCATE TABLE DROP STORAGE… well, there is no difference because DROP STORAGE is default 😉 DROP STORAGE Specify DROP STORAGE to deallocate all space from the deleted rows from the table except the space allocated by the […]

Read more
28.10.2017
Visualize your STATSPACK reports with Python Plotly

If you work as a consultant, getting remote access to your customer is sometimes a nightmare! Even when you’ll get one, there a times when you just want to get your job done and finish this nightmare. For example, there are times when it is easier to dump a statspack repo and do offline analyzes. […]

Read more
08.02.2016
Sparc M7 vs Intel and P8

I had a pleasure to work with my colleagues from Oracle – Radosław Kut and Krzysztof Marciniak – at comparing the performance of In-Memory queries between Sparc M7, Intel® Xeon® X5670 and Intel® Xeon® E5-2699. You can find results of our findings in this presentation: https://www.oracle.com/webfolder/s/delivery_production/docs/FY16h1/doc17/DB12c-on-SPARC-M7.pdf Following their methodology I’d like to present appropriate results […]

Read more
29.10.2015
Oracle 12c: PRAGMA UDF – the truth

Let’s check, why PRAGMA UDF makes execution faster, then regular function. C function responsible for calling a PL/SQL code from SQL is called "plsql_run": Now let’s try to create a new HR session and create a simple function which will multiple values by 2: From other terminal I will connect to the HR session with […]

Read more
12.03.2017
ODBV improvements

Thanks to suggestions made by Frits Hoogland, I made some improvements to the ODBV. The new version can be found here: http://ora-600.pl/oinstall/odbv.x86_64 The changes are: Recognition of first, second and third level bitmap block Recognition of pagetable segment header Block number ranges on the left side The blocks will be coloured properly to belonging segment. […]

Read more
03.11.2016
Oracle Database Block Visualizer

Recently I wanted to demonstrate to some people on my training, how Oracle database maintains blocks in a datafile – what happens after truncate, truncate with drop storage clause, delete, regular insert, direct path insert and so on… I didn’t find any tool for that so I’ve written my own. It’s core code is based […]

Read more
10.02.2024
[SOLVED!] ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ

This is a pain in the ass. You try to import something from a newer database and you hit the error that your timestamp is not good enough! Even tho you have no timestamp datatypes in the dump… The whole case was covered by Mike Dietrich: Let me show you my – a little bit […]

Read more

Contact us

Database Whisperers sp. z o. o. sp. k.
al. Jerozolimskie 200, 3rd floor, room 342
02-486 Warszawa
NIP: 5272744987
REGON:362524978
+48 508 943 051
+48 661 966 009
info@ora-600.pl

Newsletter Sign up to be updated