Sleeping for a random amount of time in a stored procedure
0
votes
2
answers
80
views
A vendor application we're using utilizes multiple _processes_ (not threads) spread across multiple computers, one for each processing core available. When asked for a batch of calculations, the application distributes the load between these engines, which then go to the database for additional details necessary to perform the computations.
As we add more cores -- and engines -- the simultaneous rush to the database causes a load spike so big, some engines start getting the
ORA-12170: TNS:Connect timeout occurred
error, which causes them to crash (I know, it shouldn't, but we cannot fix the vendor's code). For example, with the engine-count of 1386, about 300 engines dump cores, because the 16-core Oracle server cannot cope...
There is no official way to cause the application to _stagger_ these engine-initializations...
But the first thing each engine is doing is invoke the same stored procedure -- which updates the same table -- and I'm wondering, if I can alter it to perform the staggering: cause each engine to _sleep_ for random number of milliseconds.
What would the SQL snippet for this look like? Our servers run Oracle-19...
Asked by Mikhail T.
(166 rep)
Nov 19, 2024, 04:49 PM
Last activity: Jun 12, 2025, 08:24 PM
Last activity: Jun 12, 2025, 08:24 PM