Persistent Performance Issues with WebGIS Layers (Timeouts & Slow Loading)

Question

Hello team,

Over the past two weeks, we have been facing persistent performance issues with specific layers in our WebGIS applications. To provide some context, in my organization, we recently migrated from Oracle 12c to Oracle 19c. We are publishing services using ArcMap, and we use custom-built WebGIS applications to display those layers.

The layers related to Planning and Building Applications fail to load, resulting in timeouts on requests to the services. Notably, other layers (e.g., Planning Zones, Land Use, etc.) from the same service work perfectly without delays or timeouts.

I am not an expert in database architecture, but below, you can find some details about our environment:

Environment Details

* GIS Server Version: 10.9.1 (build 28388)
* Database: Oracle Enterprise Geodatabase (19c)
* Client Applications: ArcMap 10.8.2 and custom WebGIS applications
* Affected Feature Classes and Tables:
* Applications (versioned feature class)
* Building_Permits (versioned table)
* Planning_Permits (versioned table)

Additional Information

* Planning Permits Layer: Applications (feature class) joined with Planning_Permits (table) using “keep only matching records.”
* Building Permits Layer: Applications (feature class) joined with Building_Permits (table) using “keep only matching records.”
* The above feature class and tables have spatial and attribute indexes on joined fields.
* We are not using complex symbology or Definition Queries that might affect drawing times.
* These layers are the most heavily edited objects in our database, with approximately 600 new records being added daily via post commands from an ArcGIS REST API.

Every night, a compress script is run via Task Scheduler to maintain performance. I check the log file daily, and it takes approximately 2-5 minutes to complete. However, we are not performing a full database compress. Instead, we:

* Block new connections to SDE.
* Disconnect all users.
* Run the compress tool.
* Allow new connections.

Actions Taken So Far

* Restarted GIS Servers and Services: No improvement observed.
* Disabled Automatic Compress Tool: Resolved the issue for one day but was not a long-term fix.
* Diagnostics: Ran Diagnose Version Metadata and Diagnose Version Tables, but no issues were identified.
* Checked and Corrected Geometries: Used the Check Geometry tool to fix problematic geometries.
* Recreated Feature Classes and Tables: This temporarily improved performance but only for a maximum of two days.
* Created New Attribute and Spatial Indexes: Same temporary improvement as above.

Observations

* The issue is consistently observed in both WebGIS applications and ArcMap.
* The problem is isolated to the specific layers mentioned above, while other layers and tools function without delays.

Request for Assistance

I would greatly appreciate the community's insights and suggestions regarding:

* Additional diagnostics or monitoring tools I can use to identify the root cause.
* Best practices for maintaining and optimizing feature classes with high usage.
* Any similar experiences and solutions that have worked in such scenarios.

If you need additional details about our environment, configurations, or actions taken, I would be happy to provide them.

Thank you,

Jack

GIS Consultant

bixb0012 · Answer

If unregistering and reregistering improves performance for a while, I would look into what the state tree looks like over time.  Diagnose Version Tables only looks for specific kinds of issues, and the tool doesn't look at overall shape and length of state tree.  Unfortunately I don't think Esri has gotten around to including a state tree diagram tool in Pro yet, but one can still query the system tables to get the state tree information.

IakovosFlorides · Answer

I don’t know CPU and RAM utilization of ArcGIS Server, but to be honest I don’t think this is the root of the problem. As I mentioned in my previous comment, the performance issues persist even if I add the problematic feature class and tables in an ArcMap.

The way our services are constructed is that we have services mainly for Adding and Deleting Features (Map/Feature Server) using arcgis/rest/api and other services just for viewing data (Map Server). All services are being published with dedicated instance type.

Also, to give you an update, I managed to reproduce the problem in our Test environment. The way I reproduced the problem is that I exported approx. 1000 records from our Prod environment and used a custom script to add features in the respectively feature class and tables of the Test env. It is worth mentioning that the script is adding each feature individually and not all at once.

IakovosFlorides · Answer

Unfortunately I can’t proceed with your recommendation because the problem occurs in Production environment and in my organization, we need to view the data live at any time (except during the compression process).