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)
Release : 12.2
Component : AUTOMATION ENGINE
The way the unload for MELD works is:
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.