Hardware-driven Query Plan Compression

Earlier this month, IBM announced support for Power11. That announcement reminded me of a Db2 enhancement tied to the IBM Power10 portfolio which hasn’t been widely promoted. With Power10, the processors include an on-chip unit that supports hardware-based compression and decompression.

 

The Db2 for i development team decided to leverage this high-speed compression capability with its Plan Cache support. When a query running on Power10 (& Power11) servers produces a large query plan, the Db2 optimizer will store a compressed version of the query access plan in its Plan Cache. Compressed query plans means that Db2 can store more plans in the Plan Cache without increasing its overall size. More query plans increase the chances of  Db2 reusing a query plans which contributes to faster query performance since building a new query plan takes time and system resources. 

As you can see in the following figure, Db2 uses a couple of new Plan Cache properties in the ACS SQL Performance Center to highlight the temporary storage savings of query plan compression on newer Power servers. In this example, query plan compression is saving over half a gigabyte of temporary storage usage.

 

With this compression capability available, clients running with Power10 or Power11 may also want to review their Plan Cache configuration setting for the Plan Cache auto-sizer. That setting is also visible on the SQL Performance Center Plan Cache properties display as shown below.

 

 

Combining plan compression with auto-sizing enables clients to get a bigger bang for their buck when it comes to the temporary storage footprint used by Db2 for query workloads. That’s because the size of the Plan Cache not only controls the amount of temporary storage allocated for the Plan Cache, but it also impacts how much temporary storage is used to cache temporary runtime data structures to speed up future executions of the same query.

 

If the Plan Cache uses auto-sizing, Db2 will keep the same number of plans and cached temporary structures as it did before Power 10 hardware, but it will do so with a smaller temporary storage footprint. That’s due to the fact that the hit ratio drives the number of plans in the cache instead of the fixed size of the cache.  A comparison of the two sample Plan Cache configuration should make it clearer why auto-sizing can have an advantage when it comes to overall temporary storage usage by Db2 for query workloads.

 

First, let’s start with a Plan Cache using auto-sizing. On older hardware, assume that twenty thousand query plans require 2 GB of temporary storage in the auto-sized Plan Cache. With Power10 servers, we’ll assume that twenty thousand query plans could be stored in 1.5 GB of storage. 

 

In contrast, consider a Plan Cache manually set to a fixed size of 2 GB. On older hardware, it makes sense that twenty thousand query plans can also be stored in that fixed-size cache. Imagine that Power10 compression enables twenty-three thousand plans to be stored in the fixed-size 2 GB cache. While it’s great the fixed-size plan cache can store three thousand more plans with compression, keep in mind that the number of query plans dictates how much temporary storage Db2 allocates for the caching of temporary runtime structures mentioned earlier. The higher the number of query plans, the higher the number of temporary runtime structures that can be cached by Db2 for i.

 

With twenty-three thousand plans, let’s assume Db2 allocates 2 GB for cached temporary runtime structures. That means the total temporary storage footprint for the fixed-size cache is 4 GB – 2 GB for the fixed size Plan Cache and 2 GB for the cached temporary structures.  In comparison, assume that the twenty thousand plans in the auto-sized Plan Cache results in Db2 allocating 1.75 GB of storage for cached temporary runtime structures. That results in total temp storage footprint for the auto-sized Plan Cache being 3.25 GB.  Almost 20% less storage than the fixed-size cache temporary storage footprint.

 

The next logical question that pops to mind is why are not all clients using Plan Cache auto-sizing?  One of the primary reasons for this choice is that Db2’s auto-sizing algorithm prior to the IBM i 7.4 release was too conservative in its usage of temporary storage for the Plan Cache. This conservative nature often caused the auto-sizer to not allocate more temporary storage to store more query plans in the Plan Cache. Some customers’ query workloads were not able to meet their target Plan Cache Hit Ratio due to this behavior. As a result, some clients turned off the auto-sizer and manually increased the size of the Plan Cache to achieve a higher hit ratio. 

 

So, if you’re using newer Power hardware (Power10 or Power11),  it’s a good time to review your Plan Cache configuration to ensure that your query workloads are getting the most out of the temporary storage and hardware compression used by Db2 for your query workloads. If you’re not yet using newer Power hardware, then maybe it’s a good time to put a bug in the ear of your management.