5 SQL tuning Strategies - DB2

Tuning Strategies:

 

Ø       Tuning Strategy 1:

ü       Adjust the COLCARD, FIRSTKEYCARD and FULLKEYCARD columns to an artificially high value.

Reason:

ü       In order to influence DB2 to use and index

ü       As cardinality increases, filter factor decreases. As filter factor decreases, the chance that DB2 will use an available index becomes greater.

 

Ø       Tuning Strategy 2:

ü       Adjust the CLUSTERRATIO to a high value between 96 and 100, preferably 100.

Reason:

ü       To enhance the probability of DB2 to use an unclustered index.

ü       As CLUSTERRATIO increases, DB2’s reluctance to use the unclustered indices from access path selection is removed.

 

Ø       Tuning Strategy 3:

ü       Adjust the cardinality CARD to an artificially high value

Reason:

ü       To influence DB2 to change the order in which tables are joined.

ü       DB2 uses the table having more no of rows as the Outer table in a join.

 

Ø       Tuning Strategy 4:

ü       Decrease the Column Cardinality value (COLCARD or COLCARDF)

Reason:

ü       Favours DB2 to use nested loop join which is efficient over merge scan join

 

Ø       Tuning Strategy 5:

ü       Altering the HIGH2KEY and LOW2KEY values to more accurately reflect the overall range of values stored in the column.

Reason:

ü       To influence the access path selection for data with skewed distribution

comments powered by Disqus