3

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?

Improve this question
3
  • 1
    I think we'd need the query your DBA used to determine the parse calls count to distinguish between hard and soft parses. Also it would be helpful to know the SGA size to eliminate the obvious problem of the SQL just aging out of the shared pool before it could be reused. Commented May 11, 2010 at 14:02
  • I asked him to pull the list of queries again with parses broken out into soft & hard. Not sure about SGA size, but I will ask him about that as well :) Commented May 11, 2010 at 14:31
  • +1 to all for the interesting q & a's Commented May 11, 2010 at 14:43

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Ah, that helps, and I think it answers the question I asked in the comments on Rob's answer as well. We are building up the query string, OracleCommand object, etc. each time we run the query. That sounds like it would result in a soft parse each time we execute it. If we hung onto our OracleCommands in an application cache, would that save the parases? Hanging onto database resources in the .NET app doesn't sound good on the face of it. We're always being told to Dispose() database objects as soon as we're done with them.
@Cory: Each time you build a OracleCommand object from a character string the parse counter goes up. Only you can tell if this work is superfluous. It could be a good idea to cache the statements that are most likely to be executed a lot of times afterwards.
Or you can switch to using PL/SQL instead of .NET, in which case the session cursor cache and the PL/SQL cursor cache takes care of this stuff automatically. I guess that's not an option :-)
@Rob: agreed -- Also in PL/SQL you hardly ever need to build a query from a string :)
3

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.

Improve this answer

5 Comments

It doesn't look like the .NET code in question is looping around the query in question. We're using System.Data.OracleClient and doing the following: 1. build a query string 2. add parameters to a command 3. execute a reader (yes, it should be ExecuteScaler() since we're expecting a single value back). Would that type of usage line up with the first block of pseudocode above?
@Rob - but wouldn't either case result in the SQL being found in the shared pool?
@dpbradley: yes, that's why most parse calls are soft parse calls. But parse calls nonetheless.
@Cory: if there is no loop, then you just execute it very often and you should follow Vincent's advice: cache the cursor and reuse it.
You guys were extremely helpful. If I could have accepted both answers I would have. I definately have some things to look over and discuss with the other developers and our DBA. Thank you!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.