Databases Reference
In-Depth Information
There's more...
We can also create a baseline using the snapshots taken from Automatic Workload Repository
or using an SQL Tuning Set. In this case, we execute the following statement, to create a
baseline from
test_tuning_set
(as created in the
Creating
SQL
Tuning
Sets
recipe):
DECLARE
l_plan pls_integer;
BEGIN
l_plan := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'test_tuning_set');
END;
/
To control the SQL Plan Management there are two parameters:
F
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
: If set to
TRUE
, automatic capturing
of SQL Plans is enabled; the default value is
FALSE
F
OPTIMIZER_USE_SQL_PLAN_BASELINES
: If set to
TRUE
, SQL Plan Management is
enabled; this parameter is enabled by default
When we manually capture SQL Plans, the resulting plan is marked as accepted. However,
when the plans are automatically captured, we need to perform the evolving SQL Plan
baseline process, using the
DBMS_SPM.evolve_sql_plan_baseline
function.
By launching this function (it needs only one parameter, the
sql_handle
of the statement,
which we can get from the
DBA_SQL_PLAN_BASELINES
view), the optimizer will determine
if non-accepted plans in the baseline should be accepted. The function returns a CLOB
containing a complete report of the results.
See also
F
The
Managing stored outlines
,
Creating
SQL
Tuning
Sets
, and
Using
Tuning
Advisor
recipes in this chapter
