FOR UPDATE... or not FOR UPDATE, that is the question

Have you considered the impact of an unnecesary FOR UPDATE on your queries?

I had this database which suddenly rised the redo log generation rate, running on archive log mode (like every production database must be) it become a nightmare, trying to backup logs every hour or planning a huge space increase for the archive log destination filesystem.

Fortunately we keep track of every development that is promoted to production; our records showed that near the date a recent interface was released, the database increased the redo log activity. Then we proceeded to disect the suspicious code, realizing that the programmer coded 4 nested loops (on 4GL, given we have BaaN) and every loop was coded using, a presumably unnecesary SELECT...FOR UPDATE.

Those findings pointed the way of my research, since on a previous review of the process, every intermediate result table was turned to NOLOGGING, without any reduction on redo log creation, the only place where logging was taking place was the UNDO tablespace, a behavior that cannot be turned off. But I've to verify this theory... and find a solution to the puzzle.

I knew where to look for the information needed: the Oracle Data Dictionary views, then I opened the Oracle Database Reference for 10gR2, which is the 'fortunate' release of our test environment, given that our production is running 9R2, both under HP-Unix and showing exactly the same behavior.

These views helped to find useful information, and finally pinpoint the issue:
V$FILESTAT : if the UNDO tablespace was under heavy IO, it may be reflected here.
DBA_DATA_FILE : I needed to translate the file number to something meaningful.
V$UNDOSTAT : provided a 7 day meassure window that allowed to correlate heavy redo log generation with high UNDO activity.
V$EVENT_HISTOGRAM : histograms for every wait event.
V$SESSTAT : timed statistics by user session.
V$SYSSTAT : timed sytem statistics.
V$STATNAME : name of every statistic.

First I tried the following query and the resulting set confirmed the theory: at the very first place was located the datafiles for the UNDO tablespace.

 SELECT b.file_name, a.phywrts, a.phyblkwrt, a.writetim
FROM v$filestat a, dba_data_files b
WHERE a.file# = b.file_id
ORDER BY a.phyblkwrt DESC

Then I built this test case to confirm if there was a difference between SELECT and SELECT..FOR UPDATE shown at redo log write.

SQL> create table test ( col1 varchar2(10) );

Table created

SQL> select sid from v$mystat where rownum = 1;
1609

1 row returned
SQL> select name,a.value
2    from v$sesstat a, v$sysstat b
3    where b.statistic#=a.statistic#
4    and b.name = 'redo size' and sid = 1609;

redo size 13112
1 row selected

SQL> insert into test values ('World');

SQL> commit

SQL> select name,a.value
2    from v$sesstat a, v$sysstat b
3    where b.statistic#=a.statistic#
4    and b.name = 'redo size' and sid = 1609;

redo size 13504
1 row selected
SQL> select * from test;

SQL> select name,a.value
2    from v$sesstat a, v$sysstat b
3    where b.statistic#=a.statistic#
4    and b.name = 'redo size' and sid = 1609;

redo size 13504
1 row selected

SQL> select * from test for update;

redo size 14248
1 row selected

You may see that after issuing a simple SELECT there wasn't an effect in redo size, that effect was present modifying the query with the FOR UPDATE clause.

Explanation

Seems that SELECT...FOR UPDATE creates a copy of the information retrieved, which is stored at the UNDO tablespace. The undo tablespace logging behavior cannot be changed, therefore when the interface program was executed, there was m * n * p * q writes to the undo tablespace and therefore to the redo logs.

Results

After sending our recommendation to development to change their code and use simple SELECTs, the redo generation rate droped from 4 Gb to 500 Mb per hour, which is a considerable reduction.

Subscribe to Oracle Database Disected by Email