|
Problem IC83851
|
Status: Closed |
PERFORMANCE IMPROVEMENT TO HAVE BETTER PREFETCHER HANDLING ON A BUSY SYSTEM. |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| Problem description: |
This apar fix is for performance optimization to avoid holding
on to the prefetch queue latch for too long especially in an
environment where prefetchers are very busy.
e.g.
when backup is running
or
when there are a lot of short-lived temporary objects
created/dropped in the system.
db2pd -latches output will show the following latch waiters:
0x07000021352493F0 0 15936 Unknown
156 SQLO_LT_SQLB_pfQUEUE__queueLatch
0x070000213524AAB0 0 16450 Unknown
156 SQLO_LT_SQLB_pfQUEUE__queueLatch
0x070000213524D830 0 17478 Unknown
156 SQLO_LT_SQLB_pfQUEUE__queueLatch
0x070000213524AAB0 0 18506 Unknown
156 SQLO_LT_SQLB_pfQUEUE__queueLatch
0x07000021352493F0 0 21590 Unknown
520 SQLO_LT_SQLB_pfQUEUE__queueLatch
0x07000021352493F0 0 63225 Unknown
520 SQLO_LT_SQLB_pfQUEUE__queueLatch
0x070000213524D830 0 101776 Unknown
520 SQLO_LT_SQLB_pfQUEUE__queueLatch
0x07000021352493F0 0 144439 Unknown
520 SQLO_LT_SQLB_pfQUEUE__queueLatch
0x07000021352493F0 0 434082 Unknown
520 SQLO_LT_SQLB_pfQUEUE__queueLatch
0x070000213524AAB0 0 198953 Unknown
520 SQLO_LT_SQLB_pfQUEUE__queueLatch
0x070000213524D830 0 237984 Unknown
520 SQLO_LT_SQLB_pfQUEUE__queueLatch
0x070000213524AAB0 0 279333 Unknown
520 SQLO_LT_SQLB_pfQUEUE__queueLatch
0x070000213524D830 0 287547 Unknown
520 SQLO_LT_SQLB_pfQUEUE__queueLatch
0x070000213524D830 0 346050 Unknown
520 SQLO_LT_SQLB_pfQUEUE__queueLatch
The callstacks might show the following trend for db2
prefetchers:
0x090000000DAB9384 sqloXlatchConflict + 0x284
0x090000000DAB8DC0 sqloXlatchConflict@glue1A5@clone1 + 0x78
0x090000000DB447D0
@72@sqlbpfAddToThisQ__FP12SQLB_pfQUEUEP9SQLB_BPCBPP14SQLB_pfRequ
estP12SQLB_GLOBALS + 0xD0
0x090000000DB44668
sqlbpfAddToQ__FP12SQLB_GLOBALSPP14SQLB_pfRequest + 0x314
0x090000000DA74E6C
sqlbpfAddToQ__FP12SQLB_GLOBALSPP14SQLB_pfRequest@glue53C + 0x88
0x090000000AED61EC
sqldmPrefetchList__FP8sqeAgentP8SQLD_CCBPP14SQLB_pfRequesti +
0x3B4
0x090000000DA73024 sqlriPrefetchRIDs__FP8sqlrr_cbP8sqlri_lfl +
0x304
0x090000000DA72C5C
sqlriPrefetchRIDs__FP8sqlrr_cbP8sqlri_lfl@glue14B6 + 0x74
0x090000000DA72A6C sqlriListFetch__FP8sqlrr_cb + 0x234
0x090000000DB05FCC sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm
+ 0x62C
0x090000000D9F7BA4
sqlrr_process_fetch_request__FP14db2UCinterface + 0x190
0x090000000DB5F748
sqlrr_fetch__FP14db2UCinterfaceP15db2UCCursorInfo + 0x400
0x090000000BE163D4
csmDriveFetch__FP14db2UCinterfaceP15db2UCCursorInfobT3 + 0x528
0x090000000BE14FC0
csmFetch__FP14db2UCinterfaceP15db2UCCursorInfo + 0x680
0x090000000BE0FFE4
fetchCursor__10pvmPackageFP14db2UCinterfaceCUi + 0xD0
0x090000000BE0C6B8 executeSection__10pvmPackageFP5sqlcaUib +
0x740
0x090000000BDF1914 executeQuery__3PVMFUib + 0x1D8
0x090000000BDF7AFC fetchCursorVariable__3PVMFCUiN31 + 0x3F0
0x090000000BDF093C run__3PVMFv + 0x854
0x090000000BDEDDF8 pvm_entry + 0x20C
or
0x090000000DAB9384 sqloXlatchConflict + 0x284
0x090000000DAB903C sqloXlatchConflict@glue1A5 + 0x78
0x090000000DB42400
sqlbpfRemoveFromQ__FP12SQLB_pfQUEUEUlPP14SQLB_pfRequestP16sqeLoc
alDatabasePP12SQLD_OBJ_HDLP12SQLB_GLOBALS + 0x20
0x090000000DB42A30
sqlbPFPrefetcherEntryPoint__FP16sqbPrefetcherEdu + 0x2B4
0x090000000DC8BB4C RunEDU__16sqbPrefetcherEduFv + 0x68
0x090000000DC891D8 EDUDriver__9sqzEDUObjFv + 0x1C0
0x090000000DC8FAAC sqloEDUEntry + 0x260 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.5 FP10 or higher *
**************************************************************** |
| Local Fix: |
No Local Fix |
| Solution |
Problem Fixed in DB2 Version 9.5 Fix Pack 10 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 30.05.2012
19.02.2013
19.02.2013 |
| Problem solved at the following versions (IBM BugInfos) |
9.5.FP10
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.5.0.10
|
 |
