I have a Oracle 126.96.36.199 database on Solaris. We are testing some new code. It grabs a list of transactions by account number, does some manipulation and inserts it into another table.
The process is flying, processing around 200 transactions per second, then it hits this one account number and goes down to 20 transactions per second. This account number contains 200,000 transactions whereas the rest of them have around 3,000 -4,000 each.
I am thinking that I should take a hit when the cursor is grabbing all those transactions and loading them into memory but once they are there I would think that I would start loading at 200 per second but that is not happening. Any ideas what I should check? The PGA_FREEABLE_MEM hits zero when this account comes up.
I would suggest looking at the logic of 'does some manipulation'. How will that logic react when there are a large # of transactions? It sounds like there is something in there that is attempting to do some sort of search or something repeatedly such that the processing speed scales inversely to the # of transactions.