- 17.9K All Categories
- 3.3K Industry Applications
- 3.2K Intelligent Advisor
- 59 Insurance
- 534.1K On-Premises Infrastructure
- 137.6K Analytics Software
- 38.5K Application Development Software
- 5.3K Cloud Platform
- 109.1K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 70.8K Infrastructure Software
- 105.1K Integration
- 41.5K Security Software
SELECT SDO_GEOM.SDO_INTERSECTION performance issue
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!