Oracle - Parameterized Query has EXECUTIONS = PARSE_CALLS

Question

We have a .NET application that speaks with Oracle 10g. Our DBA recently pulled out a list of queries in which executions are parse_calls. We suggested that this will help us find all the non-parameterized queries in our code.

Unexpectedly, the following query appeared at the top of this list, with 1,436,169 executions and 1,436,151 sessions:

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 are quotes equal to parse_calls for this request?

Answer 1

the number of times that the request is parsed depends entirely on the calling application. The request will be processed once every time the application requests a database for its analysis.

:

• HARD parse - , . , , , , , .. ( ).

• SOFT- - , . , , ( , , )

, , , Oracle . , , , Oracle .

, , , , () .

Answer 2

, , .NET , ​​:

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

:

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

: someId's/anotherId

, .