PM13927: Message and BOM rendering times out when message counts are high on Oracle

APAR status

• Closed as program error.

Error description

• Message and BOM rendering times out when message counts are high
  on Oracle
  
  One of the message table queries:
  
  SELECT * FROM (SELECT bf_ora_rows_initial.*, ROWNUM bf_ora_idx F
  ROM (SELECT bf_messages.bf_id, bf_messages.bf_stamp, bf_users.bf
  _name user_name, bf_messages.bf_severity, bf_message_translated.
  bf_translated_text translation FROM bf_messages INNER JOIN bf_me
  ssage_translated ON (bf_messages.bf_id = bf_message_translated.b
  f_message_id AND bf_message_translated.bf_language = 'en_US') LE
  FT OUTER JOIN bf_users ON bf_messages.bf_user_id = bf_users.bf_i
  d WHERE (bf_messages.bf_translated = 1 AND (bf_messages.bf_msgke
  y ?? 'PreferenceCreated' AND bf_messages.bf_msgkey ?? 'Preferenc
  eUpdated' AND bf_messages.bf_msgkey ?? 'PreferenceDeleted')) ORD
  ER BY bf_messages.bf_stamp DESC) bf_ora_rows_initial ) bf_ora_ro
  ws WHERE bf_ora_idx BETWEEN 1 AND 15;
  
  Here is an example output from my system showing the execution p
  lan in basic form:
  Operation
                                                   Object
  ----------------------------------------------------------------
  -------------------------------------------------------- -------
  -----------------------
  SELECT STATEMENT ()
   VIEW ()
    COUNT ()
     VIEW ()
      SORT (ORDER BY)
       NESTED LOOPS (OUTER)
        HASH JOIN ()
         TABLE ACCESS (FULL)
                                                   BF_MESSAGE_TRAN
  SLATED
         TABLE ACCESS (FULL)
                                                   BF_MESSAGES
        TABLE ACCESS (BY INDEX R
                                                   BF_USERS
         INDEX (UNIQUE SCAN)
                                                   PK_BF_USER
  
  There are two places where we see opportunity. The bf_messages a
  nd bf_message_translated tables are each being full table scanne
  d. There is also a hash join of these two tables which is incred
  ibly memory and I\O intensive in almost every case. Both of thes
  e need to change.
  
  After implmenting the following two options the execution plan c
  hanges dramatically:
  alter session set OPTIMIZER_INDEX_CACHING = 50;
  alter session set OPTIMIZER_INDEX_COST_ADJ = 5;
  
  Operation
                                                   Object
  ----------------------------------------------------------------
  -------------------------------------------------------- -------
  -----------------------
  SELECT STATEMENT ()
   VIEW ()
    COUNT ()
     VIEW ()
      NESTED LOOPS (OUTER)
       TABLE ACCESS (BY INDEX RO
                                                   BF_USERS
        INDEX (UNIQUE SCAN)
                                                   PK_BF_USER
       NESTED LOOPS ()
        TABLE ACCESS (BY INDEX R
                                                   BF_MESSAGES
         INDEX (FULL SCAN DESCEN
                                                   BF_STAMP_MESSAG
  ES
        TABLE ACCESS (BY INDEX R
                                                   BF_MESSAGE_TRAN
  SLATED
         BITMAP CONVERSION (TO R
          BITMAP AND ()
           BITMAP CONVERSION (FR
            INDEX (RANGE SCAN)
                                                   BF_MSGTRANS_MSG
  ID
           BITMAP CONVERSION (FR
            INDEX (RANGE SCAN)
                                                   BF_MSGTRANS_LAN
  G
  
  Now we are performing index level scans, and using a nested loop
   join. The index level scans take care of our first opportunity
  with the query. Nested loop joins are the preferred join method
  as they are far less memory intensive, and are considered the mo
  st scalable join type in databases today. Just those two options
   have now influenced the query to be optimal using the current s
  tructure that we have.
  
  Proposed fix:
  Ideal:
  The Oracle connection should be updated to set these two options
   in the Services layer, and the engine for each and every connec
  tion session.
  
  Less ideal:
  Update the documentation to contain the two options and recommen
  ded values. This option requires a customer to implement a chang
  e on the database which is generally difficult to attain.
  
  Workaround:
  Keep the message counts low by changing the AutoClean settings i
  n Admin ? System.
  
  Set the two database parameters:
  OPTIMIZER_INDEX_CACHING = 50
  OPTIMIZER_INDEX_COST_ADJ = 5
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Messages and BOM time out on Oracle (for all databases with
  extremely high message counts)
  

Problem conclusion

• Fixed indirectly by 7.1.1.5
  

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

  BUILD FORGE SE

• Fixed component ID

  5724S2706

Applicable component levels

• R711 PSN

     UP