Oracle - Parameterized Query has EXECUTIONS = PARSE_CALLS

Question

We have a .NET application talking to Oracle 10g. Our DBA recently pulled a list of queries where executions is equal to parse_calls. We assumed that this would help us find all of the unparameterized queries in our code.

Unexpectedly, the following query showed up near the top of this list, with 1,436,169 executions and 1,436,151 parses:

SELECT bar.foocolumn
  FROM bartable bar,
       baztable baz
 WHERE bar.some_id = :someId
   AND baz.another_id = :anotherId
   AND baz.some_date BETWEEN bar.start_date AND (nvl(bar.end_date, baz.some_date + (1/84600)) - (1/84600))

Why is executions equal to parse_calls for this query?

Answer 1

Likely because the .NET programmers have chosen to code a routine like this in pseudocode:

Loop over someId's and anotherId's
  parse(your_query);
  bind someId and anotherId to your_query;
  execute(your_query);
  close(your_query);
end loop;

where they should have coded it like this:

parse(your_query);
Loop over someId's and anotherId's
  bind someId and anotherId to your_query;
  execute(your_query);
end loop;
close(your_query);

Or even better: use a single query to retrieve for all data for all someId's/anotherId's

Regards, Rob.

Answer 2

Hi Cory,

the number of times a query is parsed is entirely dependent upon the calling application. A query will be parsed once each time the application asks the database to parse it.

Server side, there are different kinds of parse:

• HARD parse -- the query has never been seen before, isn't in the shared pool. We must parse it, hash it, look in the shared pool for it, don't find it, security check it, optimize it, etc (lots of work).

• SOFT parse -- the query has been seen before, is in the shared pool. We have to parse it, hash it, look in the shared pool for it and find it (less work then a hard parse but work none the less)

Most likely in your case you are creating the statement once per session and then discard it so Oracle has to parse it each time. However, thanks to parameterizing, this parse is a soft one and Oracle only gets to the expensive step of optimizing it once.

Still, you can probably cache the statement in your application and reuse it, so as to (soft) parse it only once per session.