When passing bind variables it is best to use the native data type. Bind a DATE as a DATE, don't pass in a string and convert it. This test case is for Oracle SQL

Performance benefits of using DATE types when binding date variables for Oracle SQL.sql

/*
This test case shows the performance advantages of binding using the native SQL
data type, instead of passing in a string and converting it.

If the binds use DATE types, then Oracle can better understand the values, better
estimate the number of rows returned (the cardinality), and can choose better
execution plans.

Specifically, using native DATEs allows Oracle to use adaptive cursor sharing, so
that Oracle can create two plans for the same statement.  The optimizer can use
a full table scan for a popular value, and an index range scan for a rare value.
*/


--Create and populate a table with skewed data.
--The date column has many values for 2000-01-1 and few values for 2000-01-02.
create table skewed_table(a number, b date);

insert into skewed_table
select level, date '2000-01-01'
from dual connect by level <= 10000;

insert into skewed_table
select level, date '2000-01-02'
from dual connect by level <= 10;

--Create an index on the date column.
create index skewed_table_idx on skewed_table(b);

--Select from the column first, or Oracle won't create a histogram later.
--(Oracle won't create a histogram if column isn't used.)
select * from skewed_table where b = date '2000-01-01';

--Gather statistics.
begin
	dbms_stats.gather_table_stats(user, 'SKEWED_TABLE');
end;
/

--Run two SQL statements that use the proper data types.
declare
	v_date1 date := date '2000-01-01';
	v_date2 date := date '2000-01-02';
	v_count number;
begin
	select count(a) into v_count from skewed_table query1 where b = v_date1;
	select count(a) into v_count from skewed_table query2 where b = v_date2;
end;
/

--The following execution plans show that Oracle can choose the best plan when
--the proper data type is used.  A FULL TABLE SCAN is used for a popular value
--that returns a lot of rows, and an INDEX RANGE SCAN is used for a rare value
--that returns only a few rows.


--Query 1: Full table scan.
select *
from table(dbms_xplan.display_cursor(sql_id =>
	(
		select sql_id
		from v$sql
		where lower(sql_fulltext) like 'select%query1%'
			and sql_fulltext not like '%quine%'
	), cursor_child_no => null
));


--Query 2: Index range scan
select *
from table(dbms_xplan.display_cursor(sql_id =>
	(
		select sql_id
		from v$sql
		where lower(sql_fulltext) like 'select%query2%'
			and sql_fulltext not like '%quine%'
	), cursor_child_no => null
));


--Run two SQL statements that use strings for dates.
declare
	v_date1 varchar2(4000) := '2000-01-01';
	v_date2 varchar2(4000) := '2000-01-02';
	v_count number;
begin
	select count(a) into v_count from skewed_table query3 where b = to_date(v_date1, 'YYYY-MM-DD');
	select count(a) into v_count from skewed_table query4 where b = to_date(v_date2, 'YYYY-MM-DD');
end;
/


--Now the execution plans are both FULL TABLE SCANS.  Oracle can no longer
--accurately predict the number of rows based on the bind variable.


--Query 3: Full Table Scan.
select *
from table(dbms_xplan.display_cursor(sql_id =>
	(
		select sql_id
		from v$sql
		where lower(sql_fulltext) like 'select%query3%'
			and sql_fulltext not like '%quine%'
	), cursor_child_no => null
));


--Query 4: Full Table Scan.
select *
from table(dbms_xplan.display_cursor(sql_id =>
	(
		select sql_id
		from v$sql
		where lower(sql_fulltext) like 'select%query3%'
			and sql_fulltext not like '%quine%'
	), cursor_child_no => null
));