Experiencing severe performance issues on Oracle SDE Branch Versioning

[Royce_Simpson]

I'm an IT GIS support person for my org and not a Roads and Highways expert, so I don't have deep knowledge of LRS or even Branch versioning, but... We have an Oracle SDE Branch Versioned Roads and Highways implementation.  We do a lot of offline replicas in Field Maps.  We also have a few non-LRS-related layers in that database for reference, such as our 180,000-polygon parcel layer.

We are experiencing severe performance issues with this database.  We have other Oracle SDE databases that handle much more traffic and are much more performant.  They use traditional versioning, not Branch Versioning.

In simple terms, if I load any layer from this R&H geodatabase into Pro, rendering performance is abysmal.  For example, if I load the parcel layer, it will draw 10 or so polygons at a time, very slowly.  When I do the same operation from one of our other geodatabases with the parcel data, the render time in Pro for the entire layer at county scale is about 7 seconds.

I've noticed that in this Branch Versioned geodatabase, we have tons of old offline replicas.  I've cleaned a lot of that up, but from a performance perspective, nothing has changed.

Also, in our other traditional versioned GDB, the parcel layer has 180,000 total rows... Exactly the number of parcel polygons in the layer.  In the Branch Versioned LRS geodatabase, the same layer has over 15 million total rows, even though the attribute table shows 180,000 polygons. My guess is that we occasionally do truncate/load on that layer to update it with the latest parcel polygons, but the database is holding onto all that history.

Anyway, I'm seeing a pattern here, but have no idea how to fix this and get this geodatabase back to being performant.

I'm posting this here because I've seen that the Prune Branch History tool can address many of these issues, but I'm seeing that an LRS-enabled geodatabase doesn't support the Prune Branch History tool's operation.  If it does, I can't seem to run the tool anyway because it errors out right away.

Does anyone have advice for this?  I'm totally baffled and have run out of ideas on how to get things performant again.

[AyanPalit]

@Royce_Simpson The notes with performance times are helpful. Please add the ArcGIS Enterprise and Pro versions and Oracle versions in use. 

Branch version scenarios : By design branch versioned geodatabase is insert-only model, so delete/truncate operation adds on to the rows in the RDBMS table.

 Prune Branch History does clean up archived, intermediary transactions in the geodatabase. The tool has its own requirements to work correctly.  Looks like you tried this, what error did you run into?  

Are the latest patches applied? Please check specifically on the ArcGIS Server Utility Network and Data Management Patches. Enhancements and bug fixes to branch versioning are included in these patches. 

[Royce_Simpson]

Hi Ayan.  Thanks for getting back on this.

All ArcGIS Enterprise patches and OS patches are current.

Here's the Prune Branch History error:

-Royce

[AyanPalit · Accepted solution]

@Royce_Simpson Thanks for posting the error screenshot, confirming the current limitation of  Prune Branch History with LRS-enabled geodatabase. There is a pending enhancement request and I would recommend logging a Tech support case to have your organization added to this enhancement request.

Meanwhile, explore other options to make the layers performant and get ideas from the community.

• I can think of the option to test in a lower environment where you un-version the layer. While this is neither elegant and nor recommended if you must retain the edit history, un-versioning should collapse the  archived, intermediary transactions in the geodatabase. 
• Look at moving the parcel layer to a feature dataset other than the LRS feature dataset. It opens up the option to keep the landbase feature dataset un-versioned. You can perform maintenance and tuning on this landbase feature dataset without affecting the LRS. This is a schema change, so discuss, plan and test before implementing changes.