Reorg - Long running statement without row number - 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 ...

Products

• CA Automic One Automation

Issue/Introduction

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?

Environment

Release : 12.3

Component : AUTOMATION ENGINE

Resolution

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:

1. Update alloc_size to something larger - this will allow for more to be updated in one chunk (but may still take a long time)
   

   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.

2. Have your DBA be sure that the select ah_client...  statement is using the NK_AH_Archive and NK_AH_REORG indexes and is using the optimal execution plan
3. Check fragmentation on the A* tables and reorg/rebuild indexes as needed
4. Lower the amount of records being reorg'd so that less records overall need to be updated.