Spatial Update to DIAGRAM_OBJECTS Table During Model Build Takes Long Time to Complete (Doc ID 2754675.1)

Last updated on FEBRUARY 22, 2021

Applies to:

Symptoms

On : 2.3.0.2.0 version, Data Model

Customer has several NMS environments.
Model builds are extremely slow on only one system.

There are no detectable spatial configuration differences between the systems.


Here are the lines from the MBService log showing the times it took to process the spatial data for those patches:
MBService 0x7f74a4aac700,ctp=0xf9c9dd8,"MBService::build" MBService::submit(3040) spatializePatch took 2163166 ms.
MBService 0x7f74a4a2a700,ctp=0x7f74967d3408,"MBService::build" MBService::submit(3041) spatializePatch took 4251468 ms.
MBService 0x7f74a4aac700,ctp=0x7f74967d3408,"MBService::build" MBService::submit(3042) spatializePatch took 9131475 ms.
MBService 0x7f74a4a6b700,ctp=0x7f74967d3408,"MBService::build" MBService::submit(3043) spatializePatch took 1346082 ms.
MBService 0x7f74a8722700,ctp=0x7f74967d3408,"MBService::build" MBService::submit(3044) spatializePatch took 906914 ms.

The MBDBService log pointed to several extremely long-running update statements:

12/15/20 15:39:22: 0x7fe44899b700,ctp=0x2075de8,"sql" sql: (update diagram_objects do  set do.geo_geometry =    mdsys.sdo_geometry (      2001,      3857,      mdsys.sdo_point_type (        (select pc.x_coord from point_coordinates pc          where do.h_cls = pc.h_cls          and do.h_idx = pc.h_idx          and do.diagram_id = pc.diagram_id          and do.partition = pc.partition          and pc.sequence = 1          and 'Y' = pc.active          and rownum = 1),        (select pc.y_coord from point_coordinates pc          where do.h_cls = pc.h_cls          and do.h_idx = pc.h_idx          and do.diagram_id = pc.diagram_id          and do.partition = pc.partition          and pc.sequence = 1          and 'Y' = pc.active          and rownum = 1),         NULL),      NULL, NULL)      where do.active  = 'Y'        and do.partition in (          select p.h_idx from partitions p          where p.active = 'Y' and p.coord_system = 0)        and do.h_cls not in (select distinct h_cls from partitions where active = 'Y')        and ( SDO_GEOM.RELATE(do.geo_geometry,'EQUAL',     mdsys.sdo_geometry (      2001,      3857,      mdsys.sdo_point_type (        (select pc.x_coord from point_coordinates pc          where do.h_cls = pc.h_cls          and do.h_idx = pc.h_idx          and do.diagram_id = pc.diagram_id          and do.partition = pc.partition          and pc.sequence = 1          and 'Y' = pc.active          and rownum = 1),        (select pc.y_coord from point_coordinates pc          where do.h_cls = pc.h_cls          and do.h_idx = pc.h_idx          and do.diagram_id = pc.diagram_id          and do.partition = pc.partition          and pc.sequence = 1          and 'Y' = pc.active          and rownum = 1),         NULL),      NULL, NULL)        ,0.1) = 'FALSE'         or do.geo_geometry is null)        and do.h_cls in (0,10004,10007,109,105,206,126,178,121,10219,10206,994,10006,187,10003,10103)        and do.partition = 1037;update diagram_objects do  set do.geo_geometry =mdsys.sdo_geometry(2002,3857,NULL,   mdsys.sdo_elem_info_array(1,2,1),   CAST(MULTISET(SELECT pc.COLUMN_VALUE       FROM point_coordinates pc,            TABLE(mdsys.sdo_ordinate_array(pc.x_coord,pc.y_coord)) pc       WHERE pc.h_cls = do.h_cls       and pc.h_idx = do.h_idx       and pc.partition = do.partition       and pc.diagram_id = do.diagram_id       ORDER BY pc.sequence, rownum)   AS mdsys.sdo_ordinate_array))      where do.active  = 'Y'        and do.partition in (          select p.h_idx from partitions p          where p.active = 'Y' and p.coord_system = 0)        and do.h_cls not in (select distinct h_cls from partitions where active = 'Y')        and ( SDO_GEOM.RELATE(do.geo_geometry,'EQUAL', mdsys.sdo_geometry(2002,3857,NULL,   mdsys.sdo_elem_info_array(1,2,1),   CAST(MULTISET(SELECT pc.COLUMN_VALUE       FROM point_coordinates pc,            TABLE(mdsys.sdo_ordinate_array(pc.x_coord,pc.y_coord)) pc       WHERE pc.h_cls = do.h_cls       and pc.h_idx = do.h_idx       and pc.partition = do.partition       and pc.diagram_id = do.diagram_id       ORDER BY pc.sequence, rownum)   AS mdsys.sdo_ordinate_array))        ,0.1) = 'FALSE'         or do.geo_geometry is null)        and do.h_cls in (0,10216,406,409,10202,443,426,429,420,440)        and do.partition = 1037;) result=0
12/15/20 15:39:22: 0x7fe44899b700,ctp=0x2075de8,"sql" returning from sql
12/15/20 15:39:22: 0x7fe44899b700,ctp=0x2075de8,"DBService::sql" DBService::dispatcher action=0 name=DBService::sql from=(1/45:6261.0) ms=4871311

12/15/20 15:40:21: 0x7fe44895a700,ctp=0x2075de8,"sql" sql: (update diagram_objects do  set do.ll_geometry = sdo_cs.transform(    do.geo_geometry,8307)  where do.active = 'Y'  and ( SDO_GEOM.RELATE( do.ll_geometry, 'EQUAL', sdo_cs.transform(    do.geo_geometry,8307),0.1) = 'FALSE' or do.ll_geometry is null)   and do.geo_geometry is not null  and do.partition = 1037;) result=0
12/15/20 15:40:21: 0x7fe44895a700,ctp=0x2075de8,"sql" returning from sql
12/15/20 15:40:21: 0x7fe44895a700,ctp=0x2075de8,"DBService::sql" DBService::dispatcher action=0 name=DBService::sql from=(1/45:6261.0) ms=57477

Model builds should not be taking a long time to process spatial data

The issue can be reproduced at will with the following steps:
1. Enable timing debug in MBService (Action any.MBService debug TIMING 2 )
2. Build several maps extremely large import files ( < 150+ MB )
3. Check the MBService log for timings

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.