-- <pre>

-- Script for demo of adaptive cursor sharing with SPM
-- Author: Allison Lee

connect hr/hr

Rem ===========================================================================
Rem Step 0. Show the world that there is skew on the job column.
Rem ===========================================================================

select job_id, count(*)
from employees_acs
group by job_id
order by 2;

Rem ===========================================================================
Rem Step 1. Run with each bind value, to see which plans are picked by default.
Rem  Each bind value produces a different plan.
Rem ===========================================================================

var job varchar2(10);

exec :job := 'AD_PRES'
SELECT /*+ bind_aware */ avg(e.salary), d.department_name
FROM employees_acs e, departments d
WHERE e.job_id = :job
AND e.department_id = d.department_id
GROUP BY d.department_name;

select * from table(dbms_xplan.display_cursor(format=>'basic'));

exec :job := 'AD_VP'
SELECT /*+ bind_aware */ avg(e.salary), d.department_name
FROM employees_acs e, departments d
WHERE e.job_id = :job
AND e.department_id = d.department_id
GROUP BY d.department_name;

select * from table(dbms_xplan.display_cursor(format=>'basic'));

exec :job := 'SA_REP'
SELECT /*+ bind_aware */ avg(e.salary), d.department_name
FROM employees_acs e, departments d
WHERE e.job_id = :job
AND e.department_id = d.department_id
GROUP BY d.department_name;

select * from table(dbms_xplan.display_cursor(format=>'basic'));

Rem ===========================================================================
Rem Step 2.  Load just two of the plans into the plan baseline.
Rem ===========================================================================

Rem cleanup from step 1
alter system flush shared_pool;

exec :job := 'AD_PRES'
SELECT /*+ bind_aware */ avg(e.salary), d.department_name
FROM employees_acs e, departments d
WHERE e.job_id = :job
AND e.department_id = d.department_id
GROUP BY d.department_name;

exec :job := 'SA_REP'
SELECT /*+ bind_aware */ avg(e.salary), d.department_name
FROM employees_acs e, departments d
WHERE e.job_id = :job
AND e.department_id = d.department_id
GROUP BY d.department_name;

var loaded number
exec :loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE('1f9qv0z3gzaba');
print :loaded

Rem ===========================================================================
Rem Step 3.  See which plans are chosen for the three bind values after loading
Rem   baseline plans.
Rem ===========================================================================

Rem cleanup from step 2
alter system flush shared_pool;

Rem SPM-related invalidations on the first execution with a baseline prevent
Rem us from seeing the plan after the query is run, because the cursor is
Rem already gone.  So run the query twice for each bind.

exec :job := 'AD_PRES'

SELECT /*+ bind_aware */ avg(e.salary), d.department_name
FROM employees_acs e, departments d
WHERE e.job_id = :job
AND e.department_id = d.department_id
GROUP BY d.department_name;

SELECT /*+ bind_aware */ avg(e.salary), d.department_name
FROM employees_acs e, departments d
WHERE e.job_id = :job
AND e.department_id = d.department_id
GROUP BY d.department_name;

select * from table(dbms_xplan.display_cursor(format=>'basic +note'));

exec :job := 'AD_VP'

SELECT /*+ bind_aware */ avg(e.salary), d.department_name
FROM employees_acs e, departments d
WHERE e.job_id = :job
AND e.department_id = d.department_id
GROUP BY d.department_name;

SELECT /*+ bind_aware */ avg(e.salary), d.department_name
FROM employees_acs e, departments d
WHERE e.job_id = :job
AND e.department_id = d.department_id
GROUP BY d.department_name;

select * from table(dbms_xplan.display_cursor(format=>'basic +note'));

exec :job := 'SA_REP'

SELECT /*+ bind_aware */ avg(e.salary), d.department_name
FROM employees_acs e, departments d
WHERE e.job_id = :job
AND e.department_id = d.department_id
GROUP BY d.department_name;

SELECT /*+ bind_aware */ avg(e.salary), d.department_name
FROM employees_acs e, departments d
WHERE e.job_id = :job
AND e.department_id = d.department_id
GROUP BY d.department_name;

select * from table(dbms_xplan.display_cursor(format=>'basic +note'));

Rem the second bind value caused a new unaccepted plan to be added

select sql_handle, plan_name, accepted 
from dba_sql_plan_baselines 
where sql_handle = 'SQL_4d714c1805f2d35c';

Rem display the new unnacepted plan

select * from table(dbms_xplan.display_sql_plan_baseline
                    (sql_handle => 'SQL_4d714c1805f2d35c',
		     plan_name => 'SQL_PLAN_4uwac302z5nuw94347992',
                     format=>'basic'));

-- </pre>
