Bind variables are extremel useful in SQL, because they prevent hard parses. In a nutshell, statment is given as a tamplate with place holders. When executing the cursor, these place holders are filled with bind varialbes. Because of that, when a cursor is parsed, it is parsed with a place holders and not with the actual varialbes. Because of that, Oracle (and not only Oracle) Will generate an SQL plan which can be re-used with different search setting.

Of course, if you don't use “where” clause, bind variables are kinda useless and Oracle will do the most expensive execution anyway. Some databases like MySQL, have very little of a cursor sharing and most of their parsing is hard parse.

For Oracle there are 4 types of parsing:

• Hard Parse - The most expensive one
• Soft Parse - Still Expensive
• Soft-Soft Parse - Less Expensive
• No-Parse - The best you can have

It is obvious to notice, that we want always to have no-parse. Unfortunetely that is rarely the case

Let's get through all of them:

This one is the easiest one to understand. It’s also the most expensive one. Whenever the fist time oracle run a SQL, there is no plan about this SQL in the memory. Oracle needs to create a plan(optimizing) and save it in library cache. That’s the hard parse.

Here is a good picture to distinguish bitween the Hard parse and the Softparse:

That shared area check, can be seen below. If it succeed, we will have a soft parse, if not, we will have a hard parse and we will continue with the most expensive parts:

• Optimization - Generate Multiply Execution Plans
• Row Source Generation - Generate a query plan.

Let's example of a query which will always do a hard parse the first time, given that we change the values each time:

CREATE OR REPLACE FUNCTION get_count(p_act_id accounts.act_id%TYPE)
return NUMBER is
  l_count NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO l_count
  FROM orders
 WHERE order_act_id = p_act_id;
 RETURN l_count;
END get_count; 

exec get_count(100); <- Hard Parse
exec get_count(200); <- Hard Parse
exec get_count(300); <- Hard Parse
exec get_count(400); <- Hard Parse

Of course, there are database settings, which we can use, to FORCE the optimized to use its magic, but in general using bind varialbes is always better.

Here is how this could've have been done using bind varialbes with Native Dynamic SQL:

CREATE OR REPLACE FUNCTION get_count(p_column VARCHAR2,
                                     p_value NUMBER)
return NUMBER is
  l_count NUMBER;
  l_query VARCHAR2(200);
BEGIN
 l_query := 'SELECT COUNT(*) FROM orders where '||p_column||' = :col_value';
 EXECUTE IMMEDIATE l_query into l_count USING p_value;
                                                 ^
                                                 |
                                           Bind Variable
 RETURN l_count;
END get_count; 

exec get_count(order_act_id ,100); <- Hard Parse
exec get_count(order_act_id ,200); <- Soft Parse
exec get_count(order_act_id ,300); <- Soft Parse
exec get_count(order_act_id ,400); <- Soft Parse

Of course there is a different ways, using DBMS_SQL or Native Dynamic SQL, whatever you choose, some gives you more control, some not. However USE BIND VARIABLES IN EVERY CASE.

This one is the second easiest to understand. It’s also the second expensive one. When you submit a SQL, oracle searches the library cache, and it found a plan matches your request exactly. Oracle didn’t need to create a plan but need to do the search work. Some notes from the book about the work oracle need to do:

So if the 2nd session, finds that Plan in the shared area, it will re-use it.

This one is still called soft parse in Oracle’s document. I don’t think Oracle give it a proper name to make it easy to understand. Sometimes DBA call it soft-soft parse. When you run a SQL a few times in the same session (with session_cache_cursors enabled), Oracle will create a short cut in your session memory, so when you run it next time, oracle don’t need to do the search work. It knows where it’s already. It’s cheaper in cost than soft parse.

Here is an example in the book(although it’s using pl/sql cursor cache, the effect is the same):

for i in 1..1000 loop
  m_cursor := dbms_sql.open_cursor;
  dbms_sql.parse(
                 m_cursor,
                 'select n1 from t1 where id = :n',
                 dbms_sql.native
                 );
  dbms_sql.define_column(m_cursor,1,m_n);
 
  dbms_sql.bind_variable(m_cursor, ':n', i);
  m_rows_processed := dbms_sql.execute(m_cursor);
  if dbms_sql.fetch_rows(m_cursor) > 0 then
    dbms_sql.column_value(m_cursor, 1, m_n);
  end if;
 
  dbms_sql.close_cursor(m_cursor);
end loop;

The most significant difference of this one from the previous three is it will not increase the parse count. The reason is obviously “No parse at all ” – execute the code by using given bind variables. It the best if a same SQL run multiple times. Unfortunetely that can only be done within the same program. It is VERY BAD idea to re-use an open cursor as it will violate couple security checks by Oracle.

m_cursor := dbms_sql.open_cursor;
dbms_sql.parse(
               m_cursor,
               'select n1 from t1 where id = :n',
               dbms_sql.native
               );
dbms_sql.define_column(m_cursor,1,m_n);
 
for i in 1..1000 loop
  dbms_sql.bind_variable(m_cursor, ':n', i);
  m_rows_processed := dbms_sql.execute(m_cursor);              <-We re-execute the same cursor, ergo....no parsing of the cursor.
  if dbms_sql.fetch_rows(m_cursor) > 0 then
    dbms_sql.column_value(m_cursor, 1, m_n);
  end if;
end loop;
 
dbms_sql.close_cursor(m_cursor);