Performance

One of my close frind told me, they had a problem in their Oracle database- a SELECT statement is quicker, but a DELETE sleeps. Infact he was pointing to a big issue, normally developers tends to forget, I don’t want to kill the curiosity, so I will say the problem later. Here is my test case, what was the issue and the solution.

So, I have created a simple test table – TEST – just copy from DBA_OBJECTS. Also, created a primary key on OBJECT_ID

Woops.. it took 10.92 seconds to delete a single row which was selected in just 0.01 seconds! More over it took 7 db block gets, 33238 consistent gets and 21769 physical reads – something is terribly wrong. What is wrong here, why is using so much time and resource to DELETE a single row, but the SELECT is within the expectations. Come on let us trace the session and what oracle is doing delete this single row.