Hi, 
My first posting here. I have some i Series experience, but over 35 years Db2 experience on Db2 z/OS and 30 years experience on Db2 for LUW.

I am trying to help a busy Db2 on i shop that has a very large machine with about 750 GB of installed memory. They are running 7.3.  We have many large tables and a very high real time transaction volume 24 x7.

At my first look, the SQL Performance Center Plan Cache screen showed:

Current Plan Cache Size  -  490
Current Plan Cache Threshold Size Threshold  -  *Auto  
Maximum Plan Cache Size for AutoSizing  -  *Default(9216)  
Target Plan Cache Autosize Hit Ratio - *Default(90)
Current Plan Cache Hit Ratio - 40

Plan Cache Creation timestamp shows 2021-10-24
Last Plan Cache AutoSizing Adjustment 2021-10-24  ---(So autosizing had stopped)
Last Autosizing Limited Due to Temporary Storage 2022-03-29
Total Number of Plan Cache Autosizing Adjustments - 10

So this leads to a number of questions. Following by the actions we took, there are even more questions:

1. Why is the max plan cache so small? How is this determined?
2. How many failures need to occur for autosizing to stop?

First, beyond the IBM documentation, is there a decent source of additional information? My searches have found nothing. Also, like on every platform, the recommendations are to use the defaults and not to change them. 

My feelings with a very large amount of memory is I would like the Plan Cache to be much larger. Also, I have noticed that that all autosizing processes not only can increase sizes, but they are very good at taking memory away during less busy periods.

My first thoughts were:

3. There is 750 GB of memory, so I tried Current Plan Cache Threshold of 18432 as a fixed value to start.  The plan cache size increased over time to 7032 and the hit ratio rose to 65%. I felt we were on the right track.

4. The change was reversed back to *Auto due to a suspected disk space problem. (The problem was unrelated, but now people are worried about making the change back to 18432.)

5. Autosizing has reduced the Plan Cache back to 500 and the Hit Ratio is back to 42. Also the number of Adjustments increased to 27, and has stopped again.

Ultimately, I think we should increase the Plan Cache to a fixed value, likely around 45GB. I think we could pick up a good benefit. 

So, my questions are:
6. Does anyone have experience with this?

7. Is there other documentation / white papers available?

8. Are there dangers in increasing the Plan Cache using a fixed value?

Thanks,
Martin Hubel
IBM Gold Consultant
Lifetime IBM Champion

------------------------------
Martin Hubel
------------------------------

#SQL

The Plan Cache Autosizing algorithm is limited by the amount of temporary storage available in the System ASP.  And this property confirms that's happening on your server:
Last Autosizing Limited Due to Temporary Storage 2022-03-29

In the 7.4 release, they did change the Autosizing algorithm to be a little more aggressive with temporary storage usage.  There's a STRSST macro that can be run to enable that change on your 7.3 system, you can open a Support case to get that details on that if you want to give that a try.

The other option is setting a fixed PC size.  A bigger PC size not only allocates temporary storage for the PC, but also will increase the temporary storage used by Db2 in query plans for temporary data structures like hash tables & temporary indexes.  So the fixed PC size would mean more regular monitoring of the system temporary storage usage.  

Our Power Lab Services team can also help clients with this type of performance analysis.

------------------------------
Kent Milligan
------------------------------

Original Message:
Sent: Tue April 05, 2022 12:42 PM
From: Martin Hubel
Subject: Plan Cache Size

Hi, 
My first posting here. I have some i Series experience, but over 35 years Db2 experience on Db2 z/OS and 30 years experience on Db2 for LUW.

I am trying to help a busy Db2 on i shop that has a very large machine with about 750 GB of installed memory. They are running 7.3.  We have many large tables and a very high real time transaction volume 24 x7.

At my first look, the SQL Performance Center Plan Cache screen showed:

Current Plan Cache Size  -  490
Current Plan Cache Threshold Size Threshold  -  *Auto  
Maximum Plan Cache Size for AutoSizing  -  *Default(9216)  
Target Plan Cache Autosize Hit Ratio - *Default(90)
Current Plan Cache Hit Ratio - 40

Plan Cache Creation timestamp shows 2021-10-24
Last Plan Cache AutoSizing Adjustment 2021-10-24  ---(So autosizing had stopped)
Last Autosizing Limited Due to Temporary Storage 2022-03-29
Total Number of Plan Cache Autosizing Adjustments - 10

So this leads to a number of questions. Following by the actions we took, there are even more questions:

1. Why is the max plan cache so small? How is this determined?
2. How many failures need to occur for autosizing to stop?

First, beyond the IBM documentation, is there a decent source of additional information? My searches have found nothing. Also, like on every platform, the recommendations are to use the defaults and not to change them. 

My feelings with a very large amount of memory is I would like the Plan Cache to be much larger. Also, I have noticed that that all autosizing processes not only can increase sizes, but they are very good at taking memory away during less busy periods.

My first thoughts were:

3. There is 750 GB of memory, so I tried Current Plan Cache Threshold of 18432 as a fixed value to start.  The plan cache size increased over time to 7032 and the hit ratio rose to 65%. I felt we were on the right track.

4. The change was reversed back to *Auto due to a suspected disk space problem. (The problem was unrelated, but now people are worried about making the change back to 18432.)

5. Autosizing has reduced the Plan Cache back to 500 and the Hit Ratio is back to 42. Also the number of Adjustments increased to 27, and has stopped again.

Ultimately, I think we should increase the Plan Cache to a fixed value, likely around 45GB. I think we could pick up a good benefit. 

So, my questions are:
6. Does anyone have experience with this?

7. Is there other documentation / white papers available?

8. Are there dangers in increasing the Plan Cache using a fixed value?

Thanks,
Martin Hubel
IBM Gold Consultant
Lifetime IBM Champion

------------------------------
Martin Hubel
------------------------------

#SQL

Thanks, Kent. 
We will go back to a fixed PC size.

------------------------------
Martin Hubel
------------------------------

Original Message:
Sent: Wed April 06, 2022 01:11 PM
From: Kent Milligan
Subject: Plan Cache Size

The Plan Cache Autosizing algorithm is limited by the amount of temporary storage available in the System ASP.  And this property confirms that's happening on your server:
Last Autosizing Limited Due to Temporary Storage 2022-03-29

In the 7.4 release, they did change the Autosizing algorithm to be a little more aggressive with temporary storage usage.  There's a STRSST macro that can be run to enable that change on your 7.3 system, you can open a Support case to get that details on that if you want to give that a try.

The other option is setting a fixed PC size.  A bigger PC size not only allocates temporary storage for the PC, but also will increase the temporary storage used by Db2 in query plans for temporary data structures like hash tables & temporary indexes.  So the fixed PC size would mean more regular monitoring of the system temporary storage usage.  

Our Power Lab Services team can also help clients with this type of performance analysis.

------------------------------
Kent Milligan

Original Message:
Sent: Tue April 05, 2022 12:42 PM
From: Martin Hubel
Subject: Plan Cache Size

Hi, 
My first posting here. I have some i Series experience, but over 35 years Db2 experience on Db2 z/OS and 30 years experience on Db2 for LUW.

I am trying to help a busy Db2 on i shop that has a very large machine with about 750 GB of installed memory. They are running 7.3.  We have many large tables and a very high real time transaction volume 24 x7.

At my first look, the SQL Performance Center Plan Cache screen showed:

Current Plan Cache Size  -  490
Current Plan Cache Threshold Size Threshold  -  *Auto  
Maximum Plan Cache Size for AutoSizing  -  *Default(9216)  
Target Plan Cache Autosize Hit Ratio - *Default(90)
Current Plan Cache Hit Ratio - 40

Plan Cache Creation timestamp shows 2021-10-24
Last Plan Cache AutoSizing Adjustment 2021-10-24  ---(So autosizing had stopped)
Last Autosizing Limited Due to Temporary Storage 2022-03-29
Total Number of Plan Cache Autosizing Adjustments - 10

So this leads to a number of questions. Following by the actions we took, there are even more questions:

1. Why is the max plan cache so small? How is this determined?
2. How many failures need to occur for autosizing to stop?

First, beyond the IBM documentation, is there a decent source of additional information? My searches have found nothing. Also, like on every platform, the recommendations are to use the defaults and not to change them. 

My feelings with a very large amount of memory is I would like the Plan Cache to be much larger. Also, I have noticed that that all autosizing processes not only can increase sizes, but they are very good at taking memory away during less busy periods.

My first thoughts were:

3. There is 750 GB of memory, so I tried Current Plan Cache Threshold of 18432 as a fixed value to start.  The plan cache size increased over time to 7032 and the hit ratio rose to 65%. I felt we were on the right track.

4. The change was reversed back to *Auto due to a suspected disk space problem. (The problem was unrelated, but now people are worried about making the change back to 18432.)

5. Autosizing has reduced the Plan Cache back to 500 and the Hit Ratio is back to 42. Also the number of Adjustments increased to 27, and has stopped again.

Ultimately, I think we should increase the Plan Cache to a fixed value, likely around 45GB. I think we could pick up a good benefit. 

So, my questions are:
6. Does anyone have experience with this?

7. Is there other documentation / white papers available?

8. Are there dangers in increasing the Plan Cache using a fixed value?

Thanks,
Martin Hubel
IBM Gold Consultant
Lifetime IBM Champion

------------------------------
Martin Hubel
------------------------------

#SQL