Bind Variables
Shareable Cursor
Using bind variables can make statements with minor differences become literally the same so as to share the same cursor when executions. That means, SQL engine doesn't have to parse those similar statements every time they are executed, it reuses the shared cursor to service millions of executions.
Unshareable Cursors
Otherwise, you may run into some problems if you're using unshareable cursors.
ORA-04031
In contrast, dynamic and unshareable SQL statements with tiny differences are regarded as different ones. That makes SQL engine keep parsing statements and finding new private SQL areas to be located. Eventually, countless cursors with similar statements exhausts your shared pool. Moreover, you might see errors like ORA-04031: unable to allocate 4216 bytes of shared memory.
library cache pin
A long wait for library cache pin for each different cursor can be found during execution time.
CURSOR_SHARING
Although we can save this kind of SQL statements by setting CURSOR_SHARING to FORCE by ALTER SESSION or ALTER SYSTEM, using static and bind variables is always a good thing from performance and security perspectives. Beside, Oracle has warned that Do Not Use CURSOR_SHARING = FORCE as a Permanent Fix.
Even though you didn't see any error, chances are the limitation of OPEN_CURSORS may eventually run out, you need to raise OPEN_CURSORS to solve ORA-01000.
Use Bind Variable in SQL*Plus
We can use it directly in SQL*Plus. For the following example, we don't have to recompose the statement, instead we change the bind variable.
SQL> variable LNAME varchar2(25);
SQL> exec :LNAME := 'Rowe'
PL/SQL procedure successfully completed.
SQL> select first_name from employees where last_name = :LNAME;
FIRST_NAME
--------------------
Scott
SQL> exec :LNAME := 'Sullivan';
PL/SQL procedure successfully completed.
SQL> select first_name from employees where last_name = :LNAME;
FIRST_NAME
--------------------
Martha
As you can see, we repeatedly use literally the same statement to service all executions. Let's see what we have by querying V$SQL.
SQL> select sql_id from v$sql where sql_text like 'select first_name from employees where last_name = :LNAME';
SQL_ID
-------------
1rwyzpf20abf0
1rwyzpf20abf0
As as result, we saw two executions using the same SQL_ID which is a SQL identifier of the parent cursor in the library cache.
Use Bind Variable in PL/SQL
The following is an anonymous PL/SQL block. In which, a query statement binds a variable :1 that can easily be changed in value for further executions. Furthermore, the code looks neat and understandable, not to mention the performance gain if we use it in a loop.
SQL> set serveroutput on;
SQL> declare
2 v_firstname varchar2(25);
3 v_lastname varchar2(25);
4 v_sql varchar2(250) := 'select first_name from employees where last_name = :1';
5 begin
6 v_lastname := 'Rowe';
7 execute immediate v_sql into v_firstname using v_lastname;
8 dbms_output.put_line('The person whose last name is ' || v_lastname || ' is called ' || v_firstname || '.');
9 v_lastname := 'Sullivan';
10 execute immediate v_sql into v_firstname using v_lastname;
11 dbms_output.put_line('The person whose last name is ' || v_lastname || ' is called ' || v_firstname || '.');
12 end;
13 /
The person whose last name is Rowe is called Scott.
The person whose last name is Sullivan is called Martha.
PL/SQL procedure successfully completed.
Nevertheless, human readiness is also an important factor that can evaluate the quality of PL/SQL code, not just only performance is concerned.
Further reading: How to Find out Badly Written Statements for Tuning.