In the last couple of weeks we are facing lot of issues in our development and testing environments for connections to Oracle geodatabase through ArcCatalog. We have different databases for Asset and Basemap. There is not a huge amount of data in the asset database, still it takes too much time to connect to it, like maybe 2 minutes or so. It connects through SQLPlus in 1 or 2 seconds.
While connecting to Basemap database, it causes ArcCatalog to hang and go into a not responding state. This was not the case couple of months ago when the databases and schemas were newly created and data was loaded. At worst we were able to connect in 10 seconds through ArcCatalog.
Since then there have been many changes related to data model in the geodatabase and also many times the database was refreshed with new set of data.
Any suggestions will help. We're using ArcGIS Desktop 10.4.1 on Windows 2012 VM servers. We have 12 GB RAM and quad core processor.
Another thing which I want to highlight is that we're using versioning for spatial data editing through Workflow Manager. In many cases, during the development, the versions which were created haven't been deleted. Will deleting the unnecessary versions help?
One more thing is that we have installed the full Oracle Client as the DBMS client to allow ArcCatalog to connect to Oracle geodatabase. Is the full version required or is Instant Client sufficient?