Day 7: SQL Plan Management (SPM)

December 16, 2020

Today, we are going to talk about SQL Plan Management or SPM, which is a preventative mechanism to help stabilize SQL performance in Oracle databases. SPM is part of the base product as of version 11.1, but has added many features in Oracle 12c / 18c / 19c. Utilizing SPM allows for management of execution plans ensuring that the database uses only known or verified plans; and can help prevent performance issues caused by SQL plan changes. The main steps of using SPM include:

  • Plan capture – storing relevant information about plans for a set of SQL statements
  • Plan selection – the optimizer identifies plan changes based on stored plans history, and uses accepted SQL plan baselines to maintain SQL performance
  • Plan evolution – accepting new plans in existing baselines, either manually or automatically, normally after verifying that the new plan performs well

We don't have room here to cover every detail of SPM, so let's do a simple example of capturing a baseline from the cursor cache, and then seeing that the baseline is being used. Note: plans or baselines can also be captured automatically.
By default, Oracle will use SQL Plan baselines if they exist and are accepted. Default behavior for this has not changed since the 11gR1 version of Oracle. This can be adjusted by setting the
parameter OPTIMIZER_USE_SQL_PLAN_BASELINES to FALSE which will disable the usage. Also, it should be noted that this parameter is independent of capturing baselines.

Here we have a simple query joining two tables with the same set of data.

SQL_ID adjwa8r407rzc, child number 0
select /*+ gather_plan_statistics */ sum(t1.c), sum(t2.c)
   from     t1,   t2
  where    t1.a  =  t2.a  and    t1.d  =  :idnum

The data in the table has 25,001 duplicate rows, and 24,999 unique rows. Not to get into too much detail, but this leads to the possibility of two plans. One for looking up the duplicate rows and one for the unique rows.

SQL_ID                  PLAN_HASH_VALUE           CHILD_NUMBER
————-               —————                            ————
adjwa8r407rzc     3534348942                        0
adjwa8r407rzc     906334482                          1

Let’s create a SQL Plan Baseline on the first plan, so that the optimizer only considers that plan as good. We will use the LOAD_PLANS_FROM_CURSOR_CACHE functions of the DBMS_SPM package.


To receive the full 4 page article complete with code, please complete the form provided.  A pdf will be automatically emailed to you, so be sure to check your spam/junk mail if you do not see it.

    Full Name (required)

    Your Email (required)

    Company (required)

    Phone number (required)

    Job Title (required)