Unload is taking a long time deleting MELD records

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

 

During the db maintenance / utility job ucybdbun, Unload is taking a very long time and there are a lot of messages in the log like this:

MELD table with long time critical db calls (calls over 1 second):
20210110/225444.169 - U00003524 UCUDB: ===> Time critical DB call!       OPC: 'EXEC' time: '1:743.946.000'
20210110/225444.169 - U00003525 UCUDB: ===> 'insert into DIVDB (DIVDB_PK) select Meld_Idnr from Meld where Meld_DeleteFlag = 1 and rownum <= 50'

which is showing that an insert statement into divdb takes longer than 1 second (took 1.7 seconds above)

Environment

Release : 12.2

Component : AUTOMATION ENGINE

Resolution

The way the unload for MELD works is:

1. find the top x rows where x is the ini setting, reorg_chunk_size (by default this is 1000) that fit the criteria to be deleted (date, etc...)
2. insert these x rows into divdb
3. delete from meld whatever is in divdb FOR THIS SESSION
4. commit
5. truncate divdb
6. repeat steps 1-6 as many times as needed

The suggestions to resolve these were to:

1) increase the reorg_chunk_size from 50 to something higher - in this case it was increased to 1000 and that resolved the issue with the MELD table, however later on with RH/RT record deletion this caused issues that are better resolved with a lower number - this needs to be tuned and we ended up at 250.

2) Have a DBA check the execution plan on the SQL statement that is taking so long:

'insert into DIVDB (DIVDB_PK) select Meld_Idnr from Meld where Meld_DeleteFlag = 1 and rownum <= 50'

and be sure it is optimized.

3) Have a DBA check the indexes on the MELD table and DIVDB table and be sure that they are not too fragmented - if they are, rebuild or reorg the indexes

Another aspect in this case was the volume of records to be delete from the MELD table - to see the number of meld records to still be deleted run:
select count(*) from meld where meld_deleteflag <> 0

Running this again later should show a lower number - this is how to tell that the meld table is continuing to unload/delete.