Oracle Core # Hard parse, soft parse, soft soft parse, no parse at all

I am reading Jonathan Lewis’s latest book Oracle Core recently.

I’d like to summary all kind of parses from chapter Parsing and Optimizing:

1. Hard parse.

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.

2. Soft parse.

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.

3. Soft soft parse.

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;

4. No parse at all (Holding Cursors)

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

I hope you have a clear picture of what all these parse are. The examples are using pl/sql, but you can do the same in other languages, such as Java, Perl, etc.