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

connect sh/sh

set pages 9999
set lines 150
set echo on

-- Setup
-- First we need to create the emp table with a specific data distribution.
drop table emp purge;

create table emp
(
empno   number,
ename   varchar2(20),
phone   varchar2(20),
deptno  number
);

-- populate the table
insert into emp
  with tdata as
       (select rownum empno
         from all_objects
         where rownum <= 1000)
  select rownum,
         dbms_random.string ('u', 20),
         dbms_random.string ('u', 20),
         case
                when rownum/100000 <= 0.001 then mod(rownum, 10)
                else 10
         end
    from tdata a, tdata b
   where rownum <= 100000;

-- gather statistics
exec dbms_stats.gather_table_stats(null, 'EMP', METHOD_OPT => 'FOR COLUMNS DEPTNO SIZE 10', CASCADE => TRUE);

-- create an index
create index emp_i1 on emp(deptno);

-- check histogram has been created on DEPTNO (10 buckets)
select column_name, histogram, num_buckets
from user_tab_columns
where table_name='EMP';

--declare a value for our bind variable :deptno for our queries
variable deptno number;
exec :deptno := 9

-- Lets begin with a simple query on the emp table without the BIND_AWARE hint. Cursor will not be bind aware
Select count(*), max(empno)
From   emp
Where  deptno =:deptno;

-- Now lets add a BIND_AWARE hint and execut again. This time the cursor will be marked bind aware immediately
Select /*+ BIND_AWARE */ count(*), max(empno)
From   emp
Where  deptno =:deptno;

-- If we now check in v$SQL we will see two cursors one IS_BIND_AWARE and one that is not
Select sql_id, substr(sql_text,1,40), executions, is_bind_aware
From   v$sql
where  sql_id in ('09a1uvyty82b1', '4j5y55fd5rx77');

-- Now lets create a SQL patch for this query
connect /as sysdba
begin
  sys.dbms_sqldiag_internal.i_create_patch(
      sql_text  => 'Select count(*), max(empno) From   emp Where  deptno =:deptno',
      hint_text => 'BIND_AWARE',
      name      => 'test_patch');
end;
/

connect sh/sh
set pages 9999
set lines 150
set echo on

-- Now lets check the SQL patch is working
Select count(*), max(empno)
From   emp
Where  deptno =:deptno;

Select sql_id, substr(sql_text,1,40), executions, is_bind_aware
From   v$sql
where  sql_id in ('09a1uvyty82b1', '4j5y55fd5rx77');

--Lets try a different SQL statement this time and add a different hint
explain plan for
Select count(*), max(empno)
From   (Select *
        From   emp
        Where  deptno = 10);

Select * From table(dbms_xplan.display(format=>'basic +note'));

-- The initial plan shows a FTS we want an index access so lets add a hint
explain plan for
Select /*+ INDEX(@SEL$2 emp) */ count(*), max(empno)
From   (Select *
        From   emp
        Where  deptno = 10);

Select * From table(dbms_xplan.display(format=>'basic +note'));

-- Now we have the plan we want lets create a SQL patch for that
connect /as sysdba
begin
  sys.dbms_sqldiag_internal.i_create_patch(
      sql_text  => 'Select count(*), max(empno) From   (Select * From   emp Where  deptno = 10)',
      hint_text => 'INDEX(@SEL$2 emp)',
      name      => 'test_patch2');
end;
/


connect sh/sh
set pages 9999
set lines 150
set echo on

--Lets see if the SQL patch has an effect
explain plan for
Select count(*), max(empno)
From   (Select *
        From   emp
        Where  deptno = 10);

Select * From table(dbms_xplan.display(format=>'basic +note'));

--