Tuning Geodatabase for Oracle 11g

883
3
Jump to solution
01-26-2022 01:43 AM
yockee
by
Occasional Contributor II

Hi,

I am wondering if there are queries to check the geodatabase overall performance ? I want to monitor the performance and tune it.

I have geodatabase with condition like this :

- being accessed by 20 people max (this is the total peak of concurrent users) editing (through ArcGIS Pro) via web feature service published at Portal on premise and some using ArcMap.

- There are many viewers (via web app and android). I guess wildly and without prove : <350 viewers via web apps

- SDE schema is not separated from USER schema. No multiple tablespaces nor partitions

- All desktop users connect using Direct Connect

- Arcgis server, Geodatabase and Portal are hosted separately

- Lots of KEYSET tables (7000+ tables) and indexes, lots of unnecessary tables too

- Lots of Relationship Classes

- Accessed by different clients : Desktop editing, Web (Feature Service editing, Web apps queries, Android apps queries)

- Others : Set to OLTP database, Using ST_GEOMETRY as spatial datatype, Non-versioning (because it is not necessary)  

- Slow when loading big imagery and feature class

- There are 500+ map services

- Many times, no separation between Publishing and Production geodatabase. One sample : editing geodatabase via FS published through local Portal

- Many Feature Datasets (FD). It is said (ESRI UC 2005, Geodatabase Tuning and Performance, p.12) that having FD will generate unnecessary server activities

Questions :

1. What Oracle parameters do I have to check to maximize performance (I want to start with the database first) ?  

2. Will removing Feature Dataset helps ? By how much performance will be gained in removing FD ?

3. Should I impose more on utilizing Subtype and Domain ? Are there anything else besides these...? 

4. Does having many Map Services affect geodatabase performance ?

5. Which once degrades the server performance the least : Editing via ArcMap using Direct Connect or editing via FS, published at on-premise Portal, using ArcGIS Pro ?

One thing for sure is to suggest separation between Publication and Production geodatabase

 

Thanks for your thoughts and considerations. I appreciate them... 

environment : ArcGIS Geodatabase 10.5, Oracle 11g, ArcMap 10.5, ArcGIS Pro 2.8

0 Kudos
1 Solution

Accepted Solutions
George_Thompson
Esri Frequent Contributor

I would run the EGDB Health tool and look at the export to see what potential recommendations (if any) are suggested.

Here are some other guides: https://www.esri.com/content/dam/esrisites/en-us/about/events/media/UC-2019/technical-workshops/tw-6...

https://www.esri.com/content/dam/esrisites/en-us/about/events/media/UC-2019/technical-workshops/tw-6...

--- George T.

View solution in original post

3 Replies
George_Thompson
Esri Frequent Contributor

First, I would recommend looking at upgrading to a newer release. 10.5.x is in mature status and will be retired in Nov 2022 (https://support.esri.com/en/Products/Desktop/arcgis-desktop/arcmap/10-5-1#product-support)

With that out of the way, here are some resources to help you understand / tune your Oracle geodatabase, if needed:

Here are my responses to your questions and will say that there may be other ways available:

  • What Oracle parameters do I have to check to maximize performance (I want to start with the database first) ?
    • I usually do not touch this unless needed
  • Will removing Feature Dataset helps ? By how much performance will be gained in removing FD ?
    • It depends, it could improve performance. I usually recommend FD's if you have advanced GDB functionality (topology, network datasets, etc.) or a requirement.
  • Should I impose more on utilizing Subtype and Domain ? Are there anything else besides these...? 
    • This is a great way to improve your data integrity and accuracy
  • Does having many Map Services affect geodatabase performance ?
    • Depends on the map services and complexity
  • Which once degrades the server performance the least : Editing via ArcMap using Direct Connect or editing via FS, published at on-premise Portal, using ArcGIS Pro ?
    • I would think that these are the same and is based on use case.

One thing for sure is to suggest separation between Publication and Production geodatabase.

              This is a good strategy if you are having issue already and have the need to separate the workloads.

 

Hope this helps in the initial conversation. 

--- George T.
yockee
by
Occasional Contributor II

Hi George,

"What Oracle parameters do I have to check to maximize performance (I want to start with the database first) ?

    • I usually do not touch this unless needed"

What I want to do is performance tuning, maybe by increasing SGA or PGA, block size etc, as outlined in some Esri articles about geodatabase tuning.

I want to investigate some Oracle parameters, what they are, what their values are and how / when their values are considered bad or good. I can't seem to find any study on this (I know, each database is unique, but I guess the parameters are similar somehow) .

 

Thanks.

0 Kudos
George_Thompson
Esri Frequent Contributor

I would run the EGDB Health tool and look at the export to see what potential recommendations (if any) are suggested.

Here are some other guides: https://www.esri.com/content/dam/esrisites/en-us/about/events/media/UC-2019/technical-workshops/tw-6...

https://www.esri.com/content/dam/esrisites/en-us/about/events/media/UC-2019/technical-workshops/tw-6...

--- George T.