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.