Eliminating SQL Cache Autosize limitations record by "last autosizing limited due to temporary storage" actions
How can we eliminate "Last autosizing limited due to temporary storage" occurrence in IBMi LPAR? My first thought is we need more real memory.
IBM Description of SQL Cache Property "Last autosizing limited due to temporary storage":
Last Autosizing Limited Due to Temporary Storage
If autosizing is active, the timestamp of when the plan cache
last attempted to increase the size of the plan cache but was
unable to do so because the temporary storage used on the system
exceeded limits.
IBM Description of SQL Cache Property "Last autosizing limited due to temporary storage":
Last Autosizing Limited Due to Temporary Storage
If autosizing is active, the timestamp of when the plan cache
last attempted to increase the size of the plan cache but was
unable to do so because the temporary storage used on the system
exceeded limits.
Apparently you have a large SQL Plan Cache that requires more temp storage than can be safely allocated for the resizing process. Resizing is generally desirable for performance reasons, but it is optional. The system manages temp space, and some systems require an occasional IPL, which frees all temp space (it will also clear the plan cache).
Running out of temp space is bad, and can result in a system halt that can result in a difficult recovery. Treat this as a possible early warning sign.
First, let's see if you're in bad shape for temp space. Use the WRKSYSSTS to display temp storage utilization. Post a screen shot here.
Next, make sure you are current on all temp storage related PTFs: https://www.ibm.com/support/pages/node/706651
An IPL is usually the fastest way to resolve a critical temp space usage issue, and has the beneficial side effect of clearing the SQL plan cache.
After ensuring PTFs are up to date and performing an IPL, I suggest you monitor temp space usage for a while and see if it starts creeping up over time. Diagnosing temp space utilization problems can be difficult, and may require the use of tools an techniques that are potentially risky to the stability of the system. I recommend working with IBM Support on this sort of problem if PTF and IPL doesn't resolve it, since service tools use is sometimes required to diagnose and fix.
Here are some articles with additional guidance. Look at the bottom of the main article for guidance on reducing temp space usage:
https://www.ibm.com/support/pages/reducing-system-asp-disk-space-dasd-storage-used
Running out of temp space is bad, and can result in a system halt that can result in a difficult recovery. Treat this as a possible early warning sign.
First, let's see if you're in bad shape for temp space. Use the WRKSYSSTS to display temp storage utilization. Post a screen shot here.
Next, make sure you are current on all temp storage related PTFs: https://www.ibm.com/support/pages/node/706651
An IPL is usually the fastest way to resolve a critical temp space usage issue, and has the beneficial side effect of clearing the SQL plan cache.
After ensuring PTFs are up to date and performing an IPL, I suggest you monitor temp space usage for a while and see if it starts creeping up over time. Diagnosing temp space utilization problems can be difficult, and may require the use of tools an techniques that are potentially risky to the stability of the system. I recommend working with IBM Support on this sort of problem if PTF and IPL doesn't resolve it, since service tools use is sometimes required to diagnose and fix.
Here are some articles with additional guidance. Look at the bottom of the main article for guidance on reducing temp space usage:
https://www.ibm.com/support/pages/reducing-system-asp-disk-space-dasd-storage-used
ASKER
Based on the QWCCTLTS API, our current setting is .0000, no limitation and or no warning. On a test box, I change the Warning or Acceptable limit to 10% of the System ASP, but the QWCCTLTS " *DSPLMT control still says we are over the limit when only .2972 of the ASP is being used. Reaching out to IBM Support Today.
Thanks!
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Take a look at your temp disk space. Ensure you have plenty of free space.