Problem: Function call typically takes 2-3 millisecond but at times exceeding 2-3 Minutes.
=====================================================
From the DB logs it seems when multiple processes are trying to execute the function,
execution takes sequentially rather than parallel, which means Nth thread will have to wait for (N-1)*ExecutionTime before getting its turn
Is my observation correct? If yes then what is the solution for this? If not where/how to find the exact cause of the above problem?
=====================================================
DB Version: 8.2
Function Details:
--returns numeric, takes 10 parameters
--select query to validate data
--row level lock for select and validate
--bare minimum business logic
--update data
--couple of inserts for transaction logs/account management
--also note that few of the tables have audit triggers causing the row to be inserted in audit table with the action (only Update/Insert/Delete)