In doing major cleanup, the reorg has some slowness around the SELECT for the AH with a statement that appears many times:
20211028/080931.899 - U00003525 UCUDB: ===> 'SELECT AH_Client, AH_OType, AH_SType, AH_Status, AH_Idnr, AH_OH_Idnr, AH_TimeStamp4, AH_ArchiveFlag FROM AH WHERE AH_Client = ? and AH_TimeStamp4 IS NOT NULL and AH_TimeStamp4 < ? and AH_DeleteFlag = 0 Order by AH_OH_Idnr, AH_TimeStamp4 DESC'
20211028/090016.558 - U00003524 UCUDB: ===> Time critical DB call! OPC: 'SLCT' time: '2257:476.522.999'
20211028/090016.558 - U00003525 UCUDB: ===> 'SELECT AH_Client, AH_OType, AH_SType, AH_Status, AH_Idnr, AH_OH_Idnr, AH_TimeStamp4, AH_ArchiveFlag FROM AH WHERE AH_Client = ? and AH_TimeStamp4 IS NOT NULL and AH_TimeStamp4 < ? and AH_DeleteFlag = 0 Order by AH_OH_Idnr, AH_TimeStamp4 DESC'
20211028/093104.264 - U00003524 UCUDB: ===> Time critical DB call! OPC: 'SLCT' time: '888:267.896.999'
Why is this being ran multiple times? The trace shows the same timestamp, and this is for the same client, so this is a lot of overhead to pull all the records back but then flag in chunks and repeat it. Is there anything that limits this or is it running a full table scan?
Release : 12.3
Component : AUTOMATION ENGINE
The statement is used to find a chunk of AH records to flag for deletion at a time. Within the ini file, this is limited by the alloc_size setting.
alloc_size acts as a rownum in this case and should not slow down the sql statement. As long as the execution plan is optimized and the indexes are being used, there should not be a full table scan. The following suggestions should be used:
alloc_size is what limits the amount of data read in each of the SQL statements. Here's a chart of the approximate amount or memory needed for different levels of alloc_size:
The upper limit of alloc_Size is 2147483647
alloc_Size
10000=5MB
200000=102MB
5000000=2,5 Gig
If you're going to adjust alloc_size, our suggestion is to go with a moderate increase and check the database performance again with the DBA.