We have a large UPDATE (affects about 200K records) on a huge table (80M records). Due to a coding mistake, the UPDATE updates the same set of rows (200K) all over again with the exact same value.
There is a temporary index (say,
TMP_INDX) on the column that the UPDATE updates. [This column is also used in the WHERE clause]. This index is "temporary" in the sense it is created at the beginning of the script and dropped at the end.
Now, the UPDATE runs as part of daily batch and is fast on all days except on one day of the week where it takes almost 10x more time than the rest of the days.
AWR shows there is a 1000x times increase in the physical writes to
TMP_INDX on the day the update takes lot of time. This is the only thing that seems to change in the AWR. Strangely, AWR shows "dbfile sequential read" to be the culprit instead of "physical writes" on the days this happens. The execution plan is still the same as the other days.
Given the above input, I am thinking the slow UPDATE might be the result of the increase in the physical writes to the index caused by the update to a column that is part of a composite index. However, as I mentioned earlier, the column is updated with the exact same value, in which case oracle seems to do redo optimization which will not update the index. I could not understand why this would fail on one particular day of the week. Can somebody throw some light into this? Thanks.