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

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