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