Oracle – physical writes to an index

oracleperformance

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.

Best Answer

I would guess that there is a weekly maintenance job that is running on that one day a week, that is affecting something that is forcing extra reads of the index. Is your daily batch job always run long on the same day of the week each week? If so, look at the views dba_scheduler_jobs and dba_scheduler_schedules to see if any jobs are scheduled.

Now the bigger question is, is why not fix the UPDATE statement to include criteria to avoid updating rows where the value is already the correct value? IE:

update student set score = 100 where score != 100 -- and whatever other criteria

Also as a temporary fix to your "temporary" index, create it using the nologging clause in the index attributes part might help a little.

Related Question