inserts/updates sporadically take 10min-2hrs instead of normal run in secs
Basic database / table info:*
Oracle DB Version= Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Table in question= Custom table in custom schema, <10k records, no custom indexes, no triggers, 56 columns, LOGGING=YES
Issue_
We have a 3rd party java application that does sql inserts / updates / selects against the described custom table via custom procedure calls. Under normal circumstances, the sql inserts / update / selects complete successfully in mere seconds. However, seemingly sporadically ( at a high level ), the inserts / updates will take > 10 minutes to complete. The 3rd party java application currently has a time-out value set at 10 minutes for the given insert / update. The insert / update takes > 10 minutes, the application then times out and the insert / update never occurs. The application has sufficient error handling and it proceeds to try and redo the insert / update. Eventually it will complete and when it does, it only takes seconds. However this cycle of 10 minutes of time-out can go on for 2 to 3 hours some times.
The insert / update is only performing 1 record insert / update. The custom procedure doing the inserts / updates are very basic and just make sql insert / update calls with basic error handling.
Two 3rd party java applications do write to same table at the same times. However, we have similar situations on similar custom tables and only 1 java application writes to that table.
Not to further complicate things, but just for more information, sometimes when we are executing a concurrent program via sql call in a custom procedure, we get the same timeout for a span of time and then it executes successfully in seconds per normal.
Issue seems related to database load, as we cannot replicate this issue on the DEV / QA databases ( which match prod version ). Any ideas?
Don't want to overload with information on first post. Please let me know of any further info that might be helpful.