ArcSDE 9.3.1 and Oracle 11.1 - performance problems

530
2
01-26-2011 06:13 AM
by Anonymous User
Not applicable
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
0 Kudos
2 Replies
anthonysanchez
New Contributor III
Hello Martin,
is it possible for you to post the raw trace as an attachment?


Anthony
0 Kudos
by Anonymous User
Not applicable
Original User: Ameskamp

Hi Anthony,

no, I'm afraid I can't upload a complete trace because it is too big (4 MB zipped). I do have a smaller trace though that exhibits similar problems: It's a simple select with a rubberbanding cursor after ArcMap startup which shows 521 user statements and 9386 internal statements. I can send you the full trace by mail if you like.

If you need any more details, please let me know.

Thanks for taking the time,

Martin
0 Kudos