IT37120: CONCURRENT QUERIES USING REVERSE SCAN OF INDEX WITH MANY DELETEDENTRIES MAY ENCOUNTER POOR PERFORMANCE

APAR status

• Closed as program error.

Error description

• When the execution of a query involves the scan of an index in
  the reverse direction some additional precautions are necessary
  to ensure that execution does not become deadlocked with other
  threads using the index in the forward direction. These
  precautions include a back-off and retry mechanism which may
  include the need to access many index pages. If there are large
  numbers of index pages containing only deleted entries then the
  number of pages needing to be accessed will increase which can
  increase the chances of triggering the retry mechanism.
  
  Here is an example query plan showing a reverse index scan:
  
  select max(id) as max_id
  from     tab1
  where    from_time + 10 units second < CURRENT
  
  Estimated Cost: 2
  Estimated # of Rows Returned: 1
  
    1) informix.tab1: INDEX PATH
  
          Filters: informix.tab1.from_time + interval(10)
  second(9) to second < CURRENT year to fraction(3)
  
      (1) Index Name: informix.itab1_x1
          Index Keys: id  (Reverse)  (Aggregate)  (Serial,
  fragments: ALL)
  
  Factors to consider to help avoid the problem include monitoring
  of btscanner activity to remove deleted entries from the index
  and creation of an index defined using a descending order on the
  column(s).
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of Informix Server prior to 12.10.xC16 and 14.10.xC7.  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Informix Server 12.10.xC16 or 14.10.xC7.         *
  ****************************************************************
  

Problem conclusion

• Fixed in Informix Server 12.10.xC16 and 14.10.xC7.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  INFORMIX SERVER

• Fixed component ID

  5725A3900

Applicable component levels