content_image/990757.jpg
## 11.2.2 Locating and loading stabilized statements 

Without dynamic plan stability, a dynamic SQL statement that is not found in the dynamic statement cache goes through a full prepare. With dynamic plan stability, Db2 checks for a match in stabilized statements that are stored in the Db2 catalog after a cache miss. If a match is located, this stabilized statement and its cache structures are loaded from the catalog back into the dynamic statement cache and the full prepare is avoided. In this way, the query can be run with the stabilized access path and reliable query performance can be achieved. If a match is not located among the stabilized queries in the catalog, the query goes through a full prepare. 

## Locating and loading performance results 

The performance measurements in this section compare the cost of locating and loading the stabilized statements and their cache structures from the catalog against the cost of full prepares. As shown in Figure 11-3, for our 950 and 3,600 complex queries cases, we see over 90% elapsed time and CPU time savings with locating and loading the stabilized statements from the catalog compared with doing full prepares. For our 80,000 and 320,000 simple queries, we see over 20% elapsed time and 70% CPU time savings with matching stabilized statements over full prepares. 

<img src='content_image/990757.jpg'>

## 11.2.3 Overhead of stabilized statement misses 

With dynamic plan stability, the query is forced to go through a full prepare if locating the matching stabilized statements results in a miss. For these situations, the process of finding the matching stabilized statements is pure overhead compared to not using dynamic plan stability. 

To determine the amount of overhead of stabilized statement misses, we performed a series of tests. Before the tests start, no stabilized dynamic statements are available. Db2 is recycled and the dynamic statement cache is empty. The test is done by using the following process: 

1. Full prepare the 3,600 RLP101 complex queries when no stabilized queries are present. This result is the baseline to compare to for Step 6, in which the same 3,600 RLP101 complex queries go through a full prepare when 900,000 unmatched stabilized queries are in the catalogs. 

