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:

#!/bin/bash
export clpath=$ORACLE_HOME/jdbc/lib/ojdbc7.jar:$ORACLE_HOME/md/jlib/sdoutl.jar:$ORACLE_HOME/md/jlib/sdoapi.jar
java -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
java -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
java -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
java -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
java -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
java -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:

SQL> set timing on
SQL> alter session set spatial_vector_acceleration=false;

Session altered.

Elapsed: 00:00:00.00
SQL> get q
  1  with v_buildings as
  2  (
  3  select b.name, b.geom
  4  from buildings b, places_pol2 p
  5  where sdo_inside(b.geom,p.geom)='TRUE'
  6  and   p.name='Bemowo'
  7  and   b.name is not null
  8  )
  9  select distinct b.name
 10  from roads r, v_buildings b
 11  where sdo_filter(r.geom,sdo_geom.sdo_buffer(b.geom,100,0.05,'units=m'))='TRUE'
 12* and   r.name='Lazurowa'
SQL> /

NAME
------------------------------------------------
Wydzial Mechatroniki i Lotnictwa
Wydzial Mechatroniczny
Orlen
Akademik Wojskowy nr 1
Iver Bud
Wydzial Mechaniczny
Dom Studencki numer 1
Dom Studencki numer 2
Klub "Yogi"
Biedronka

10 rows selected.

Elapsed: 00:00:05.18
SQL> alter session set spatial_vector_acceleration=true;

Session altered.

Elapsed: 00:00:00.00
SQL> get q
  1  with v_buildings as
  2  (
  3  select b.name, b.geom
  4  from buildings b, places_pol2 p
  5  where sdo_inside(b.geom,p.geom)='TRUE'
  6  and   p.name='Bemowo'
  7  and   b.name is not null
  8  )
  9  select distinct b.name
 10  from roads r, v_buildings b
 11  where sdo_filter(r.geom,sdo_geom.sdo_buffer(b.geom,100,0.05,'units=m'))='TRUE'
 12* and   r.name='Lazurowa'
SQL> /

NAME
------------------------------------------------
Wydzial Mechatroniki i Lotnictwa
Wydzial Mechatroniczny
Orlen
Akademik Wojskowy nr 1
Iver Bud
Wydzial Mechaniczny
Dom Studencki numer 1
Dom Studencki numer 2
Klub "Yogi"
Biedronka

10 rows selected.

Elapsed: 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:

SQL> alter session set spatial_vector_acceleration=false;

Session altered.

SQL> alter session set tracefile_identifier='novector';

Session altered.

SQL> select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/rico/rico/trace/rico_ora_7659_novector.trc

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> get q
  1  with v_buildings as
  2  (
  3  select b.name, b.geom
  4  from buildings b, places_pol2 p
  5  where sdo_inside(b.geom,p.geom)='TRUE'
  6  and   p.name='Bemowo'
  7  and   b.name is not null
  8  )
  9  select distinct b.name
 10  from roads r, v_buildings b
 11  where sdo_filter(r.geom,sdo_geom.sdo_buffer(b.geom,100,0.05,'units=m'))='TRUE'
 12* and   r.name='Lazurowa'
SQL> /

NAME
------------------------------------------------
Wydzial Mechatroniki i Lotnictwa
Wydzial Mechatroniczny
Orlen
Akademik Wojskowy nr 1
Iver Bud
Wydzial Mechaniczny
Dom Studencki numer 1
Dom Studencki numer 2
Klub "Yogi"
Biedronka

10 rows selected.

SQL> alter session set tracefile_identifier='vector';

Session altered.

SQL> select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/rico/rico/trace/rico_ora_7659_vector.trc

SQL> alter session set spatial_vector_acceleration=true;

Session altered.

SQL> @q

NAME
------------------------------------------------
Wydzial Mechatroniki i Lotnictwa
Wydzial Mechatroniczny
Orlen
Akademik Wojskowy nr 1
Iver Bud
Wydzial Mechaniczny
Dom Studencki numer 1
Dom Studencki numer 2
Klub "Yogi"
Biedronka

10 rows selected.

SQL> !cat /u01/app/oracle/diag/rdbms/rico/rico/trace/rico_ora_7659_novector.trc | wc -l
  126864

SQL> !cat /u01/app/oracle/diag/rdbms/rico/rico/trace/rico_ora_7659_vector.trc | wc -l
  102263

SQL> 

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).

SQL> !cat /u01/app/oracle/diag/rdbms/rico/rico/trace/rico_ora_7659_novector.trc | grep -c "MDRT"
8

SQL> !cat /u01/app/oracle/diag/rdbms/rico/rico/trace/rico_ora_7659_vector.trc | grep -c "MDRT"
0

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

SQL> !cat /u01/app/oracle/diag/rdbms/rico/rico/trace/rico_ora_7659_novector.trc | grep -c "PERSISTENT LOB"
1724

SQL> !cat /u01/app/oracle/diag/rdbms/rico/rico/trace/rico_ora_7659_vector.trc | grep -c "PERSISTENT LOB"
0

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:

oracle@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}'
,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
oracle@ryba:~$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun May 3 20:22:01 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> ed
Wrote file afiedt.buf

  1  select object_name, object_type
  2  from dba_objects
  3  where owner='HR'
  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)
SQL> /

OBJECT_NAME		       OBJECT_TYPE
------------------------------ -----------------------
SYS_LOB0000093516C00009$$      LOB
PLACES_POL2		       TABLE
MDRT_16CA2$		       TABLE
MDRT_16C73$		       TABLE
BUILDINGS		       TABLE
ROADS			       TABLE

6 rows selected.

SQL> get bh
  1  select object_name, count(distinct file# || block#) as cnt
  2  from v$bh b, dba_objects o
  3  where b.objd=o.data_object_id
  4  and   o.owner='HR'
  5* group by object_name
SQL> /

OBJECT_NAME			      CNT
------------------------------ ----------
SYS_LOB0000093516C00009$$		2
ROADS				      928
MDRT_16C73$			      109
BUILDINGS			      684
MDRT_16CA2$			      180
PLACES_POL2			       54

And for SPATIAL_VECTOR_ACCELERATION=TRUE:

oracle@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}'
,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
oracle@ryba:~$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun May 3 20:25:10 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> ed
Wrote file afiedt.buf

  1  select object_name, object_type
  2  from dba_objects
  3  where owner='HR'
  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)
SQL> /

OBJECT_NAME		       OBJECT_TYPE
------------------------------ -----------------------
SYS_LOB0000093516C00009$$      LOB
PLACES_POL2		       TABLE
MDRT_16CA2$		       TABLE
MDRT_16C73$		       TABLE
BUILDINGS		       TABLE
ROADS			       TABLE

6 rows selected.

SQL> get bh
  1  select object_name, count(distinct file# || block#) as cnt
  2  from v$bh b, dba_objects o
  3  where b.objd=o.data_object_id
  4  and   o.owner='HR'
  5* group by object_name
SQL> /

OBJECT_NAME			      CNT
------------------------------ ----------
SYS_LOB0000093516C00009$$		2
ROADS				      928
MDRT_16C73$			      109
BUILDINGS			      684
MDRT_16CA2$			      180
PLACES_POL2			       54

6 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:

SELECT used FROM sdo_feature_usage WHERE feature_name = :name FOR UPDATE

Search:


Kontakt

Database Whisperers sp. z o. o. sp. k.
Al. Jerozolimskie 200
Wejście B, III piętro/ pokój 342
02-486 Warszawa
NIP: 5272744987
REGON:362524978
+48 508 943 051
+48 661 966 009
info@ora-600.pl

Newsletter zapisz się żeby być na bieżąco