SQL Updates, locks and multithreading
I am trying to do the following:
Updating 5M rows in multiple database writer thread using OCI. I have not set any Oracle parallel execution related parameters yet.
However, it is found that there is no performance improvement in doing this in multiple threads.
My questions:
1. Does Oracle lock the table during each update? I think it does and that is why there is no performance enhancement.
- If yes, is there any way to tell Oracle not to lock the table during updates. As it is, my threads will never update the same row in parallel.
2. Is the way out is to do the following?
- Partition the table
- Do an "ALTER SESSION ENABLE PARALLEL DML"
- Fire updates
3. Is there any OCI way of doing this?
Thanks in advance,
~Sri