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