Optimization profiles and guidelines

knowledge center URL
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.perf.doc/doc/c0024522.html

selectivity clause

You can influence the optimizer by providing the expected selectivity using a selectivity clause. It can be useful in situations where you want to encourage (or discourage) DB2 to use certain indexes.

SELECT c1, c2, c3, FROM T1, T2, T3
        WHERE T1.x = T2.x AND
        T2.y=T3.y AND
        T1.x >= ? selectivity 0.00001 AND
        T2.y gt; ? selectivity 0.5 AND
        T3.z = ? selectivity 0.2 AND
        T3.w = ?

guideline clause

SELECT c1, c2, c3, FROM T1, T2, T3
        WHERE T1.x = T2.x AND
        T2.y=T3.y AND
        T1.x >= ? selectivity 0.00001 AND
        T2.y gt; ? selectivity 0.5 AND
        T3.z = ? selectivity 0.2 AND
        T3.w = ?
                /*
<OPTGUIDELINES>
     <HSJOIN>
        <HSJOIN>
           <TBSCAN   TABLE='T1' />
           <TBSCAN   TABLE='T2' />
        </HSJOIN>
        <IXSCAN  TABLE='T3' />
     </HSJOIN>   
   </OPTGUIDELINES>*/ ;

other example:
<OPTGUIDELINES><DEGREE VALUE=‘ANY’/></OPTGUIDELINES>

负面案例:

过低的selectivity 值导致优化器低估排序内存的分配，从而导致了排序溢出。
In this scenario, I found that:

• The optimizer chooses the same access plan for the two SQL. However, the estimated returned rows for the SQL that invokes the “selectivity” clause is very low, which is about 115120, but the truly returned row count is 4812590.
• The SQL invokes the “selectivity” clause runs for more than 5 min under 20 occurrence, however, the other one runs for 3 min under the same scenario.
• For the SQL that invokes the “selectivity” clause, the POOL_TEMP_DATA_L_READS is 413310 and the SORT_OVERFLOWS is 1. But for the other SQL it is zero.
• For the SQL that invokes the “selectivity” clause, the ESTIMATED_SORT_SHRHEAP_TOP is 6337, but for the other SQL it is 318072.

Base on all of above, I found that the optimizer underestimates the sort heap needed for the SQL that invokes the “selectivity” clause, so that the sort overflow occurs and performance is impacted.