I have an Access front-end application and an Oracle back-end set up. The user interface is written in MS Access (Access 2000 format) and all the data is stored within Oracle.
This has worked fantastically over the years and started out as a system based on Win2000 and Access 2000. We're now on Win7 and Access 2010.
We are migrating the back-end to Oracle 11g, previously this was 9i. On the original 9i system, Access 2010 app runs a delete SQL in certain scenarios to remove upwards of 600 records of temporary data within a short span. Average is around 30 seconds.
Now however when I test the new 11g instance it takes an extremely long time. This is the case even for a short number - such as 6 - taking four times as long on 11g compared with 9i. I recorded nearly 9 minutes on the delete for the larger set before killing Access.
Looking at the Oracle logs it appears that there is a loop running of a select followed by a single record delete until all the relevant records have been removed. The code within Access is only sending one delete SQL command and it is not run within a loop.