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.
Solved! Go to Solution.
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.
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.
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.
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.
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.
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.