--
-- Script for force bind-aware cursor sharing blog post
-- 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 change the value of the bind
exec :deptno :=10;
Select count(*), max(empno)
From   emp
Where  deptno =:deptno;

-- And execute the statement a second time with the new bind values
Select count(*), max(empno)
From   emp
Where  deptno =:deptno;

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

connect /as sysdba
alter system flush shared_pool;

connect sh/sh

set pages 9999
set lines 150
set echo on

exec :deptno := 9;

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

exec :deptno :=10;
Select /*+ BIND_AWARE */ count(*), max(empno)
From   emp
Where  deptno =:deptno;

-- If we now check in v$SQL
Select child_number, substr(sql_text,1,40), executions, is_bind_sensitive, is_bind_aware, is_shareable
From   v$sql
where  sql_id in ('09a1uvyty82b1');

Select * from table(dbms_xplan.display_cursor('09a1uvyty82b1',1,'basic');
--