DB2 - Problem description
Problem IT31248 | Status: Closed |
Under high concurrency, performance of queries using TEMP might be affected due to SQLO_LT_SQLB_PTBL__pool_table_latch latch | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
On systems with a high concurrency of the queries using TEMP operator (typically observed with SORT or CTEs) and where there are more than one system temp tablespace for given page size a performance degradation might be observed. System will report: - increased TOTAL_EXTENDED_LATCH_WAIT_TIME - waits on SQLO_LT_SQLB_PTBL__pool_table_latch in MON_GET_EXTENDED_LATCH_WAIT - db2pd -latches showing EDUs waiting on SQLO_LT_SQLB_PTBL__pool_table_latch The response time of tablespace monitoring interfaces including MON_GET_TABLESPACE and administrative views based on it will be also affected. A backtrace from Db2 engine collected during the slowness will show threads with sqlbGetSysTempDatapool function on the top of the stack, e.g: SQLO_SLATCH_CAS64::getConflict sqlbGetSysTempDatapool sqldInternalCreateTable sqldTableCreateTemp | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * All Users on DB2 v11.1 * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to product DB2 v11.5. * **************************************************************** | |
Local Fix: | |
As a temporary workaround to this issue, drop all system managed temp tablespaces of the same page size leaving just one. | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * All Users on DB2 v11.1 * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to product DB2 v11.5. * **************************************************************** | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 11.12.2019 18.02.2021 18.02.2021 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |