I recently spoke with an Oracle DBA. He mentioned they had a third party application that was generating gobs of redo by updating rows to their existing values, i.e., update t set c = c.
This bothered me, as my understanding was that other than recursive SQL such as extent allocation, block cleanout, or undo segment management, redo should be very little when columns are updated to their existing value(s).
I decided to set up a test case so I could at least reproduce it and drill into it more. This is the first part of the analysis.
We start by creating a 50 column table comprised of one numeric PK, and 49 columns of varchar2(30) datatype. We then load the table with object names from dba_objects.
SQL> declare
2 l_statement varchar2(32767) := 'create table t (c1 number primary key,';
3 begin
4 execute immediate 'drop table t';
5 for i in 2..50 loop
6 if i < 50 then
7 l_statement := l_statement || 'c' || i || ' varchar2(30),';
8 else
9 l_statement := l_statement || 'c' || i || ' varchar2(30))';
10 end if;
11 end loop;
12 execute immediate l_statement;
13 l_statement := 'insert into t select object_id,';
14 for i in 2..50 loop
15 if i < 50 then
16 l_statement := l_statement || ' object_name,';
17 else
18 l_statement := l_statement || ' object_name from dba_objects where object_id is not null';
19 end if;
20 end loop;
21 execute immediate l_statement;
22 for cur in (select * from t) loop
23 null;
24 end loop;
25 end;
26 /
PL/SQL procedure successfully completed.
Notice that at the end of the block above, we also scan the table to clean out all the blocks. Finally, we log out so each test is performed from a separate session.
We then set up another block in a new session to update each row to its existing value.
SQL> variable total_redo number
SQL> declare
2 l_statement varchar2(32767) := 'update t set ';
3 l_variable_length number;
4 l_value varchar2(30);
5 begin
6 for i in 2..50 loop
7 if i < 50 then
8 l_statement := l_statement || 'c' || i || ' = c' || i || ',';
9 else
10 l_statement := l_statement || 'c' || i || ' = c' || i;
11 end if;
12 end loop;
13 execute immediate l_statement;
14 select value into :total_redo from v$mystat natural join v$statname where name = 'redo size';
15 for cur in (select * from t) loop
16 null;
17 end loop;
18 end;
19 /
PL/SQL procedure successfully completed.
SQL> print total_redo
TOTAL_REDO
----------------
167,985,928
We see that for about 59,000 rows, we generated about 160MB of redo. Once again, we scan the table to ensure all blocks are cleaned out. We do this after we determine our redo so the cleanout itself does not affect our analysis.
One again, in a new session, we then update our rows to a new changed value. We also ensure our rows are about the same size as the initial rows. We do this to ensure the size of the data doesn't impact our analysis.
SQL> variable total_redo number
SQL> declare
2 l_statement varchar2(32767) := 'update t set ';
3 l_variable_length number;
4 l_value varchar2(30);
5 begin
6 select avg(length(object_name))
7 into l_variable_length
8 from dba_objects;
9 l_value := rpad('x',l_variable_length,'x');
10 for i in 2..50 loop
11 if i < 50 then
12 l_statement := l_statement || 'c' || i || ' = ''' || l_value || ''',';
13 else
14 l_statement := l_statement || 'c' || i || ' = ''' || l_value || '''';
15 end if;
16 end loop;
17 execute immediate l_statement;
18 select value into :total_redo from v$mystat natural join v$statname where name = 'redo size';
19 for cur in (select * from t) loop
20 null;
21 end loop;
22 end;
23 /
PL/SQL procedure successfully completed.
SQL> print total_redo
TOTAL_REDO
----------------
215,670,580
As you can see, we generated a little over 200MB for changed row values. I would have expected the disparity (only about 40MB) to be much larger than it was.
I checked, and we don't have any supplemental logging enabled. The following are some values from v$database:
CREATED 2008-02-08 16:22:31
RESETLOGS_CHANGE# 564488
RESETLOGS_TIME 2008-02-08 16:22:33
PRIOR_RESETLOGS_CHANGE# 1
PRIOR_RESETLOGS_TIME 2006-12-19 04:16:53
LOG_MODE ARCHIVELOG
CHECKPOINT_CHANGE# 6728756987948
ARCHIVE_CHANGE# 6728756981942
CONTROLFILE_TYPE CURRENT
CONTROLFILE_CREATED 2008-02-08 16:22:31
CONTROLFILE_SEQUENCE# 2057989
CONTROLFILE_CHANGE# 6728756989329
CONTROLFILE_TIME 2010-08-06 11:32:43
OPEN_RESETLOGS NOT ALLOWED
VERSION_TIME 2008-02-08 16:22:31
OPEN_MODE READ WRITE
PROTECTION_MODE MAXIMUM PERFORMANCE
PROTECTION_LEVEL MAXIMUM PERFORMANCE
REMOTE_ARCHIVE ENABLED
ACTIVATION# 1827432451
SWITCHOVER# 1827432451
DATABASE_ROLE PRIMARY
ARCHIVELOG_CHANGE# 6728756987948
ARCHIVELOG_COMPRESSION DISABLED
SWITCHOVER_STATUS SESSIONS ACTIVE
DATAGUARD_BROKER DISABLED
GUARD_STATUS NONE
SUPPLEMENTAL_LOG_DATA_MIN NO
SUPPLEMENTAL_LOG_DATA_PK NO
SUPPLEMENTAL_LOG_DATA_UI NO
FORCE_LOGGING NO
PLATFORM_ID 13
PLATFORM_NAME Linux 64-bit for AMD
RECOVERY_TARGET_INCARNATION# 2
LAST_OPEN_INCARNATION# 2
CURRENT_SCN 6728756997178
FLASHBACK_ON NO
SUPPLEMENTAL_LOG_DATA_FK NO
SUPPLEMENTAL_LOG_DATA_ALL NO
STANDBY_BECAME_PRIMARY_SCN 0
FS_FAILOVER_STATUS DISABLED
FS_FAILOVER_CURRENT_TARGET None
FS_FAILOVER_THRESHOLD 0
FS_FAILOVER_OBSERVER_PRESENT None
FS_FAILOVER_OBSERVER_HOST None
Our next post will analyze the redo itself by dumping the logfile to see if we can determine the size of each change.