We have been using ArcGIS Desktop and Oracle 11g for many years. A year ago we migrated from ArcGIS 9.3.1 to ArcGIS 10.3.1 moving away from SDE schema to user based geodatabase schema. Recently our users are complaining on the slowness in the connection. In which we noticed that the connection to Oracle drastically slowed to beyond 30 seconds additionally refresh connection and expand connection takes the time to unprecedented 6 minutes. We have over 2000 featureclasses but this was not a problem in ArcGIS 9.3.1. What could be the issue?
Have been through forums and found a bug relatively similar as in the following link
Related Bug accepted in ESRI
Bug BUG-000081116:Optimize the TableListTables command or query involved in enterprise database connections.
Is this the bug related to my issue. Can someone help to guide us?
It is possible that you are encountering that specific issue or another one (unknown). If you wanted to test that defect theory out, install an ArcGIS 10.5.1 Desktop client and re-test just the connection speeds. That is the version that it was corrected in.
You may want to get a new case created with Esri Technical Support to confirm that is the correct issue.
Hope this helps.
Side note: you mentioned that you also migrated to
"...to user based geodatabase schema."
You may want to revisit that if you created a new geodatabase for each schema as support for those will be going away in the future.
Support for creating a geodatabase in a user's schema in Oracle:
We are announcing the deprecation of the creation of new user schema geodatabases in Oracle. ArcGIS 10.6.x and the companion release of ArcGIS Pro will be the last release to support creation. At 10.6.x upgrade and connection to existing user-schema geodatabases will still be supported, though not recommended. The ability to upgrade or connect will be deprecated in subsequent releases.
Please note that we have had the following warning in our documentation since 10.1: “Be aware that the amount of time it takes to connect to the Oracle database will increase as the number of user-schema geodatabases you create and use increases. This is especially true if the user-schema geodatabases contain a mix of geodatabase tables and tables that are not registered with the geodatabase.”
Current doc and limits of user-schema geodatabases in Oracle: Create a geodatabase in Oracle—Help | ArcGIS Desktop
User-schema geodatabases are already deprecated. You probably don't want to move into that tangle from 9.3.1. 10.x should be much faster than 9.3.1 in listing tables because of changes implemented early in the 10.0/10.1 lifetime. Given the age of 10.3.1, you may find yourself back in Retired support status well before you get used to using Python.