Hi Tamil, I don't think I can help.
AFAICS, it's stuffed by the one-row-at-a-time approach. The SQL in statspack would seem to show that much of the db sequential read wait time is due to the "does this row exist?" part of the logic.
On top of that, I'm worried by the queries like.
"SELECT MAX(ROLE_ID) FROM SEP_ROLES WHERE DSRC_ACCT_ID = :1"
That smells like disaster for concurrent updates.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
Reply With Quote