Changing a plan score

The database monitors the SQL plan cache size. When the size exceeds the maximum threshold, plans are discarded until the total size is within the threshold. Plans are pruned based on their plan score. A plan's score is computed from multiple criteria including total runtime, optimization time and how long it has been since the plan was last used. Plans with low scores (relative to other plans in the cache) are discarded. You can override the plan score for a statement to either a high value to bias it away from being discarded or a low value hasten its removal. Once overriden, the plan score is locked in to the value specified.

To change a plan score for a statement, do the following:

  1. In the System i Navigator window, expand the system you want to use.
  2. Expand Databases and the database that you want to work with.
  3. Right-click SQL plan cache snapshot and select SQL Plan Cache -> Show Statements.
  4. Enter filter information and click Apply. To just return all statements, click Refresh.
  5. Right-click a statement and select Plan -> Change Plan Score.