10 | SQL PLAN MANAGEMENT WITH ORACLE DATABASE 12C
Initialization Parameters
There are two init.ora parameters that control SPM.
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
Controls the automatic creation of new SQL plan baselines for
repeatable SQL statements. This parameter is set to
FALSE
by default. Note it is not necessary for this parameter to
be set to
TRUE
in order to have a newly found plan added to an existing SQL plan baseline.
If a SQL statement has multiple plans then all of them will be captured, but only the first will be accepted. If you do
not which to use plan baselines during auto capture, you can set optimizer_use_sql_plan_baselines to
FALSE
.
OPTIMIZER_USE_SQL_PLAN_BASELINES
controls the use of SQL plan baselines. When enabled, the optimizer
checks to see if the SQL statement being compiled has a SQL plan baseline before executing the cost-based plan
determined during parse. If a SQL plan baseline is found and the cost-based plan is an accepted plan in that
baseline, then the optimizer will go ahead and use that plan. However, if a SQL plan baseline is found and the cost-
based plan is not an accepted plan in that baseline, then it will be added to the SQL plan baseline but not executed.
The optimizer will cost each of the accepted plans in the SQL plan baseline and pick the one with the lowest cost.
This parameter is
TRUE
by default. When set to
FALSE
the optimizer will only use the cost-based plan determined
during parse (SQL plan baselines will be “ignored”) and no new plans will be added to existing SQL plan baselines.
These parameter values can be changed on the command line either at a session or system level using an
alter
session
or
alter system
command. It is also possible to adjust the parameter setting on the upper left hand
side of the main SQL plan baseline page (the Settings section) in Enterprise Manager.
Managing the space consumption of SQL Management Base
The statement log and all SQL plan baselines are stored in the SQL Management Base. The SQL Management
Base is part of the database dictionary, stored in the
SYSAUX
tablespace; this is the tablespace for all internal
persistent information outside the dictionary and cannot be changed. By default, the space limit for the SQL
Management Base is no more than 10% of the size of the
SYSAUX
tablespace. However, it is possible to change the
limit to any value between 1% and 50% using the PL/SQL procedure
DBMS_SPM.CONFIGURE
or Enterprise
Manager. A weekly background process measures the total space occupied by the SQL Management Base, and
when the defined limit is exceeded, the process will generate a warning in the alert log, for example:
SPM: SMB space usage (99215979367) exceeds 10.000000% of SYSAUX size (1018594954366).
Reaching the limit will not prevent new plans from being added to existing SQL plan baselines or new SQL plan
baselines from being added to the SQL Management Base.
There is also a weekly scheduled purging task (operated by MMON) that manages the disk space used by SPM
inside the SQL Management Base. The task runs automatically and purges any plans that have not been used for
more than 53 weeks, by running the
DBMS_SPM.DROP_SQL_PLAN_BASELINE
function on each one. It is
possible to change the unused plan retention period using either using
DBMS_SPM.CONFIGURE
or Enterprise
Manager; its value can range from 5 to 523 weeks (a little more than 10 years).
The SQL Management Base is stored entirely within the
SYSAUX
tablespace, so SPM will not be used if this
tablespace is not available.