--
-- setup script for isecond blog post on SQL Patach
-- Author: Maria Colgan maria.colgan@oracle.com (@SQLMaria)

connect scott/tiger

set pages 9999
set lines 150
set echo on

-- Setup a table called test and populate it with some basic data
Drop table test purge;
Create table test (name varchar2(50), id number);

insert into test values('Maria','10000');
insert into test values('Maria','10000');
insert into test values('Maria','10000');
insert into test values('Maria','10000');
insert into test values('Maria','10000');
insert into test values('Maria','10000');
insert into test values('Maria','10000');
insert into test values('Maria','10000');
insert into test values('Maria','10000');
insert into test values('Maria','10000');
insert into test values('Maria','10000');
insert into test values('Maria','10000');
insert into test values('Maria','10000');
insert into test values('Maria','10000');
insert into test values('Maria','10000');
insert into test values('Maria','10000');
insert into test values('Maria','10000');
insert into test values('Maria','10000');
insert into test values('Maria','10000');
insert into test values('Maria','10000');

commit;
-- enable SPM auto plan capture
alter session set optimizer_capture_sql_plan_baselines=true;

-- Issue a basic DML statement on our test table
UPDATE /*test1*/ test a SET a.name='zjt'
WHERE  a.id > 6000
AND    rownum< 2;

-- Now lets check how the cursor looks for our statement
SELECT sql_text,sql_id,executions,hash_value,child_number
FROM   v$sql
WHERE  sql_text like 'UPDATE /*test1*/%';

-- Lets run the statement again
UPDATE /*test1*/ test a SET a.name='zjt'
WHERE  a.id > 6000
AND    rownum< 2;

--Now if we check the shared pool we will see two executions of the cursor
SELECT sql_text,sql_id,executions,hash_value,child_number
FROM   v$sql
WHERE  sql_text like 'UPDATE /*test1*/%';

--But if we run the statement for the third time
UPDATE /*test1*/ test a SET a.name='zjt'
WHERE  a.id > 6000
AND    rownum< 2;

--And we check the shared pool we see we have two cursors now why?
SELECT sql_text,sql_id,executions,hash_value,child_number
FROM   v$sql
WHERE  sql_text like 'UPDATE /*test1*/%';

pause
-- A SQL plan baseline has been created for our statements
select child_number, SQL_PROFILE, SQL_PATCH, SQL_PLAN_BASELINE from v$sql where sql_id='cuqcxr9jurqgb';

Select signature, sql_text, origin
From dba_sql_plan_baselines
Where sql_text like 'UPDATE /*test1*/ test %';

connect /as sysdba

Show parameter baseline

--