Slow Selects ArcSDE Oracle/ArcMap 10.1

AnnalieseVollick — Mon, 12 Nov 2012 15:11:06 GMT

I hope to save someone in the future the grief I went through the past few days troubleshooting slow attribute and spatial/by location selects in ArcMap against Oracle SDE layers by posting this.

First, the basics of our set up.
ArcGIS (ArcMap/ArcSDE) 10.1
ArcSDE on Oracle 11g R2 on Unix AIX (7.1)
ArcMap on Windows 7 SP1, 4GB Ram, 32 Bit, 3.3Ghz

The behaviour was trying to select approximately 6000 of 29000+ points that fall in a selected polygon(select by location) or, approximately 900 of 29000+ records that are null (select by attribute) was taking upwards of an hour (or so). Compared to our 9.1 (ArcSDE/Oracle 9i/Windows XP) set up which returned the results in seconds.

I checked all database/SDE parameters and set what wasn't, to what was recommended. I searched forums, blogs, compared server_config/dbtune between 9.1 and 10.1 settings and researched some more, etc. for any way way to speed this up when all settings seemed fine. Finally, I found these two links:
http://support.esri.com/en/knowledgebase/techarticles/detail/37841

http://support.esri.com/en/knowledgebase/techarticles/detail/32164

By dropping Statistics and locking SDE_LOGFILE_DATA tables created for each user I'd used to query the data, the queries complete in seconds in the 10.1 environment too. I will be keeping an eye on this as I add new users to this set up.

If this is common knowledge, forgive me, but I struggled to find any pointer to this information and must have missed where this is covered in the ArcSDE Oracle documentation I was wading through. I hope it may help someone else!

Annaliese

topic Slow Selects ArcSDE Oracle/ArcMap 10.1 in Data Management Questions

Slow Selects ArcSDE Oracle/ArcMap 10.1