Parsing can start and end the processing of SQL, depending on type of parse. Generally there are 4 types of parsing:

1. No Parse
2. Soft-Soft Parse
3. Soft Parse
4. Hard Parse

If the query doesn't need parsing, the processing of SQL will go directly to EXECUTING phase (however that is rarely the case, we will see in a sec:

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. This is the same example as presviouly but moved the open/parse/close cursor outside.

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);
  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);

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;

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: When you pass a piece of text to Oracle it will do a syntax check to decide if it is legal, then it will search the library cache for a matching text (using a hash value computed from the text). If it finds a textual match Oracle starts the semantic check—checking to see if the new text actually means the same as the existing text (same objects, same privileges, etc.); this is known as cursor authentication. If everything matches, then the session need not optimize the statement again.

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. Oracle will go through the entire three and generate all possible plans. Then Oracle will compare each of the plans to find the best. As you can imagine this requires A LOT OF resources, we will discuss in a bit.