Hello everybody,
I am trying to cope with a migration from Oracle 11gR2 to 19.10 of some schemas which use *a lot* of spatial data and procedures. It is about tracking the fleet of the local public transport company: I receive something like 1.5 million bus positions every day, and I check them against the scheduled routes, timetables, etc.
Everything works OK, the current database (11R2) does not show any problem in elaborating the data.
When I launch the same procedures on the same data migrated on the 19g instance, the elapsed time of processing them is much (much!) higher, it almost doubles itself.
After some research, manual reading and forum help, I made the following operations on the migrated data/tables/indexes
Checked all the imported geometries with SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT, and used SDO_UTIL.RECTIFY_GEOMETRY to fix the invalid ones.
Check the metadata on MDSYS. ALL_SDO_GEOM_METADATA
Dropped and recreated the spatial indexes as MDSYS.SPATIAL_INDEX_V2 instedd of MDSYS.SPATIAL_INDEX
Alter table to put parallelism to 1
Checked that all geometric data (BLOB) are stored as SECURFILE LOBS
Changed database parameter SPATIAL_VECTOR_ACCELERATION to TRUE
Still, the performances are very poor.
The greatest increase in execution time is due to the following select:
SELECT SDO_GEOM.SDO_INTERSECTION(FIGURA,mySdoGeometry,1)
INTO mySdoGeometry2
FROM DADO_AVM.AVM_DTRIP_PERCORSI
WHERE DAL <= TRUNC(p_data_invio)
AND AL > TRUNC(p_data_invio)
AND PERCORSO = p_percorso
;
mySdoGeometry is a local variable of SDO_GEOMETRY type, representing a polygon (a square with a 130 meters side). The WHERE clause identifies one and only one record in the DADO_AVM.AVM_DTRIP_PERCORSI, and the FIGURA field is the SDO_GEOMETRY data where a route is represented. This select gets hit an average of 1400 times a day by each bus (we have 1.600 buses), and it *has* to answer as quickly as possible. In the current production environment it gets executed in not (appreciable) time, so there must me something I’m missing…
Can anyone help?
Thanks from Paolo!