Updates causing lots of redo?
We have an issue where the log switches every 4 minutes on a 'test' database, i.e. not doing much, just a few tests. I have checked the log miner and discovered that an unsupported procedure was doing 41,000+ updates every 4 minutes:
SQL> @logmnr_results
OPERATION SEG_OWNER SEG_NAME COUNT(*)
---------------------------------------- -------------------- ---------------------------------------- ----------
INSERT AFIS TCP_NEXT_MESSAGE 2
START 13
COMMIT 901
INSERT AFIS TCP_MESSAGES 3
DELETE AFIS ITEM_TEXT 1
UNSUPPORTED LIMARES MESSAGE_OUT 10
UNSUPPORTED AFIS ISERV_SESSIONS 4
INSERT AFIS ITEM_TEXT 3
UNSUPPORTED LIMARES ALLOCATION_HOLD_MAP 41024
INTERNAL 52
DELETE AFIS TCP_MESSAGES 3
UNSUPPORTED SYS SEQ$ 2
DELETE LIMARES MESSAGE_OUT 10
On checking the procedure ALLOCATION_HOLD_MAP it updates all 864 columns table to null every 5 seconds. Apparently this needs to be done this way, so we can ensure the data is refreshed for new data! Anyway, I am assuming that these 41000+ entries in the log file are related to these updates (
OPERATION SEG_OWNER SEG_NAME COUNT(*)
---------------------------------------- -------------------- ---------------------------------------- ----------
INSERT AFIS TCP_NEXT_MESSAGE 2
START 13
COMMIT 901
INSERT AFIS TCP_MESSAGES 3
DELETE AFIS ITEM_TEXT 1
UNSUPPORTED LIMARES MESSAGE_OUT 10
UNSUPPORTED AFIS ISERV_SESSIONS 4
INSERT AFIS ITEM_TEXT 3
UNSUPPORTED LIMARES ALLOCATION_HOLD_MAP 41024
INTERNAL 52
DELETE AFIS TCP_MESSAGES 3
UNSUPPORTED SYS SEQ$ 2
DELETE LIMARES MESSAGE_OUT 10
On checking the procedure ALLOCATION_HOLD_MAP it updates all 864 columns table to null every 5 seconds. Apparently this needs to be done this way, so we can ensure the data is refreshed for new data! Anyway, I am assuming that these 41000+ entries in the log file are related to these updates (
0