I have this problem of trying to explain to some people they are wrong.
Here is the situation:
Biztalk app starts transactions on a oracle database.
first command: alter session set isolation level serializable
next: they (update) set a flag in a record they will be processing a record (they don't state which record or who is processing)
next: start reading records
next: they insert processed records
they do this with 15 sessions at once
they say they have connection pooling set up, but every time sessions are closed and reopened for a new loop
how would you explain to them that of the 45 minutes they need to process 200000 records
there is no point in tuning the 13 seconds oracle is busy with the reads and the inserts.
based on tkprofs results 13 seconds was the time spent by the oracle database, the rest was because of SQL*net wait client