Addressing OPTIMIZE FOR 1 ROW Performance Regression

Many DBAs and developers use OPTIMIZE FOR 1 ROW in their SQL statements as a hint to the DB2 optimiser, to discourage the use of sort or list prefetch within the access path where only a small number of rows are expected to be returned. Some sites even include this clause as part of their SQL standards in some situations.

Unfortunately, DB2 10 for z/OS introduced a change for statements which use OPTIMIZE FOR 1 ROW which can lead to performance regression under some circumstances. Prior to this change, OPTIMIZE FOR 1 ROW would discourage the use of sort based access paths but they would still be considered if the optimiser determined the query cost would be significantly lower. DB2 10 changed this behaviour, so if there is a choice between “sort based” access paths and “non-sort based” alternatives DB2 10 eliminates all of the “sort based” ones before selecting the cheapest of the others. Where multiple candidate indexes exist, this may result in DB2 selecting an index with lower MATCHCOLS simply to avoid a sort, which may in turn result in poorer query performance.

APAR PM56845 has been created to address this issue, and will provide a new DSNZPARM to revert to the old access path algorithm. Unfortunately this APAR is still open at the time of writing, so if you are feeling pain from this issue today the best way to address it is either to change the SQL statement to use OPTIMIZE FOR 2 ROWS or create/amend indexes to support both sort avoidance and index matching.