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

[MatjazHabic]

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.

[RobertBorchert]

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

[RobertBorchert]

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

[George_Thompson]

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.

[MatjazHabic]

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ž

[MatjazHabic]

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ž

[George_Thompson]

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.