topic Re: GP append on ST_GEOMETRY VEEERY slow on Oracle in Data Management Questions

GP append on ST_GEOMETRY VEEERY slow on Oracle

Anonymous User — Fri, 28 Jun 2013 14:09:03 GMT

Original User: Ameskamp

Hi,

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 11.2.0.3, 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

Re: GP append on ST_GEOMETRY VEEERY slow on Oracle

MartinAmeskamp — Fri, 28 Jun 2013 20:16:00 GMT

Here's some more information: Both databases should have plenty of memory (4.5G of buffer cache), and the bottleneck seems to be the target database that's doing a lot of things I don't understand on the insert. This is the tkprof digest of an SQL trace on the target DB, nearly all the time is spent in the insert statement:

INSERT INTO UTARC.FCL_B_BUILDING_A FCL_B_BUILDING_A (TYPE_ID,OBJ_ID,OLD_ID,
COL_IL_1,COL_LV_1,COL_IL_2,COL_LV_2,REFSCALE,REL_OID,ACLASS_ID,UTCLSUB_ID,
OLD_VALUE,STATUS_BASEMAP,BRANCH,ATT_STRING_1,ATT_STRING_2,ATT_STRING_3,
ATT_REAL_1,ATT_REAL_2,ATT_REAL_3,ATT_INTEGER_1,ATT_INTEGER_2,ATT_INTEGER_3,
SHAPE,GLOBALID,OBJECTID)
VALUES
( :a1, :a2, :a3, :a4, :a5, :a6, :a7, :a8, :a9, :a10, :a11, :a12, :a13, :a14,
:a15, :a16, :a17, :a18, :a19, :a20, :a21, :a22, :a23,SDE.ST_GEOMETRY(:st1,
:st2,:st3,:st4,:st5,:st6,:st7,:st8,:st9,:st10,:st11,:st12,:st13,:st14),
:a25, :a26)

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse       58      0.01       0.00          0          0          0           0
Execute     58      1.35       1.33          0     413659    496904          58
Fetch        0      0.00       0.00          0          0          0           0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total      116      1.37       1.33          0     413659     496904          58

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 45
Number of plan statistics captured: 3

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
         0          0          0 LOAD TABLE CONVENTIONAL (cr=7113 pr=0 pw=0 time=18604 us)

Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait Total Waited
----------------------------------------   Waited ---------- ------------
SQL*Net message to client                      58        0.00          0.00
SQL*Net message from client                    58        0.00          0.13
********************************************************************************

Now why the database should be doing 10,000 block accesses on a single insert, I don't konw. Anyone have any idea or any suggestions where ro look for further information?

Thanks again, Martin

Re: GP append on ST_GEOMETRY VEEERY slow on Oracle

Anonymous User — Fri, 28 Jun 2013 20:46:07 GMT

Original User: vangelo

This certainly isn't the usual behavior. I load 7-8 million row tables in minutes (using
ST_GEOMETRY) and a 'C' API loader application, so you probably want to contact
Tech Support to see what may be different in your installation.

- V

Re: GP append on ST_GEOMETRY VEEERY slow on Oracle

MartinAmeskamp — Mon, 01 Jul 2013 07:57:03 GMT

Right, that's what I'm doing right now. Besides, after a couple of hours, ArcCatalog (not ArcSOCP) crashes with an OutOfMemory Exception and 4,100 MB of virtual size.

Meanwhile, I found out that sdeexport/sdeimport work just fine (around 15 minutes on this sample FCL), so I'll use that and let tech support figure out what's going wrong here...

Martin