IT09544: ONSTAT -G SES REPORTS SESSION POOL OPENTABLE MEMORY LEAK WHEN RUNNING QUERIES WHICH REQUIRE AUTO-INDEX PATH QUERY PLANS

APAR status

• Closed as program error.

Error description

• This problem appears to be new in 12.10.FC5 (as it did not
  reproduce in 12.10.FC4).
  
  Query plan for the query being run repeatedly is as follows
  (showing it's using auto-index path)
  
  select {+ ORDERED, AVOID_HASH(t2), AVOID_FULL(t2) } *
  from t1,t2
  where t1.c1 = 1 and t1.c2 = 5 and
  t1.c2 = t2.c2
  
  DIRECTIVES FOLLOWED:
  ORDERED
  AVOID_HASH ( t2 )
  AVOID_FULL ( t2 )
  DIRECTIVES NOT FOLLOWED:
  
  Estimated Cost: 7
  Estimated # of Rows Returned: 2
  
    1) jrenaut.t1: INDEX PATH
  
      (1) Index Name: jrenaut.t1_idx1
          Index Keys: c1 c2 c3   (Key-Only)  (Serial, fragments:
  ALL)
          Lower Index Filter: (jrenaut.t1.c2 = 5 AND jrenaut.t1.c1
  = 1 )
  
    2) jrenaut.t2: AUTOINDEX PATH
  
      (1) Index Name: (Auto Index)
          Index Keys: c2
          Lower Index Filter: jrenaut.t1.c2 = jrenaut.t2.c2
  NESTED LOOP JOIN
  
  Then if you use onstat -g ses <session id> to monitor the
  session's memory pool you will see the opentable size growing.
  
  Sample output:
  
  Memory pools    count 3
  name         class addr              totalsize  freesize
  #allocfrag #freefrag
  45           V     462ec040         15519744   11314272   3935
  7501
  45*O0        V     461b8040         4096       768        1
  1
  45_SORT_0    V     4618d040         69632      52096      87
  88
  
  name           free       used           name           free
  used
  overhead       0          9984           scb            0
  144
  opentable      0          4066232        filetable      0
  1232
  
  Then to distinguish this from other opentable session growth
  (like APAR IT03000) if you do onstat -g afr <session id> | grep
  opentab you will see an increasing amount of lines that look
  like this:
  
  4eec1630         1080       opentable    3669   rssupp.c:224
  4eec3630         1080       opentable    3669   rssupp.c:224
  4eec5630         1080       opentable    3669   rssupp.c:224
  4eec7630         1080       opentable    3669   rssupp.c:224
  4eec9630         1080       opentable    3669   rssupp.c:224
  4eecb630         1080       opentable    3669   rssupp.c:224
  4eecd630         1080       opentable    3669   rssupp.c:224
  4eecf630         1080       opentable    3669   rssupp.c:224
  4eed1630         1080       opentable    3669   rssupp.c:224
  4eed3630         1080       opentable    3669   rssupp.c:224
  4eed5630         1080       opentable    3669   rssupp.c:224
  4eed7630         1080       opentable    3669   rssupp.c:224
  4eed9630         1080       opentable    3669   rssupp.c:224
  4eedb630         1080       opentable    3669   rssupp.c:224
  4eedd630         1080       opentable    3669   rssupp.c:224
  4eedf630         1080       opentable    3669   rssupp.c:224
  4eee1630         1080       opentable    3669   rssupp.c:224
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * IDS.12.10.xC5 users                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Update to IBM Informix Server 12.10.xC6                      *
  ****************************************************************
  

Problem conclusion

• Problem Fixed In IBM Informix Server 12.10.xC6
  

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

• RA10 PSN

     UP

• RA10 PSY

     UP

• RB10 PSN

     UP

• RB10 PSY

     UP

• RB50 PSN

     UP

• RB50 PSY

     UP

• RB70 PSN

     UP

• RB70 PSY

     UP

• RC10 PSN

     UP

• RC10 PSY

     UP