Hard Parsing can bring your app down

For years now the first thing I look at in an AWR report is the Hard Parse row in the load profile section, and every now and then it immediately brings a hard parsing problem to my attention.

I am not sure why this is one of the first things I look at - maybe it is because Tom Kyte frequently points out that parsing is one of the top things done wrong in the database, or may it is because I know how much trouble a hard parsing problem can cause, but either way, it is a good thing to check on your database report.

Until recently, I had never actually seen a DB brought down by a hard parsing problem - maybe this is because I had scared any of the developers on my teams into not making that mistake, or maybe I got lucky.

A few weeks ago, I got a call before I arrived into the office indicating a critical production database was performing very poorly, and as it turned out the root cause of this problem was a hard parsing issue. When we checked the ASH report over a 5 minute period, the top waits looked something like:

                                                               Avg Active
Event                               Event Class        % Event   Sessions
----------------------------------- --------------- ---------- ----------
library cache: mutex X              Concurrency          45.13     305.41
latch: shared pool                  Concurrency          26.47     179.12
null event                          Other                23.98     162.30
library cache lock                  Concurrency           1.87      12.67
          -------------------------------------------------------------

Pulling an AWR for the time period indicated 70 hard parses per second! The database had been suffering along for some time in this state, but something happened that pushed it over some threshold, and all of a sudden sessions starting queuing on latches, DB calls starting timing out from the application and people started getting rather unhappy.

The entire problem was caused by one simple stored procedure that looked something like:

begin
  open v_ref_cursor for 'select a, b, c from T where pk_col = '|| i_pk_col;
end;

So simple, but also so wrong. Aside from the hard parsing problem, this procedure is wide open to SQL injection attacks. It is also very easy to fix:

begin
  open v_ref_cursor for 'select a, b, c from T where pk_col = :b1' using i_pk_col;
end;

The morale of the story is this - if you ever find yourself using dynamic SQL constructs in PLSQL, or Java or anywhere, you better be binding any variables into the query, and if you have any doubt, make sure its gets reviewed by your peers.