Cannot generate scripts for locking down access plans
"Locking down access plans" means generating a set of scripts for create PLAN_TABLE hints that are based on access plans in an EXPLAIN snapshot. This feature is helpful when you have a set of access plans that are optimized and you don't want changes in your system environment to affect them.
The problem that is described in this technote occurs when you attempt to lock down access plans in an EXPLAIN snapshot that uses information captured from catalog plans or packages. When you capture SQL statements from plans, packages, or both, and save those statements into a new query workload, you have the option of saving any EXPLAIN information that the workflow assistant found for those statements. The resulting EXPLAIN snapshot cannot be used for generating scripts for locking down access plans.
You can identify such an EXPLAIN snapshot by its type is either of the following dialog. These dialogs are for choosing EXPLAIN snapshots for comparisons. An EXPLAIN snapshot of the type "Workload Capture" cannot be used for generating scripts for locking down access plans.
Resolving the problem
There currently is no fix for this problem. You can generate scripts for locking down access plans only from an EXPLAIN snapshot that you created from the workflow assistant. There are three ways to create an EXPLAIN snapshot:
- The first time that you run advisors on a query workload, an EXPLAIN snapshot is taken automatically if no EXPLAIN information was saved when the query workload was created.
- Before running advisors on a query workload, select the option Re-collect EXPLAIN information before running workload advisors. This option appears on the Run Workload Advisors page in the Invoke section of the workflow assistant.
- In the Manage section of the workflow assistant, select the query workload and select Explain Selected Workload in the More actions field.