Posts Tagged ‘ArcSDE’

Choosing the optimal configurations for GeoServer

December 29, 2011 1 comment

What I wanted to do

Setup GeoServer with optimal performance. I define optimal performance as fast response with large number of concurrent users (HTTP request of WMS). The GeoServer will be serving vector line data with 80K features. The vector lines are currently stored as a feature class in ArcSDE with Oracle.


I have identified several parameters/configurations could improve the performance of GeoServer at OpenGeo and GeoServer’s documentation. In particular, here are a list of big questions that I need to answer:

1. What are the optimal JVM options

2. Which application server shall I use to serve GeoServer, Tomcat or Glassfish?

3. Which source data format shall I use to feed GeoServer? Shall I stay with ArcSDE or shall I export the feature class to a shapefile?

Here is the verision of some applications that I used:

  • Tomcat 6.0.33
  • Glassfish 3.1
  • GeoServer 2.1.1
  • JRE 1.6
  • ArcSDE 9.3.1
  • Oracle 11g


To find answers, I need to do some testing and find out what set of configurations give me the fastest HTTP response when GeoServer handles WMS request. To measure the testing result, I found an excellent tool called JMeter. It basically issues customized HTTP request to specified web service and measures the response time. It can also do the testing for multiple-user scenario.

I started with JVM options. A benchmark was established with default JVM settings, Tomcat, ArcSDE as source data format, and single user scenario. Then I changed one parameter for JVM option at a time, measured the response time and compared the result against the benchmark.

After finding the optimal JVM options, I switch the source data format from ArcSDE to shapefile and continue to run the testing on single user scenario.

Then I switched to multiple-user scenario with 10 users sending requests at 1 second interval. In this case, GeoServer’s performance decreases dramatically for either shapefile or ArcSDE as source data format.

I also did similar test with Glassfish.

Here is my findings:

Optimal JVM options: -server -Xms1024m -Xmx1024m -XX:MaxPermSize=512m -XX:+UseParNewGC. Of course, these options depend on the hardware configuration of my machine

Tomcat vs. Glassfish

  • In single user case: Glassfish is faster than Tomcat
  • In multiple user case: Tomcat is faster than Glassfish but more volatile. On average, Tomcat responses faster. But comparing with Glassfish, a lot more of Tomcat’s responses are either faster or slower, making the response time less predictable in Tomcat.
  • With user number increases, response time increases too. But Glassfish’s response time increases more than Tomcat for the same number of user increase

ArcSDE/RDBMS vs. Shapefile

  • In general, shapefile is faster than ArcSDE/RDBMS. In single user scenario, shapefile is more than 10 times faster than ArcSDE. This is reasonable because when ArcSDE is involved there is overhead of connecting to database. Even if there is database connection pooling in Tomcat, the overhead is still very costly
  • In multiple user case: response time increase for ArcSDE is less than shapefile. This is reasonable as RDBMS is designed to handle concurrent users scenario
  • In my 10-concurrent user scenario, even shapefile’s performance decreased more dramatically than ArcSDE with user increase, it still perform about 10 times better than ArcSDE. But I think with more and more concurrent users (100, 1K even 10K), ArcSDE/RDBMS’s performance should eventually out beat shapefile

So, if I want stability in performance, fast response time, AND if I predict only very small number of concurrent users, I should go with Glassfish and shapefile. In the case of large number of concurrent users, it seems that the combination of Tomcat and ArcSDE/RDBMS  should lead to better performance.


Getting Rid of Orphan State Locks in SDE with Oracle

August 31, 2011 Leave a comment


User of SDE database with Oracle was doing editing on a featureclass on ArcMap when ArcMap suddenly crashed. After the crash, a state lock stayed in STATES_LOCKS table that prevented SDE database compress to state zero. When SDE database can’t be compressed to state zero, the edits that user made on states other than state zero would NOT be moved to business table after compress. Once this happens, most likely you will find that the user who made the edits can see the change on his feature class, SDE admin will get successful result running sdeversion -o compress, but the changes user made at his end just won’t show up on web map that consumes the feature class.


I ran into similar problem recently. There are several things you can do to fix this issue:

1. Check to make sure ALL your database users have been disconnected from ArcCatalog or ArcMap. You can first look at SDE_ID in STATE_LOCKS table. Then run SDEMON -o status -I users. This command will give you the users that is connecting to your SDE database. You should find an ID in the first column of the command result. Look for ID the same as SDE_ID in STATE_LOCKS table. This way you can tell which user (most likely the PC of your user) is giving you the trouble. You can run SDEMON -o kill to kill an inactive connection.

2. If you still see a state lock, that means most likely this is an orphan lock. DON’T try to use SQL to remove the state lock because using SQL to manipulate these tables have known to crash SDE database. Instead, run SDEMON -o shutdown to stop your SDE service and then SDEMON -o start to restart the service. After doing so, you might still see the state lock but once you issue sdeversion -o compress to compress the state tree, the orphan state lock will go away.

If you are as unfortunate as me, you might run into an issue where after stopping your SDE service, you can’t start it – SDEMON -o start just won’t response. In this case, ask your UNIX admin to find additional active process on the server that belongs to SDE user and kill it! In my case, a “oracle” process is still running and it is manually killed. After killing the process, SDEMON -o start works again.

Categories: Spatial Database Tags: ,

Unable to delete representations – Problem solved

What I wanted to do

Test Representation in ArcGIS

What I have

  • Oracle 11g
  • ArcSDE 9.3
  • ArcGIS for Desktop (according to recent ESRI product naming announcement)


Here is what happened:

I was learning how to use representation. So I created a representation on a feature class in my SDE database on Oracle. Once the representation is created, in ArcCatalog I looked into the attribute table of the feature class and saw two columns: ruleID and override. I right clicked on the columns and delete them. And then I went to the properties of the feature class and delete the representation from Representation tab.

Now annoying things happened: I restart ArcCatalog and open the properties of the feature class, navigate to Representation, nothing shows up in the list, which is normal because I created and then deleted the representation. But if I click new and create a new representation, the one that had been created and deleted would show up together with the new representation in the list. If I select the ghost representation and try to delete it, I get the Unable to delete representation notice. What’s worse is that this ghost now shows up in all OTHER feature classes representations tab and I can’t get rid of it! Even if I remove this feature class the ghost representation is still there.


I haven’t seen similar issues posted on the Internet but the solutions to  many representation issues involve do something about an Oracle system table named SDE.GDB_ExtentsionDatasets. So I looked into this table in Oracle and found the dead body of that representation ghost. After removing the record, the ghost went away!

Manually Register Oracle Spatial Tables with ArcSDE – Filling the missing pieces

ESRI has a technical article talks about how to manually register Oracle Spatial tables with ArcSDE. But even if your data meets all the criteria, there is no guarantee that the registration would be successful. In fact, after following this article and several instructions on the Internet, I found that you will need to do more than meeting these criteria to make successful registration.

What I wanted to do

Register Oracle Spatial table with ArcSDE

What I have

Oracle table with SDO_GEOMETRY column


  1. Meet all criteria listed in ESRI’s technical article
  2. You MUST build spatial index for the table. Without the spatial index, the registered table will not draw in ArcMap or ArcCatalog
  3. The following parameters MUST be specified in sdelayer -o register:  -t, -P and -x. Watch out for -x. The first two parameters of -x have to cover the minimum extent of your data. Otherwise, the registered table will not draw
Here is the sde command that I used to register Oracle Spatial table:
sdelayer -o register -u <User> -p <Password> -s <Database Server> -l test_table,SHAPE -e l -C UNQ_ID -t SDO_GEOMETRY -S “Test” -P HIGH -x -400,-400,1000000000,0.000000008983153