Sql plan management

SQL plan management provides a mechanism for maintaining consistent SQL performance regardless of changes in optimizer version, optimizer statistics, schema changes, system settings and SQL profile creation.

More theory can be found here:
https://oracle-base.com/articles/11g/sql-plan-management-11gr1

Steps to configure sql plan management:

Check if Automatic Plan Capture is enabled or not. IT IS NOT NECESSARY ALSWAY TO HAVE TO ENABLED AND CAREFULL TESTING NEEDS TO BE DONE TO ENABLE IT IN PROD.

SHOW PARAMETER OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

If not already enabled, enable it.

ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;

The use of SQL plan baselines is controlled by the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter, which is set to TRUE by defaul.

show parameter optimizer_use_sql_plan_baselines;

Access to the DBMS_SPM package requires the ADMINISTER SQL MANAGEMENT OBJECT privilege.

If it is not available, grant it.
grant ADMINISTER SQL MANAGEMENT OBJECT to SCOTT;



Manual Plan Loading:
LOAD_PLANS_FROM_CURSOR_CACHE:
The LOAD_PLANS_FROM_CURSOR_CACHE functions allow SQL statements to be loaded from the cursor cache.
There are four overloads, allowing statements to be identified by a number of criteria, including: SQL_ID, SQL_TEXT, PARSING_SCHEMA_NAME, MODULE and ACTION. Manually loaded plan are by default accpeted
The following example identifies the SQL statement using the SQL_ID.
SET SERVEROUTPUT ON
DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => 'gat6z1bc6nc2d');
   
  DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/

Note that once this plan loaded it will automatically create baseline if not already present or add it to existing baseline. Manual loading automatically enables and accepts plan.
Now even if this plan is sub-optimal query will continue to use this plan even if better plan exists. To make use of better plan we either need to evolve current plan or accept other plans if automatic plan capture is enabled.

Assume that above baseline uses FTS and latter index added on the column and index scan is optimal. So to make query use better plan we have to evolve this plan.

We can check the baseline created in DBA_SQL_PLAN_BASELINES view.

SELECT sql_handle, plan_name, enabled, accepted
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%spm_test_tab%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';

To evolve sql baseline to accept new plan generated automatically;

SET LONG 10000
SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9')
FROM   dual;

LOAD_PLANS_FROM_SQLSET:
alternatively you can load plan from sql tunning set as well.

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_sqlset(
    sqlset_name => 'my_sqlset');
END;
/

ALTERING PLAN BASELINES:

The ALTER_SQL_PLAN_BASELINE function allows the following attributes of a specific plan, or all plans within a baseline to be altered:

enabled (YES/NO) : If YES, the plan is available for the optimizer if it is also marked as accepted.
fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans.
autopurge (YES/NO) : If YES, the SQL plan baseline is purged automatically if it is not used for a period of time.
plan_name : Used to amend the SQL plan name, up to a maximum of 30 character.
description : Used to amend the SQL plan description, up to a maximum of 30 character.
The following shows a specific plan being marked as fixed.

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SYS_SQL_7b76323ad90440b9',
    plan_name       => 'SYS_SQL_PLAN_d90440b9ed3324c0',
    attribute_name  => 'fixed',
    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

SQL MANAGEMENT BASE:

We can check parameters those can be configured by dbms_spm package for baseline by using below query;
SELECT parameter_name, parameter_value
FROM   dba_sql_management_config;

how to set these parameters:

BEGIN
  DBMS_SPM.configure('space_budget_percent', 11);
  DBMS_SPM.configure('plan_retention_weeks', 54);
END;
/

TRANSFERRING SQL PLAN BASELINES:

The DBMS_SPM package provides functionality for transferring SQL plan baselines between databases. First, a staging table must be created in the source database using the CREATE_STGTAB_BASELINE procedure.

BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(
    table_name      => 'spm_stageing_tab',
    table_owner     => 'TEST',
    tablespace_name => 'USERS');
END;
/
The PACK_STGTAB_BASELINE function exports the SQL plan baselines to the staging table. There are several parameters allowing you to limit amount and type of data you export. The following example exports all SQL plan baselines.

SET SERVEROUTPUT ON
DECLARE
  l_plans_packed  PLS_INTEGER;
BEGIN
  l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
    table_name      => 'spm_stageing_tab',
    table_owner     => 'TEST');

  DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/

Then export this table and import it to destination schema. Post importing we can unpack baselines by using UNPACK_STGTAB_BASELINE function

SET SERVEROUTPUT ON
DECLARE
  l_plans_unpacked  PLS_INTEGER;
BEGIN
  l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
    table_name      => 'spm_stageing_tab',
    table_owner     => 'TEST',
    creator         => 'TEST');

  DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/

DROPPING PLANS AND BASELINES:

The DROP_SQL_PLAN_BASELINE function can drop a specific plan from a baseline, or all plans if the plan name is not specified.

CONN sys/password@db11g AS SYSDBA

SET SERVEROUTPUT ON
DECLARE
  l_plans_dropped  PLS_INTEGER;
BEGIN
  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
    sql_handle => NULL,
    plan_name  => 'SYS_SQL_7b76323ad90440b9');
   
  DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/