How to isolate and fix Enterprise Geodatabase speed issues with ArcGIS Pro

[Department_of_Communications__]

Hi, 

I wonder if anybody has time to throw out a few ideas or knowledge snippets to help please? My issue is with an Oracle Enterprise Geodatabase (10.7.1 on Oracle 12) and Arcgis Pro 2.5 (and ArcMap).  The rough overview : I have a feature class of 990,000 records with 10 attributes and I want to update one attribute for all rows using field calculate in  Pro using direct connect to a non versioned feature class.  The calculate is just - update a column to a value such as 110.  

This is simplified for testing purposes.  My initial timings for this calculate is 18-20 minutes, post investigations we discovered symantec antivirus did not have exceptions for Oracle on the database server, once that was sorted the query updates in 10 mins 15 seconds, so a huge improvement but still way to slow.  For this test case my desktop was communicating across the network to perform this query/update, so we used wireshark  to see if there where packets or connections causing issues etc. (apologies I'm not a networking guy!) but apparently the network is fine, they also used cross over cables to test and take the actual network out of the equation but the times returned are roughly the same.  For sanity sake I installed Pro on the database server and ran the query locally - and it completes in 3 mins flat - a full 7 minutes faster.  

The DBA has edited the sqlnet.ora  as below to see if the network traffic can be addressed to reduce it .. but its had no effect so far ....

# sqlnet.ora Network Configuration File: E:\app\oracle\product\12.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NONE)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

DR_BASE = C:\app\oracle\product\12.2.0\dbhome_1\log
SQLNET.INBOUND_CONNECT_TIMEOUT=180
DIAG_ADR_ENABLED = OFF
Send_buf_size=65536
recv_buf_size=65536
default_sdu_size=65535

So can anybody recommend and approach to isolate the issue, as it only arises when i introduce the network into the equation , can anybody spot something glaringly obvious that I'm missing.  Is it possibly something in ArcGIS Pro that can be configured? Any thought observations or direction would be hugely appreciated.  

Cheers

Paulg

[CraigSwadner]

We are running Microsoft sql 2017 database, and we really don’t have a dataset that large however just a couple of questions / suggestions. Are you running a versioned database ? have you tried without versioning? Also if versioning how often is the db gone to full compress? Also you may want to do a trace on the database to see if indexing is necessary. i reindex a lot of our tables / layers daily for performance …

Craig Swadner (GIS Coordinator)

City of Cape Coral

1015 Cultural Park Blvd.

Cape Coral, Fl 33990

Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net

[JoshuaBixby]

Direct connections to enterprise geodatabases rely on the SDE communication protocol, and that protocol is very sensitive to latency.  What are your ping times and Oracle/TNS ping times to the database?

[jonathanharte]

Craig, Thanks for your observations ....  My database is a new instance - non versioned for testing and the feature class has been re-indexed each time I have tested against it. Since its oracle it useses its own explain plan for query updates etc.. so for the moment I have not had the dba mess with the explain plan, however he has traced and hasn't appeared to find any issues ... but to be honest I'm not ofay with traces and the inner workings of Oracle.... 

Joshua,  Thanks for your observations.  I have no alternative to direct connect (or do I ?).  My tnsping is 10msec.. I'll do a bit of reading on sde connection properties ..

Thanks guys.

Paulg

[JoshuaBixby]

Is it 10 ms from your remote PC or the Pro client installed on the database server?  If the remote PC, I normally would say that is not enough latency to cause major issues, but obviously some aspect of the network is involved if Pro on the database server runs 3x faster than on a remote PC.

[jonathanharte]

Hi Joshua,

10 msec remote pc  and 0 msec on the  database server

[MichaelVolz]

Do you get similar performance when using ArcMap for this field calculation compared to Pro?

Have you performed this operation in previous versions of ArcMap/Oracle and gotten better performance so your performance has degraded over time with the same dataset?

[jonathanharte]

Hi Michael,

Similar speeds but Pro is faster ...   This speed issue has existed for a good while now, but has only recently caused issues due to new workflows.   Doing the calculation on a client (Pro or ArcMap) on the database box its only 3 mins to execute so the issues arise with the network components / protocols of either the client or the database ?

The same dataset has the same performance over time as we have Oracle analyse the tables space often to help with the performance

Thanks for taking time to think about it...

Cheers

Paulg