Forum Stats

  • 3,768,263 Users
  • 2,252,769 Discussions
  • 7,874,510 Comments

Discussions

SELECT SDO_GEOM.SDO_INTERSECTION performance issue

Paolo Castagno
Paolo Castagno Member Posts: 9 Blue Ribbon
edited Jul 16, 2021 7:11AM in Spatial Discussions

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!

 

Answers

  • _jum
    _jum Member Posts: 504 Gold Badge
    edited Jul 15, 2021 2:59PM

    How is the execution plan for

    SELECT SDO_GEOM.SDO_INTERSECTION(FIGURA,mySdoGeometry,1) ...
    

    is there a spatial index on FIGURA and is it used?

    Rick Anderson-Oracle
  • Rick Anderson-Oracle
    Rick Anderson-Oracle Member Posts: 131 Employee

    What does EXPLAIN PLAN show for this query? There should be a DOMAIN INDEX clause in the EXPLAIN PLAN output.

  • Paolo Castagno
    Paolo Castagno Member Posts: 9 Blue Ribbon
    edited Jul 16, 2021 7:05AM

    Fitrst of all, thank you for replying.


    Yes, there is a spatial index on FIGURA:

    CREATE INDEX DADO_AVM.AVM_DTRIP_PERCORSI_ORA_SX ON DADO_AVM.AVM_DTRIP_PERCORSI
    (FIGURA)
    INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2
    NOPARALLEL;
    

    This is the explain plan in 19c env:

    Plan hash value: 2477972316
    -------------------------------------------------------------------------------------------------------------
    | Id | Operation                          | Name                  | Rows | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT                    |                       |  1   | 86    |  4 (0)     | 00:00:01 |
    |* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| AVM_DTRIP_PERCORSI    |  1   | 86    |  4 (0)     | 00:00:01 |
    |* 2 | INDEX RANGE SCAN                   | AVM_PERCORSI_PERCORSO |  3   |       |  1 (0)     | 00:00:01 |
    -------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
     1 - filter("AL">TRUNC([email protected]!) AND "DAL"<=TRUNC([email protected]!))
     2 - access("PERCORSO"='PI5A')
    

       in the old 11g is slightly different (don't know what "batched" stands for):

    Plan hash value: 2304119998
     
    -----------------------------------------------------------------------------------------------------
    | Id | Operation                  | Name                  | Rows | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT           |                       |   1  |  86   |   4  (0)   | 00:00:01 |
    |* 1 | TABLE ACCESS BY INDEX ROWID| AVM_DTRIP_PERCORSI    |   1  |  86   |   4  (0)   | 00:00:01 |
    |* 2 |  INDEX RANGE SCAN          | AVM_PERCORSI_PERCORSO |   3  |       |   1  (0)   | 00:00:01 |
    -----------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
      1 - filter("AL">TRUNC([email protected]!) AND "DAL"<=TRUNC([email protected]!))
      2 - access("PERCORSO"='PI5A')
    


    Does it rings any bell?

    -Paolo

  • _jum
    _jum Member Posts: 504 Gold Badge
    edited Jul 16, 2021 9:39AM

    No, the spatial index (DOMAIN INDEX) can't be used in your query.

    You could test if it is used with:

    SELECT dal, al, SDO_GEOM.SDO_INTERSECTION(FIGURA,mySdoGeometry,1)
      FROM DADO_AVM.AVM_DTRIP_PERCORSI
     WHERE SDO_ANYINTERACT(FIGURA,mySdoGeometry) = 'TRUE';
    
  • Paolo Castagno
    Paolo Castagno Member Posts: 9 Blue Ribbon
    edited Jul 16, 2021 10:38AM

    Yes, i think it gets used:

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 3684427479
    
    -------------------------------------------------------------------------------------------------------------
    | Id | Operation                      | Name                      | Rows | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT               |                           |    1 |    86 |      1  (0)| 00:00:01 |
    |  1 | TABLE ACCESS BY INDEX ROWID    | AVM_DTRIP_PERCORSI        |    1 |    86 |      1  (0)| 00:00:01 |
    |* 2 |  DOMAIN INDEX (SEL: 0.000000 %)| AVM_DTRIP_PERCORSI_ORA_SX |      |       |      1  (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------------
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
      2 - access("MDSYS"."SDO_ANYINTERACT"("FIGURA","MDSYS"."SDO_GEOMETRY"(2003,82088,NULL,"SDO_ELEM_INFO_ARRAY"(1,1003,3),"SDO_ORDINATE_ARRAY"(2354140.6,4592751.65,2354010.6,4592621.65)))='TRUE')
    


  • _jum
    _jum Member Posts: 504 Gold Badge

    Now the index is used and you can use the results as subquery or WITH-clause to find the wanted rows:

    SELECT dal, al, ingeom
     FROM
     (SELECT dal, al, SDO_GEOM.SDO_INTERSECTION(FIGURA,mySdoGeometry,1) intgeom
       FROM DADO_AVM.AVM_DTRIP_PERCORSI
      WHERE SDO_ANYINTERACT(FIGURA,mySdoGeometry) = 'TRUE')
     WHERE dal   <= TRUNC(p_data_invio)
      AND al     > TRUNC(p_data_invio)
      AND percorso = p_percorso; 
    

    In rare cases, if the DOMAIN INDEX for the subquery is not used, you could need a MATERIALIZE hint:

    /*+ MATERIALIZE */

    for the subquery .

  • Rick Anderson-Oracle
    Rick Anderson-Oracle Member Posts: 131 Employee

    You should also run statistics collection on the various indexes as the spatial index has an estimated cardinality of 0.0%

    which might result in the index not being used.

  • _jum
    _jum Member Posts: 504 Gold Badge

    Rick, well spotted 👍️

  • Paolo Castagno
    Paolo Castagno Member Posts: 9 Blue Ribbon

    Thank you both for the valuable suggestions.

    BTW, I tried to update index statistics by executing DBMS_STATS.GATHER_INDEX_STATS and there were no changes in the execution plan (I also tried to drop, recreate and analyze the index).

    I also tried to implement the select as suggested by Jum, but the result was much worse than the original one: I had to kill the session because it was taking too long.

    What I did to "solve" the problem was rewrite the select as follow:

         SELECT FIGURA
         INTO   myPercorso
         FROM   DADO_AVM.AVM_DTRIP_PERCORSI
         WHERE  DAL    <= TRUNC(p_data_invio)
         AND    AL      > TRUNC(p_data_invio)
         AND    PERCORSO = p_percorso
         ;
         mySdoGeometry2 := SDO_GEOM.SDO_INTERSECTION(myPercorso,mySdoGeometry,1); 
    

    So the select is just plain SQL with no spatial stuff involved (just the data type), and the resulting geometry is calculated surely just once.

    And now the PLSQL procedure in the new environment is somewhat faster than the old one.

    Thank you again,

    - Paolo