This demo shows you how you can use SQL plan management (SPM) to fix a SQL statement that is experiencing a performance regression caused by a sub-optimal plan.

SPM will search for historic plans, choose the best one and enforce it with a SQL plan baseline.

This demonstration is intended for use in Oracle Database 18c onwards.

It works as follows:

- Tables T1 and T2 have data skew

- Q1 is a query that joins T1 and T2

- Histograms tell the optimizer about the skew so Q1 performs well

- We drop the histograms and this induces a poor plan for Q1

- SPM is initiated and it finds the previous good plan

- The good plan is tested (automatically) by SPM and a SQL plan baseline is created

- Q1 now uses the good plan

$ sqlplus / as sysdba [or connect to PDB ADMIN]

SQL> @@user

SQL> connect spmdemo/spmdemo

-- Create test tables

SQL> @@tab

-- Review/execute the following script

SQL> @@example

Note that AWR is accessed. Check the Oracle Database License Guide for details.

The test creates two tables T1 and T2 - use a test database

DECLARE

l_plans_dropped PLS_INTEGER;

FOR REC IN (SELECT DISTINCT SQL_HANDLE FROM DBA_SQL_PLAN_BASELINES )

LOOP

L_PLANS_DROPPED := DBMS_SPM.DROP_SQL_PLAN_BASELINE (

sql_handle => rec.sql_handle,

PLAN_NAME => NULL);

END LOOP;

END;

set echo on

exec dbms_stats.delete_column_stats(user,'t1','d',no_invalidate=>false,col_stat_type=>'HISTOGRAM');

exec dbms_stats.delete_column_stats(user,'t2','d',no_invalidate=>false,col_stat_type=>'HISTOGRAM');