SDE Taking Too Long To Load Data

2943
18
Jump to solution
07-11-2016 05:02 AM
MarkMcCart
Occasional Contributor

We are loading large amounts of data (1,000,000+ records) into a single feature classes into our Oracle 12c ArcSDE 10.4 instance. However, it is taking way too long to load that data. For example, I tried to load 550,000 records into a feature class using the Simple Data Loader in ArcCatalog on Sunday starting at 10:00 am, by 7:00 pm, it still wasn't done loading. I let it run all night, and it finally loaded. Our test environment does not have this same issue. Loading data into our test environment, with less resources, takes just a few minutes.

Where can I start to troubleshoot this issue? Is it in Oracle, SDE set up? We have REST services being served out by these feature classes and tables in SDE, could that be an trouble area?

Any help would be appreciated.

Thanks,

Mark

Tags (2)
18 Replies
PanagiotisPapadopoulos
Esri Regular Contributor

Also check if you have any network issues between the Database and the place the data coming.

If it is possible place the input data on the Database server.

Reply
0 Kudos
MarkMcCart
Occasional Contributor

So we changed the ATTRBUFSIZE from 50,000 to 100,000 to match what we had on Test. It is still taking quite a while to load data. We're monitoring the data load in Toad and noticed once it loaded 56,000 records, it took about 3-4 minutes before it loaded any more.

Reply
0 Kudos
MarkMcCart
Occasional Contributor

How can use the old 'Load-only' loading option in 10.4 now that SDE command line is gone?

VinceAngelo
Esri Esteemed Contributor

Dropping the spatial index has the same effect.

Reply
0 Kudos
MarkMcCart
Occasional Contributor

When monitoring the data load in Toad, we noticed that Test and Prod are running different commands. Production is calling this statement a lot: SELECT table_name, time_last_modified FROM SDE.sde_tables_modified

Test does not make this call.

Reply
0 Kudos
TerryIffland
Occasional Contributor

Are all the patches applied to the Production database as those in Test? I recall something happening like this with 10.1 where a query was running before an insert.  It created a ton of overhead.  I believe there was a patch from ESRI to resolve the problem in 10.1. May be the same issue in 10.4 or in the Oracle database.

Reply
0 Kudos
George_Thompson
Esri Frequent Contributor

Hi Mark,

I think that it might be a good idea to open a new support case with Esri Technical Support​ and have the Geodatabase team investigate. There are a lot of different factors that go into the load times. They may be able to shed some more light on this issue.

--- George T.
Reply
0 Kudos
MarkMcCart
Occasional Contributor

After some testing and looking at our trace files. We determined that a change had to be made to our sqlnet.ora file in our Oracle client. We added the line 'DEFAULT_SDE_SIZE= 512'. Once that change was made, data loaded much faster. Thanks for everyone's advice to help us solve this include Jim at Esri.

-Mark

View solution in original post

deleted-user-UxcAu2PHrDQp
Occasional Contributor

Look for high Oracle Client wait times in the database logs or trace files from the client.  This is an indicator of needing to set the DEFAULT_SDE_SIZE or look into client or network resources.  We decided not to make a global database change for this parameter but let the clients define this in their own TNSNAME or SQLNET files. 

Reply
0 Kudos