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
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!
