SPATIAL QUERY PERFORMANCE SLOWER AFTER UPGRADE FROM 12.2 TO 18C/19C (Doc ID 2731010.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

On : 19.1.0.0.0 version, Spatial Query Issues

With SVA parameter is set to TRUE the following statement gives bad performance on 19.7, while on 12.2 it works faster:

--19.7
CREATE TABLE TEST1
AS
SELECT T1.ID_GIS, MAX(TO_NUMBER("LEVEL")) as ZAB_BUDYNKI_W450 FROM
            USER1.TEST2 T1 INNER JOIN
            USER1.TEST3 T2
ON (sdo_anyinteract(T1.GEOM, T2.GEOM) = 'TRUE')
GROUP BY ID_GIS

call     count       cpu    elapsed       disk      query    current        
rows
------- ------  -------- ---------- ---------- ---------- ----------  
----------
Parse        1      0.20       0.23          7       2280          0          
 0
Execute      1      3.64    7920.65          0          7         51    
9921890
Fetch        0      0.00       0.00          0          0          0          
 0
------- ------  -------- ---------- ---------- ---------- ----------  
----------
total        2      3.84    7920.88          7       2287         51    
9921890

--12.2
CREATE TABLE TEST1
AS
SELECT T1.ID_GIS, MAX(TO_NUMBER("LEVEL")) as ZAB_BUDYNKI_W450 FROM
            USER1.TEST2 T1 INNER JOIN
            USER1.TEST3 T2
ON (sdo_anyinteract(T1.GEOM, T2.GEOM) = 'TRUE')
GROUP BY ID_GIS


call     count       cpu    elapsed       disk      query    current        
rows
------- ------  -------- ---------- ---------- ---------- ----------  
----------
Parse        1      0.21       0.23          2       2292          0          
 0
Execute      1      0.55     324.23          0          7         57    
9921890
Fetch        0      0.00       0.00          0          0          0          
 0
------- ------  -------- ---------- ---------- ---------- ----------  
----------
total        2      0.77     324.47          2       2299         57    
9921890

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.