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
Additional Information
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:
Actions Taken So Far
Observations
Request for Assistance
I would greatly appreciate the community's insights and suggestions regarding:
If you need additional details about our environment, configurations, or actions taken, I would be happy to provide them.
Thank you,
Jack
GIS Consultant
Forgot to mention that as a temporary fix, I have been unregistering and re-registering the problematic feature class/tables as versioned. This resolves the performance issue, but only for about 2 days before the problem reoccurs.
Just as a comparison for performance could you create a file gdb that registered with ArcGIS Server and perform a parallel daily truncate and append of the updated SDE feature classes to the file gdb. It is very important that you use the Truncate Table tool with this workflow.
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).
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.
Based on your description it is unclear to me if the slowness is caused by the database or by your services. You did not mention the CPU and RAM utilization for your ArcGIS Server. Is there much free RAM and CPU during peak activity?
I like @MichaelVolz suggestion. If your users do not need to immediately see the latest changes to the Enterprise geodatabase you can update the feature classess to one or more FGDBs. Then use the FGDBs as the source for your map services / feature services. Create automated scripts that run during slack times to move data from the enterprise geodatabase to the FGDB
I get the impression that you have multiple feature classes in your map service / feature service. Are you using dedicated services or a shared pool service? I would recommend publishing the feature classes with the heaviest traffic as their own dedicated service, e.g. one feature class = one service. If you only have a few feature classes you can publish them all this way and assign more instances to the services with higher traffic. If you have a lot of services you can publish some of the lower traffic services in a shared pool.
Bernie.
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.