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. <\/p>\n
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).<\/p>\n
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.<\/p>\n
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.<\/p>\n
\r\nSQL> declare\r\n 2 l_statement varchar2(32767) := 'create table t (c1 number primary key,';\r\n 3 begin\r\n 4 execute immediate 'drop table t';\r\n 5 for i in 2..50 loop\r\n 6 if i < 50 then\r\n 7 l_statement := l_statement || 'c' || i || ' varchar2(30),';\r\n 8 else\r\n 9 l_statement := l_statement || 'c' || i || ' varchar2(30))';\r\n 10 end if;\r\n 11 end loop;\r\n 12 execute immediate l_statement;\r\n 13 l_statement := 'insert into t select object_id,';\r\n 14 for i in 2..50 loop\r\n 15 if i < 50 then\r\n 16 l_statement := l_statement || ' object_name,';\r\n 17 else\r\n 18 l_statement := l_statement || ' object_name from dba_objects where object_id is not null';\r\n 19 end if;\r\n 20 end loop;\r\n 21 execute immediate l_statement;\r\n 22 for cur in (select * from t) loop\r\n 23 null;\r\n 24 end loop;\r\n 25 end;\r\n 26 \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n<\/pre>\nNotice 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.<\/p>\n
We then set up another block in a new session to update each row to its existing value.<\/p>\n
\r\nSQL> variable total_redo number\r\nSQL> declare\r\n 2 l_statement varchar2(32767) := 'update t set ';\r\n 3 l_variable_length number;\r\n 4 l_value varchar2(30);\r\n 5 begin\r\n 6 for i in 2..50 loop\r\n 7 if i < 50 then\r\n 8 l_statement := l_statement || 'c' || i || ' = c' || i || ',';\r\n 9 else\r\n 10 l_statement := l_statement || 'c' || i || ' = c' || i;\r\n 11 end if;\r\n 12 end loop;\r\n 13 execute immediate l_statement;\r\n 14 select value into :total_redo from v$mystat natural join v$statname where name = 'redo size';\r\n 15 for cur in (select * from t) loop\r\n 16 null;\r\n 17 end loop;\r\n 18 end;\r\n 19 \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> print total_redo\r\n\r\n TOTAL_REDO\r\n----------------\r\n 167,985,928\r\n\r\n<\/pre>\nWe 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.<\/p>\n
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.<\/p>\n
\r\nSQL> variable total_redo number\r\nSQL> declare\r\n 2 l_statement varchar2(32767) := 'update t set ';\r\n 3 l_variable_length number;\r\n 4 l_value varchar2(30);\r\n 5 begin\r\n 6 select avg(length(object_name))\r\n 7 into l_variable_length\r\n 8 from dba_objects;\r\n 9 l_value := rpad('x',l_variable_length,'x');\r\n 10 for i in 2..50 loop\r\n 11 if i < 50 then\r\n 12 l_statement := l_statement || 'c' || i || ' = ''' || l_value || ''',';\r\n 13 else\r\n 14 l_statement := l_statement || 'c' || i || ' = ''' || l_value || '''';\r\n 15 end if;\r\n 16 end loop;\r\n 17 execute immediate l_statement;\r\n 18 select value into :total_redo from v$mystat natural join v$statname where name = 'redo size';\r\n 19 for cur in (select * from t) loop\r\n 20 null;\r\n 21 end loop;\r\n 22 end;\r\n 23 \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> print total_redo\r\n\r\n TOTAL_REDO\r\n----------------\r\n 215,670,580\r\n\r\n<\/pre>\nAs 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.<\/p>\n
I checked, and we don't have any supplemental logging enabled. The following are some values from v$database:<\/p>\n
\r\n\r\nCREATED 2008-02-08 16:22:31\r\nRESETLOGS_CHANGE# 564488\r\nRESETLOGS_TIME 2008-02-08 16:22:33\r\nPRIOR_RESETLOGS_CHANGE# 1\r\nPRIOR_RESETLOGS_TIME 2006-12-19 04:16:53\r\nLOG_MODE ARCHIVELOG\r\nCHECKPOINT_CHANGE# 6728756987948\r\nARCHIVE_CHANGE# 6728756981942\r\nCONTROLFILE_TYPE CURRENT\r\nCONTROLFILE_CREATED 2008-02-08 16:22:31\r\nCONTROLFILE_SEQUENCE# 2057989\r\nCONTROLFILE_CHANGE# 6728756989329\r\nCONTROLFILE_TIME 2010-08-06 11:32:43\r\nOPEN_RESETLOGS NOT ALLOWED\r\nVERSION_TIME 2008-02-08 16:22:31\r\nOPEN_MODE READ WRITE\r\nPROTECTION_MODE MAXIMUM PERFORMANCE\r\nPROTECTION_LEVEL MAXIMUM PERFORMANCE\r\nREMOTE_ARCHIVE ENABLED\r\nACTIVATION# 1827432451\r\nSWITCHOVER# 1827432451\r\nDATABASE_ROLE PRIMARY\r\nARCHIVELOG_CHANGE# 6728756987948\r\nARCHIVELOG_COMPRESSION DISABLED\r\nSWITCHOVER_STATUS SESSIONS ACTIVE\r\nDATAGUARD_BROKER DISABLED\r\nGUARD_STATUS NONE\r\nSUPPLEMENTAL_LOG_DATA_MIN NO\r\nSUPPLEMENTAL_LOG_DATA_PK NO\r\nSUPPLEMENTAL_LOG_DATA_UI NO\r\nFORCE_LOGGING NO\r\nPLATFORM_ID 13\r\nPLATFORM_NAME Linux 64-bit for AMD\r\nRECOVERY_TARGET_INCARNATION# 2\r\nLAST_OPEN_INCARNATION# 2\r\nCURRENT_SCN 6728756997178\r\nFLASHBACK_ON NO\r\nSUPPLEMENTAL_LOG_DATA_FK NO\r\nSUPPLEMENTAL_LOG_DATA_ALL NO\r\nSTANDBY_BECAME_PRIMARY_SCN 0\r\nFS_FAILOVER_STATUS DISABLED\r\nFS_FAILOVER_CURRENT_TARGET None\r\nFS_FAILOVER_THRESHOLD 0\r\nFS_FAILOVER_OBSERVER_PRESENT None\r\nFS_FAILOVER_OBSERVER_HOST None\r\n\r\n<\/pre>\nOur next post will analyze the redo itself by dumping the logfile to see if we can determine the size of each change.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n