The requirement is to finish transactions in 3 seconds. But during nightly batch load the requirement is violated.
Database ASH, AWR, redo Log Miner, v$event_histogram snapshots, even app logs show transactions are completed in 3 seconds.
Where is the time wasted?
It is wasted in obtaining connection from java connection pool.
Some indications can be found v$event_histogram snapshots: “os thread startup” event pops out occasionally during the problem period. But do not expect it to pop out in any aggregated report. Application pool is not requesting a connection that often after all.
Another place is Oracle grid “user logon time (msec)” metric. That one is said to be “time the agent takes to make a connection to the database”. Meaning Oracle grid actually performs a connection test and measures the time. It goes up to 2 seconds in that particular case but well, connections do take time. Starting a process is like building a house. Even worse.
However, the best way to diagnose it is to understand how application actually works. App administrator guessed the reason right away after presented with the fact that neither oracle nor app logs show excessive time spent in the database.