HOL 18c SQL Plan Management

In the previous section you did find slow SQL statements with the SQL Performance Analyzer. Now we can use SQL Plan Management to fix the plans. The question is: Is this always necessary and useful?

HOL 18c SQL Plan Management

The SQL Performance Analyzer reports showed overall good results for the run in Oracle 18.2.0.

But still you could now try to fix a specific plan which has been changed. Or just write down all plans in the SQL Tuning Set into the SQL Plan Baseline in Oracle 18.2.0. Lets see if the result is good. Or if it may be better to allow the optimizer to find newer paths.

Fix a single statement

In this exercise we use scripts written by Carlos Sierra.

Will fix this plan now.

. upgr18
cd /home/oracle/scripts
sqlplus / as sysdba

Here we’ll use one of Carlos Sierra’s scripts: spb_create.sql:


The script asks you for the SQL_ID first:

Type in: 13dn4hkrzfpdy

Then it will display the potential plans:


       Plan ET Avg      ET Avg      CPU Avg     CPU Avg           BG Avg       BG Avg     Rows Avg     Rows Avg       Executions       Executions                                   ET 100th    ET 99th     ET 97th     ET 95th     CPU 100th   CPU 99th    CPU 97th    CPU 95th
 Hash Value AWR (ms)    MEM (ms)    AWR (ms)    MEM (ms)             AWR          MEM          AWR          MEM              AWR              MEM   MIN Cost   MAX Cost  NL  HJ  MJ Pctl (ms)   Pctl (ms)   Pctl (ms)   Pctl (ms)   Pctl (ms)   Pctl (ms)   Pctl (ms)   Pctl (ms)
----------- ----------- ----------- ----------- ----------- ------------ ------------ ------------ ------------ ---------------- ---------------- ---------- ---------- --- --- --- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
 3300316041       3.710                   1.601                      603                     1.000                         3,202                         385        385   1   0   0       3.710       3.710       3.710       3.710       1.601       1.601       1.601       1.601
  954326358       4.011                   1.906                      609                     1.000                         3,193                         405        405   2   0   0       4.011       4.011       4.011       4.011       1.906       1.906       1.906       1.906

The first plan is the better plan – found after upgrade. We will fix it now by accepting it as THE plan we’d like to be used for future executions of statement with SQL_ID: 13dn4hkrzfpdy

Select up to 3 plans:

1st Plan Hash Value (req): 3300316041
2nd Plan Hash Value (opt):


Verify if the plan has been accepted:

SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines;

Fix all statements

Now we pin down all possible statements collected in the SQL Tuning Set STS_CaptureCursorCache – and verify with SQL Performance Analyzer again the effect.

Use spm_load_all.sql:


Once you fixed the plans, use the SQL Performance Analyzer to verify the plans and the performance:


Compare the two resulting reports again – and compare them to the two examples from the previous run.

Do you recognize that fixing all statements resulted in CPU_TIME stable compared to but worse than in the initial run in 18.2.0?

What is the outcome?
Allow the new release to find new, sometimes better plans. Even though your most critical statements should be stable at first, you should allow changes to benefit from better performance.

The idea of testing is that you identify the really bad statements and plans, and fix them. But not all of them.

Further Resources

MOS Note: 789888.1
How to Load SQL Plans into SQL Plan Management (SPM) from the Automatic Workload Repository (AWR)

MOS Note: 456518.1
How to Use SQL Plan Management (SPM) – Plan Stability Worked Example

White Paper:
SQL Plan Management with Oracle Database 12c Release 2

Carlos Sierra: Plan Stability



===> NEXT: Plugin UPGR into CDB2