topic Re: add layer to Map from Oracle 19C Spatial (SDO_GEOMETRY) is very slow on big tables in ArcGIS Pro Questions

add layer to Map from Oracle 19C Spatial (SDO_GEOMETRY) is very slow on big tables

MatjazHabic — Thu, 19 May 2022 13:50:50 GMT

When we add Layer from Oracle 19c (SDO_GEOM geometry), there is dialog for calculating extent.

If we chose first or second option, it takes over 30 minutes for application to respond.

If we check sessions on database there is following select with full table scan which can't be terminated. Sum of elapsed time and remaining time is over 1700 seconds.

SELECT SDO_AGGR_MBR (GEOM)
FROM CGIS_WEBGIS.ZKN_GR x
WHERE x.GEOM.SDO_GTYPE IS NOT NULL

If we manualy enter Extent for that layer, why application still run this sql statement ?

Table has over 5Mio polygon records.

We have Oracle 19C and Arcgis Pro 2.9.3.

Re: add layer to Map from Oracle 19C Spatial (SDO_GEOMETRY) is very slow on big tables

RobertBorchert — Thu, 19 May 2022 14:57:04 GMT

There could be a huge variety of factors that could contribute. You may have to submit a ticket to ESRI.

What is your hardware?

RAM, Processor, HHD, SSD, cores etc.

Those are all things that will effect processing time.

Have you tried loading the data in question into a FileGeodatabase on a local drive and comparing the time and performance?

Are you in office directly connected via Ethernet to your system or using Wi-Fi

Re: add layer to Map from Oracle 19C Spatial (SDO_GEOMETRY) is very slow on big tables

RobertBorchert — Thu, 19 May 2022 14:59:03 GMT

However, 5 million records is probably the largest contributing factor. To that point you may be bogged down by bandwidth, and processing power.

Re: add layer to Map from Oracle 19C Spatial (SDO_GEOMETRY) is very slow on big tables

George_Thompson — Thu, 19 May 2022 15:17:05 GMT

Is the table registered with the geodatabase or just a spatial table in Oracle?

If it is not registered it will do a full table scan to find the extent. This is expected. So the 1700 seconds is about 28 min.

Re: add layer to Map from Oracle 19C Spatial (SDO_GEOMETRY) is very slow on big tables

MatjazHabic — Fri, 20 May 2022 05:33:35 GMT

Hi,

the table is just a spatial table in Oracle. I understand that full table scan (FTS) last so long, but it is not necessary to get max extent from database, if I enter this coordinates in input form

My opinion is that this input form is made for this type of situations to avoid long lasting FTS.

Performance (zoom,pan) after initial layer adding is ok.

Matjaž

Re: add layer to Map from Oracle 19C Spatial (SDO_GEOMETRY) is very slow on big tables

MatjazHabic — Fri, 20 May 2022 06:00:51 GMT

This is possible workaround, where we can’t use Drag&Drop from Catalog pane.

If I add layer via Make Query Layer and provide all necessary data, process completes within 2 seconds.

Matjaž

Re: add layer to Map from Oracle 19C Spatial (SDO_GEOMETRY) is very slow on big tables

George_Thompson — Fri, 20 May 2022 10:42:07 GMT

Using the Make Query Layer tool, as you referenced above, may be the best option cause you provide the extent.

One item to know is that the query layer will only exist in the map document.