Dear Forum Members,
We are seeing the unexpected performance bottle neck while executing the update statement on one of our table.
Some time the update statement is getting stuck without completing even after several hours in our 11.2 version of oracle production database server .
The update statement usually takes less than 1 minutes to update the same amount of data but for the same amount of data the update statement is getting stuck without completion.
The table is having approximately 10 million data , also we have a partition on the our key column run id. so the each run id will have approximately 1 million records so we will be updating around 0.1 million records with the update statement.
The same table we are updating in two places in the packages procedure, but some time it’s getting stuck at either one of the update statement. We have tried modifying the update statement as merge statement and also update with the FOR ALL bulk update but we are having same issue .
We have temporary solution to resolve this issue by creating the backup table of the table we are updating using create table_backup as select * from table to store the data from the main table, truncating the main table and inserting the data back to the table.
After rebuilding the table by truncating and reloading it back , both of our update statements are completing in a minute.
Please advise me what could be the reason behind this kind of unstable behavior of the update on this particular table. Have you seen this kind of issue in your oracle environment.
Please provide your valuable suggestions/advise to resolve this issue permanently.
We have taken DBA's help during the execution of the update statement to check is there are any locks but there are no locks at this time, also we have given DBAs to execute the update statement and it got completed in less than a minute but getting stuck if we execute it from the package procedure (Not always).
FYI - The packaged procedure having update statement is called from the data-stage job.
Thanks in advance.