-- Script for blog post on SPM and how baselines are matched 
connect sh/sh
set pages 9999
set lines 150
set echo on
spool how_baseline_are_matched.log

-- This is the SQL statement in question.
-- Select count(s.quantity_sold) total_sales
-- From   Sales s
-- Where  s.prod_id=30; 

-- Setup SPM for our statement via the SH schema
-- 1. Execute the statement & check the plan

Select count(s.quantity_sold) total_sales
From   Sales s
Where  s.prod_id=30;

select * from table(dbms_xplan.display_cursor());

-- 2. Find SQL_ID for the SQL statement
col sql_text format a42
col parsing_schema_name format a14
col sql_handle format a20

Select sql_id, sql_text
From   V$sql
Where  sql_text like 'Select count(s.quantity_sold)%';

-- 3. Load the plan from the cursor cache into a SQL plan baseline
variable cnt number;

execute :cnt :=dbms_spm.load_plans_from_cursor_cache(sql_id=>'d439wy93ffuc6');

Select :cnt from dual;

Select SQL_HANDLE, SQL_TEXT, PARSING_SCHEMA_NAME, PLAN_NAME, ENABLED, ACCEPTED
From   dba_sql_plan_baselines
Where  sql_text like 'Select count(s.quantity_sold%';

-- 4. Confirm the plan baseline is being used.

-- explain plan for
Select count(s.quantity_sold) total_sales
From   Sales s
Where  s.prod_id=30;

select * from table(dbms_xplan.display_cursor());

-- 5. Make a copy of the sales table in the scott schema so we can run the same query from there
--  exp sh/sh file=sales.dmp log=exp.log tables='SALES'
--  imp scott/tiger file=sales.dmp log=imp.log full=Y

-- Connect as scott and run the same SQL statement
connect scott/tiger
set pages 9999
set lines 150
set echo on
col sql_text format a42
col parsing_schema_name format a14
col sql_handle format a20


-- drop the IND_SALES_PROD_QTY_SOLD index in the scott schema

drop index IND_SALES_PROD_QTY_SOLD;

Select count(s.quantity_sold) total_sales
From   Sales s
Where  s.prod_id=30;

select * from table(dbms_xplan.display_cursor());
 
Select SQL_HANDLE, SQL_TEXT, PARSING_SCHEMA_NAME, PLAN_NAME, ENABLED, ACCEPTED
From   dba_sql_plan_baselines
Where  sql_text like 'Select count(s.quantity_sold%';

-- 6. Accept new plan in the baseline and prove it is used

-- Accept new plan

variable evol_out clob

execute :evol_out :=  dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SQL_4bdba935d6456bd6', -
                                   plan_name=>'SQL_PLAN_4rqx96rb4auyq4925533e', - 
                                   verify=>'NO');

Select SQL_HANDLE, SQL_TEXT, PARSING_SCHEMA_NAME, PLAN_NAME, ENABLED, ACCEPTED
From   dba_sql_plan_baselines
Where  sql_text like 'Select count(s.quantity_sold%';

-- 7. check that the baseline is now used in schema SCOTT

Select count(s.quantity_sold) total_sales
From   Sales s
Where  s.prod_id=30;

select * from table(dbms_xplan.display_cursor());