Slow Perfomance of ArcSDE

[PatrickOberli]

Hi

First some background info. I need to write a document about why our Arcgis environment has the performance which it has. I never worked with Arcgis so far...
At least I know how to open some data in Arcmap and I have full access to all involved servers.
First the setup:
- Intel Quadcore Xeon E5420
- 4GB Ram
- Gentoo installation
- SDE93 (I think without patches, sdeversion shows: ArcSDE 9.3  for PostgreSQL Build 508)
- Postgresql 8.3
- 26GB database/Gisdata

I have a sample data, which consists of around 40 "SDE Feature Class" (I guess layers). If I open them in Arcmap it takes some 5 Minutes to show them.

The server shows in this time 100% CPU Load on one core with the process gsrvr and some 10-20% with postgres.

If I export this same data in Arccatalog to files and store them local and open them local, it takes some 15 seconds to show them in Arcmap.

How can I now check why this gsrvr takes so much cpu load, or is this to be expected and fully normal?

Thanks
Pato

[VinceAngelo]

"Draw all tables at maximum extent" isn't the fairest of benchmarks available -- It generates
the maximum possible load on the database server, which biases the test toward flat files.

How fast is the network between the client workstation and the Linux server?  If it's slower
than gigabit speed (or is burdened with load), then this is another bias toward flat files.

I wouldn't ever expect local flat files to have a longer draw time than an ArcSDE server
in a single-user test, but if you had patched your server and client to at least 9.3sp1,
you'd probably see better ArcSDE performance.  9.3.1sp2 is closer to current, and
would likely give optimal 9.3 performance.

How many feature datasets do you have in the server?  ArcGIS 9.x connect time is closely
bound to the number of feature datasets.

Has any tuning ever been performed on the ArcSDE server?

Was any tuning ever done during layer creation to optimize the coordinate referernces?

- V

[PatrickOberli]

Thanks for your answer.
The network is 1Gbit/s with low load.

>Has any tuning ever been performed on the ArcSDE server?
I fear yes, it looks currently like it's "overtuned".

I just made my test again. Loading from Arcsde Server takes a little bit over 8 Minutes (and it actually reloads everything again as I zoom or move the map).
Then I exported all the data in Arccatalog to a local drive as a 'Shapefile'. Then I opened this folder in Arccatalog and dragged the same data to a fresh Arcmap map. The loading from the local disk taskes around 15 seconds (compared to over 8 Minutes taking the data from Arcsde).

The local client installation is 9.3.1SP2.

> How many feature datasets do you have in the server? ArcGIS 9.x connect time is closely
bound to the number of feature datasets.
Absolutely no idea, how can I find that out?

> Was any tuning ever done during layer creation to optimize the coordinate referernces?
Also no idea.
Sorry about the lack of information 😞

[VinceAngelo]

I generally spend my time tuning data, not ArcSDE.  The only parameter I've ever
tuned that improved performance was to increase the transmit buffer size when
working with a satellite networking solution (and it gave the appearance of slower
response to the local network folks).

If you want to see where ArcSDE is spending its time, you can enable the SDETRACE
environment, and run your connection test.  Be warned: I did this with a 7500 table
instance with 1250 feature datasets with 9.2sp4 and it took an hour to generate
a 7Gb trace file.  Parsing that trace was a major PITA.  Be sure to disable the trace
variables as soon as the test completes -- I managed to generate a 64Gb trace once
(would have been larger, but the C: drive ran out of space).

- V

[ForrestJones]

Hi Pato,

One more thing to check is if the data in ArcSDE has a spatial index or not. A slow draw time when zooming or querying can be caused by not having a spatial index. To check just right-click the featureclass in ArcCatalog and choose properties. Then go to the "Indexes" tab. Then check the section for the spatial index.

[WilcoLoth]

Hi all!

Interesting topic! We're currently experiencing the same kind of problems when selecting data; selection is done quite fast, but the time to draw the actual selection takes an inexplicable long time!

Specs:

Intel Xeon X5450
4GB
MS Server 2008
Gigabit
ArcServer 9.3.1 SP1
Oracle Spatial (10G client)

Client machines running ArcGIS 9.3.1

We're connecting throught the Direct Connect-connection and we've also tweaked the register of a client machine (followed the steps mentioned in this post: http://resources.arcgis.com/content/kbase?fa=articleShow&d=22668).

We're really in the dark why the perfomance is sluggish; we're not using feature datasets at all since they tend to have anegative effect on overall database performance.

Any suggestions?

[VinceAngelo]

Which Oracle release are you using? (10.2.0.?)  What geometry storage are you using?

I was recently introduced to an optimizer issue that affects SDO_GEOMETRY in
Oracle 10.2.0.5-11.2.0.2.  There are drastic and less drastic solutions avaliable.

- V

[WilcoLoth]

Which Oracle release are you using? (10.2.0.?) What geometry storage are you using?

I was recently introduced to an optimizer issue that affects SDO_GEOMETRY in
Oracle 10.2.0.5-11.2.0.2. There are drastic and less drastic solutions avaliable.

- V

Dear vangelo,

Thanks for your reply!

We are indeed running Oracle 10.2.0 and all the feature classes are stored in SDO_GEOMETRY.

I'm curious to your drastic & less drastic solutions...

Greetings from The Netherlands,

Wilco

[VinceAngelo]

The issue I ran into is described in Metalink note 1268383.1.  We're still working toward
a solution but I've found several interesting features:
1) It seems to be intermittent
2) I couldn't reproduce the issue in an instance where Oracle Spatial was not installed
(Esri only uses the Locator capabilities available in Intermedia)
3) We were able to use a view on the original table with an optimizer hint to get spatial-
first queries that didn't result in full table scans.

In light of the above, the option to disassociate statistics with spatial queries seems
quite drastic indeed.

- V

[WilcoLoth]

@ Vangelo,

Thanks again for your reply!

The problem doesn't seem to be intermittent: it's actually always an issue. This afternoon I've loaded the 2 feature classes on which we've first encountered the problem in our test-DB and stored them in SDEBINARY.

The issue of extremely slow performance when displaying\visualizing a selection doesn't exist at all, which rules out any problems with the database. The problem is we're actually "stuck" with the SDO_GEOMETRY-storage option...

I'm gonna make a support call, though I'm a fraid there won't be an easy solution.

I'm gonna try and locate your Metalink note 1268383.1.

Thanks again & have a great weekend,

Wilco