One Statment One Connection
I am trying to delete millions of records from Oracle 11G using ODP.NET or DevArt. If I send in 100 delete statements I want them to all execute at the same time. But instead they run in order 1 by 1.
I tried using the conventional way open connection execute statement close connection each with their own thread. I sent in 200 statements in theory that should kill the server 100+% cpu usage. But instead I see only 8% usage and it takes hours to delete 1 million records.
So I disabled pooling thinking that letting Oracle decide when to reuse a connection was the issue. Same code open run close. Same result. I tried both ODP and DevArt same results. If I open multiple SQLPlus windows and run multiple statements I am able to push the CPU as expected.
We tested directly on the server running multiple statements in batch and we were able to push the CPU. So the server set up is correct. Recycle bin disabled, No Logging, Nothing to cause slow downs. So the issue is when connecting using .NET.
I can submit some sample code if it helps.
Tags for this Thread