AnsweredAssumed Answered

GP append on ST_GEOMETRY VEEERY slow on Oracle

Question asked by geonetadmin on Jun 28, 2013
Latest reply on Jul 1, 2013 by ameskamp
Original User: Ameskamp


we're trying to copy the contents of one largish (~50 GB) Oracle geodatabase into an empty identical schema using the GP-tool append for each feature class or table. We find that append takes excessively long on large feature classes.

Hardware: New PC with WS 2008 R2, Oracle, ArcSDE 10.0 SP5, ArcGIS Desktop 10.0 SP5. CPU is Intel i5-3570, 20 GB RAM, both databases sit on a fast SSD (Samsung SSD 840 PRO). The whole operation takes place on this machine, while it does, there is no other significant activity.

Schema situation: The source FCL (ST_GEOMETRY, polygon features) has around 2 million features and takes up 42,000 16k blocks (~650 MB). Basically, FCLs are versioned, but versioning has been removed for data transfer. The target FCL is defined identically, it is not versioned, doesn't have any class extension nor indexes (neither spatial nor attribute). Spatial reference systems are identical, so there's no projection taking place, both are high resolution.

On the source DB, the resulting SQL statement is a simple select [all attributes] from FCL, resulting in a full table scan - which is exactly what I expected. The problem is, that this operation, i.e. the append, takes 7 hours.

As an alternative, I've created a clone of the target FCL on the target DB (using import for spatial reference and attributes), but this time using SDE LOB. The append from the st_geometry source to the SDE LOB took 11 minutes! During both appends, the ArcSOCP process used an average of 17% CPU time (100% being the 4 Cores of the i5-CPU).

What's going on here? Has anyone observed similar behaviour?

Thanks, Martin