Oracle PL/SQL script to simulate bind variables, time queries and generate execution plans.

oracle_bind_variable_plan.sql

set timing on;
set linesize 120;


--
-- Bind Variable Setup
--
variable Bind1 number;
variable Bind2 varchar2(2000);

exec :Bind1 := 0;
exec :Bind2 := 'Zero';


--
-- Query
--
SELECT
    *
FROM
    my_table
WHERE
    num_column = :Bind1
AND
    char_column = :Bind2;

--
-- Query Execution Plan
--
explain plan for
SELECT
    *
FROM
    my_table
WHERE
    num_column = :Bind1
AND
    char_column = :Bind2;

select * from table(dbms_xplan.display);