Original User: Ameskamp
Hi all,
we've been facing a nasty Oracle problem for some time now and I'm wondering whether we're the only ones seeing this: We have a fairly complex application based on ArcGIS 9.3.1, geometry storage ST_GEOMETRY, about 150 feature classes, two geometric networks etc. Database platform is Oracle 11.1.0.7 on both Windows Server 2003 and IBM AIX 6.1.
We've noticed that ArcMap response time varies very much with certain operations. E.g. the first interactive select of a couple of features after starting ArcMap normally takes about 3 seconds, but sometimes it takes much longer (10-15 seconds).
After some testing we looked at Oracle traces and found that Oracle generates a huge number of recursive statements (selects as user SYS against Oracle system tables like sys.hist_head$ or sys.icol$). We have a test scenario of about a dozen interactive actions that generates around 15,000 SQL (user) statements. Sometimes, only these user statements get executed, in these cases the total time spent in the database (according to tkprof summaries) is around 25-30 seconds. At other times, Oracle generates up to 45,000 internal or recursive statements and total database time is more than 100 seconds.
I wonder if anyone is seeing similar problems - large fluctuations in database response times for interactive ArcMap actions without apparent explanations in database load. If someone suspects that they have a similar problem - identifying this phenomenon with Oracle Tracing is fairly easy, I can post or mail instructions if needed.
Thanks very much for any comment, Martin
