Database Reference
In-Depth Information
Unused SQL plan baselines that don't have the
fixed
attribute set to
yes
are automatically removed after a retention
period. The default retention period is 53 weeks. The current value can be displayed through the
dba_sql_management_
config
view (from version 12.1 onward the
cdb
version of the view also exists) with a query like the following:
SQL> SELECT parameter_value
2 FROM dba_sql_management_config
3 WHERE parameter_name = 'PLAN_RETENTION_WEEKS';
PARAMETER_VALUE
---------------
53
You can change the retention period by calling the
configure
procedure in the
dbms_spm
package. Values between
5 and 523 weeks are supported. The following example shows how to change it to 12 weeks. If the
parameter_value
parameter is set to
NULL
, the default value is restored:
dbms_spm.configure(parameter_name => 'plan_retention_weeks',
parameter_value => 12);
Privileges
When SQL plan baselines are automatically captured (that is, by setting the
optimizer_capture_sql_plan_baselines
initialization parameter to
TRUE
), no particular privilege is needed to create them.
The
dbms_spm
package can be executed only by users with the
administer sql management object
system
privilege (the
dba
role includes it by default). No object privileges exist for SQL plan baselines.
End users don't require specific privileges to use SQL plan baselines.
When to Use It
You should consider using SQL plan baselines in two situations. First, consider it whenever you're optimizing a
specific SQL statement and you can't change it in the application (for example, when adding hints isn't an option).
Second, you should consider using it when, for whatever reason, you're experiencing troublesome execution plans
instability. Because the aim of SQL plan baselines is to force the query optimizer to choose an execution plan from
a limited list of accepted execution plans, use this technique only when you want to explicitly restrict the query
optimizer's choice to specific execution plans.
Unfortunately, SQL plan baselines are available only with Enterprise Edition. With Standard Edition, use stored
outline instead.
Pitfalls and Fallacies
One of the most important properties of SQL plan baselines is that they're detached from the code. Nevertheless,
that could lead to problems. In fact, because there is no direct reference between the SQL plan baselines and the SQL
statement, it's possible that a developer will completely ignore the existence of the SQL plan baseline. As a result, if
the developer modifies the SQL statement in a way that leads to a modification of its signature, the SQL plan baseline
will no longer be used. Similarly, when you deploy an application that needs some SQL plan baselines to perform
correctly, you mustn't forget to install them during the database setup.
You must be aware that SQL plan baselines aren't immediately dropped when the objects they depend on are
dropped. This isn't necessarily a problem, though. For example, if a table or an index needs to be re-created because
