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 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...
Within a PL/SQL block (or within your procedure), use DBMS_RANDOM to generate a random value and then DBMS_SESSION.SLEEP to sleep for that many seconds:
DECLARE
seconds NUMBER := DBMS_RANDOM.VALUE(1, 20);
BEGIN
DBMS_SESSION.SLEEP( seconds );
END;
/