IZ07497: SMP GRANULARITY OF 1 CAN DEGRADE PERFORMANCE.

APAR status

• Closed as program error.

Error description

• The optimizer is miscalculating its plan which causes the
  runtime to choose SMP Granularity of 1 . That means
  for each sub-agent running within a connection, it will read one
  index row at time. This can cause an index latch contention
  where each process before reading a key or set of keys need to
  latch the index and then read the row. This gets multiplied when
  similar queries are running concurrently
  and accessing the same index and intra_parallel is set.
  

Local fix

• Workaround: turn off intra_parallel
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: *
  * EE or EEE *
  ****************************************************************
  * PROBLEM DESCRIPTION: *
  * See Error Description. *
  ****************************************************************
  * RECOMMENDATION: *
  * Upgrade to DB2 Version 9.1 Fix Pack 3 or any subsequent fix *
  * pack or release. *
  ****************************************************************
  SMP GRANULARITY OF 1 CAN DEGRADE PERFORMANCE.
  

Problem conclusion

• The optimizer is miscalculating its plan which causes the
  runtime to choose SMP Granularity of 1 . That means for each
  sub-agent running within a connection, it will read oneindex
  row at time. This can cause an index latch contention where
  eachprocess before reading a key or set of keys need to latch
  the index and then read the row. This gets multiplied
  whensimilar queries are running concurrently and accessing the
  same index and intra_parallel is set.
  
  First fixed in DB2 Version 9.1 Fix Pack 3.
  

Temporary fix

Comments

APAR Information

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

  IY89768

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

Fix information

• Fixed component name

  DB2 UDB ESE SOL

• Fixed component ID

  5765F4102

Applicable component levels

• R910 PSY

     UP