Triton Db2 Geek

Confessions of a DB2 geek

IBM Gold Consultant Program and IBM Premier business Partner

OLTP and BI mixed workloads – a merry coexistence in DB2 10.5 “Cancun Release”

September 22nd, 2014 - by

If you have been using DB2 10.5 with a mix of row and column based tables in the same database you may have experienced slowdown in some of your OLTP queries accessing your row based tables after implementing column based tables.  We had this issue with one of our customers after implementing columnar tables.  In one scenario this happened due to an access plan change where the optimizer chose a more expensive HSJOIN after the SORTHEAP was increased.

 

A quick background: columnar tables need a very high allocation of database level sort memory, (allocated using the sortheap and sheapthres_shr database parameters) for their processing.  However, changing sort memory to a very high value can have an adverse effect on some OLTP queries, depending on predicate and statistical distribution. With a small SORTHEAP value, the optimizer correctly chooses NLJOIN (nested loop join) or MSJOIN (merge sort join) because HSJOIN (hash sort join) is estimated to have significant sort spilling. But with a very high SORTHEAP value, the optimizer may think there is no spilling and hence wrongly decides HSJOIN is cheaper, causing the OLTP queries to take almost twice as much time to execute. We noticed in some of our queries that access plans were using MSJOIN and index scan before the SORTHEAP was increased. After the SORTHEAP increase, the access plans got changed and the optimizer decided to use HSJOIN and a table scan.

 

This meant that in order to maintain the SLAs for the OLTP queries, we would have to place the columnar tables in a separate database. Not ideal.

 

Now, enter the new DB2 10.5 FP4 “Cancun Release” and we have a solution! Using the DB2 registry variable DB2_EXTENDED_OPTIMIZATION, we can specify a lower SORTHEAP value (the original SORTHEAP value before it was increased to accommodate columnar tables) and force the optimizer to use that value for OLTP queries accessing row-based tables.

 

db2set DB2_EXTENDED_OPTIMIZATION=”OPT_SORTHEAP_EXCEPT_COL 1024″

 

If the query is not using any column organized objects then the optimizer can decide to ignore the database level large SORTHEAP value and to use instead the new value specified in this registry setting (1024 in this example).

 

After setting the DB2_EXTENDED_OPTIMIZATION registry variable in our database, we noticed the access plan for the OLTP query was back to how it was originally. MSJOIN and index scan was used and the response time was back to normal.

 

This now means row and column based tables can co-exist in the same database without OLTP queries on row tables being adversely affected.

 

This is also really useful for customers who have a need to carry our reporting on their transactional databases. They can use shadow tables (see http://bit.ly/shadow_tables) which are columnar, without worrying about the adverse impact the large sort memory will have on their OLTP queries accessing row based tables.

 

Authors: Somu Chakrabarty and Iqbal Goralwalla

Comments

  • I got the opportunity to discuss with two different customers in IDUG Open user group meet few months back on their experience on movement to DB 10.5. One of them were moving their Datamart from SQL Server to DB2 BLU, they were using the ANALYTICS option for workgroup having both ROW based and COLUMN Organized tables. The advantage they experienced were tremendous, in terms of Load & Go, Extrememe performance gain in complex queries and ease of migration as 10.5 supports both Row based + Column Based tables. The other team of folks who were moving to 10.5 just to be in latest in terms of product they were using, they were not using the ANALYTICS options to go with the base version. Even with ROW Based tables but on 10.5, they could observe very high percentage of data compression, great optimization graphs with minimal intervention at DBA activities. Even though you are not using the BLU Feature by using ANALYTICS, moving to 10.5 itself is a huge gain it seems.

  • Iqbal Goralwalla says:

    Thanks for your sharing your Blu experiences with customers Jayanta. It’s great to see customers reap performance and storage benefits from BLU and also from moving to DB2 10.5.

« »

Tag Archives