-- reset the shared pool for the demostration, do ot run this at a production system, only maybe with your local XE
alter system flush shared_pool ;
-- lets create a bind variable at sql*plus and bind 100 to it
variable x number;
exec :x := 100 ;
-- lets get first name from employees table for employee_id 100
select first_name from employees where employee_id = :x ;
FIRST_NAME
----------
Steven
-- and check the shared pool for our first query
SELECT a.sql_text, parse_calls, executions FROM v$sqlarea a
WHERE upper(a.sql_text) LIKE upper(’%select%emp%employee_id%’) and upper(a.sql_text) NOT LIKE upper('%sqlarea%') ;
SQL_TEXT
-------------------------------------------------------
PARSE_CALLS EXECUTIONS
----------- ----------
select first_name from employees where employee_id = :x
1 1
-- lets bind 110
exec :x := 110 ;
-- and get first name from employees table for employee_id 110
select first_name from employees where employee_id = :x ;
FIRST_NAME
----------
John
-- and check the shared pool for our first query
SELECT a.sql_text, parse_calls, executions FROM v$sqlarea a
WHERE upper(a.sql_text) LIKE upper(’%select%emp%employee_id%’) and upper(a.sql_text) NOT LIKE upper('%sqlarea%') ;
SQL_TEXT
-------------------------------------------------------
PARSE_CALLS EXECUTIONS
----------- ----------
select first_name from employees where employee_id = :x
2 2
-- lets bind 110
exec :x := 120 ;
-- carefull this time I changed the query, upper SELECT FROM and WHERE
SELECT first_name FROM employees WHERE employee_id = :x ;
FIRST_NAME
-----------
Matthew
SELECT a.sql_text, parse_calls, executions FROM v$sqlarea a
WHERE upper(a.sql_text) LIKE upper(’%select%emp%employee_id%’) and upper(a.sql_text) NOT LIKE upper('%sqlarea%') ;
SQL_TEXT
-------------------------------------------------------
PARSE_CALLS EXECUTIONS
----------- ----------
SELECT first_name FROM employees WHERE employee_id = :x
1 1
select first_name from employees where employee_id = :x
2 2

Two important things I wanted to show here;
1- The first query experienced a hard-parse and since the second query was using bind variable and exactly the same query experienced a soft-parse,
2- Using bind variables is not enough to limit hard parsing overhead, you also need to share your application codes. The third query was different from the first two, SELECT FROM and WHERE was written upper. In order to overcome this setting development standarts and favoring package usage is needed, a function called fnc_get_first_name_by_employee_id for everyone.

SELECT DATATYPE_STRING, VALUE_STRING
FROM v$sqlarea a, V$SQL_BIND_CAPTURE b
WHERE upper(a.sql_text) LIKE upper(’%select%emp%employee_id%’) and upper(a.sql_text) NOT LIKE upper('%sqlarea%') and
a.HASH_VALUE = b.HASH_VALUE and b.ADDRESS = a.ADDRESS;
DATATYPE_STRING
----------------
VALUE_STRING
----------------
NUMBER
120
NUMBER
100

To show the cost of no binding I will use two examples, first with pl/sql and second with jdbc. Both will have a loop yo simulate a heavy On-line Transaction Processing(OLTP) system, like 1000 of people executing the same query.

– DATE type stored as seven bytes; “century, year, month, day, hour, minute, second”,
– VARCHAR2 type stored as its length,
– NUMBER type uses variable-length scientific notation;
* Oracle stores a number in base 100 format.
* Each byte can store 2 digits.
* One byte is reserved for the exponent.
* When a negative number is stored, an additional byte is required for the sign (-).
* NUMBER(10) and NUMBER will take the same amount of space.
* They will also shrink to the minimum size required to store the contained data.
* The maximum precision for a number is NUMBER(38)

select vsize(1100) from dual;
VSIZE(1100)
-----------
2 /* 2 bytes : Note that the number is stored as (11*10exp2) Thus it requires 1 byte for the two digits (11) and one byte for the exponent(2) */
select vsize(-10.02) from dual;
VSIZE(-10.02)
————-
4 /* 4 bytes : One for sign, 2 for digits(1002) and one for exponent(-2) */