Article Original Creation Date: 2010-10-27
Overview
--JAVA CLEAREMPTYUNDO 100000 -1
They would like the tool to commit after 100.000 records and go on until all UNDO records have been removed, but the tool gives these errors:
CLEAREMPTYUNDO: chunkSize cannot exceed 1000, resetting it to 1000
CLEAREMPTYUNDO: maxRecords cannot exceed chunkSize, resetting it to chunkSize
CLEAREMPTYUNDO: chunkSize = 1000, maxRecords = 1000
CLEAREMPTYUNDO: Loaded 1000 records...processing
The customer has more than 9 million records so the procedure will last for some days.
Environment
Root Cause
Resolution
New runSQL.class file with extra options:
I have updated runSQL to issue one SELECT statement, retrieving all the records to be processed (either as specified in the command, or all records if -1 is specified). This still uses the following SQL statement to select the records to process:
If maxRecords == -1:
SELECT dbor.undo_dbor_conf_guid
FROM sprt_sg_dbor_tc dbor, sprt_sg_tc tc
WHERE dbor.sg_tc_guid = tc.guid
AND dbor.undo_dbor_conf_guid IS NOT NULL
AND tc.undo_tc_instance_guid IS NULL;
If maxRecords != -1:
SELECT dbor.undo_dbor_conf_guid
FROM sprt_sg_dbor_tc dbor, sprt_sg_tc tc
WHERE dbor.sg_tc_guid = tc.guid
AND dbor.undo_dbor_conf_guid IS NOT NULL
AND tc.undo_tc_instance_guid IS NULL
AND rownum <= maxRecords;
I have also added a new command, called CLEAREMPTYUNDO2, which allows you to specify the SELECT statement for selecting the records to process. The syntax for this new command is:
--JAVA CLEAREMPTYUNDO2
where:
is the number of records to process before performing a commit
Both CLEAREMPTYUNDO and CLEAREMPTYUNDO2 before a single select statement to retrieve the GUIDs of all the records to process, then process this list 1000 records at a time, committing the transaction once records have been processed. Two queries are then issued to clear the empty UNDO data. First, we set SPRT_SG_DBOR_TC.UNDO_DBOR_CONF_GUID to NULL using the following query:
UPDATE sprt_sg_dbor_tc SET undo_dbor_conf_guid = NULL WHERE undo_dbor_conf_guid IN ();
Then we delete the record from SPRT_SG_DBOR_CONFCODE using the following query:
DELETE FROM sprt_sg_dbor_confcode WHERE guid IN ();
The select statement for the CLEAREMPTYUNDO command has not changed. The one I show below is the same one that was there previously. The difference is that rather than executing that query for 1000 records at a time (so for 100,000 records, that query would have been executed 100 times), runSQL now only executes it once.
1. 3,4 million UNDO ACTIVE records would be kept in the table. Reading the UNDO blob only 35% of times is much better than reading it 100% of times. By the way, as we shared that these records would have been deleted, we ask you to verify if that’s still possible.
The recommendation is to NOT remove UNDO ACTIVE DBOR records that have associated UNDO template configurations. The initial query does not consider whether a DBOR record had an associated UNDO configuration. The subsequent query does consider this and therefore the number of records returned is substantially reduced.
2. More important: during conference calls we shared that only UNDO ACTIVE records can be deleted. Using the statement you provided, we will delete records in all states, in this moment there are about 9 thousand records in states different from ACTIVE. See below. Is that a problem?
The recommendation is to NOT remove DBOR records with a state (status) other than ACTIVE.
select dbor.status,
count(*)
FROM sprt_sg_dbor_tc dbor, sprt_sg_tc tc
WHERE dbor.sg_tc_guid = tc.guid
AND dbor.undo_dbor_conf_guid IS NOT NULL
AND tc.undo_tc_instance_guid IS NULL
AND dbor.status = 'ACTIVE'
group by status;
STATUS COUNT(*)
-------------------------------- ----------
ACTIVE 6306261