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 the 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:
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.
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.
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.
We can see that one plan was stored as a baseline:
Let's also check that a baseline was created:
Note: the baseline is enabled by default but not fixed. This happens when we manually load a baseline. As the baseline is not fixed, Oracle will attempt to capture new plans as baselines for this statement. To prevent this, we will FIX the baseline so that no other plans are considered.
Fixing a plan is done by using the ALTER_SQL_PLAN_BASELINE function of the DBMS_SPM package.
The function returns the number of plans that were modified. If you do not provide a plan name, then all plans for the given SQL_HANDLE will be affected.
Note: to remove the fixed status, set the ATTRIBUTE_VALUE to NO.
Now, let's see if new baselines are being used. The best way to do this is to query the DBA_SQL_PLAN_BASELINES view. This view has columns that show the origin of the SQL Baseline, the status, when it was last executed, and when it was verified. An example query:
Note: depending on your database setting, you will need to join on V$SQL either using FORCE or EXACT signature. Also, the outer join on the V$SQL table as the statement may not be in your cursor cache.
Abridged output from this query shows the last time the baseline was used for execution:
You can also see if the plan is being used by checking the NOTES section of an explain plan:
SQL plan management can improve or preserve performance during database upgrades, system, and data changes. A database upgrade that installs a new optimizer version usually results in plan changes for a small percentage of SQL statements. Using SPM, you can stabilize plan regression issues.
Hopefully, this example gets you thinking about how you can use SPM in your environment. Look for the upcoming presentation with Viscosity, where we will dive deeper into the many features of SPM, both manual and automated.
One of the sayings we have at Viscosity is that our customers "have four aces in their pocket." Over the next 5 days, the talented staff at Viscosity, along with our Oracle ACEs, will address more Oracle Database 18c and 19c new features. Continue to join us next year as we continue our Oracle Database 19c hands-on lab workshops.
Happy Holidays!