Reducing Parse Calls in Oracle

I am noticing the parse_calls are equal to the number of executions in our Oracle 11g database.

 select parse_calls, executions
 from v$sql order by parse_calls desc;

Running the above query gives the following result.

"PARSE_CALLS" "EXECUTIONS"
   87480        87480
   87475        87476
   87044        87044
   26662        26662
   21870        21870
   21870        21870

As I'm aware this is a major performance drawback. All of these SQL statements are either stored procedures or using bind variables. I'm also reusing the command objects that are calling the stored procedures from C#.

How do I reduce the number of parse calls in this?

Also, is there some method I can distinguish between hard parses and soft parses?

EDIT:

As @DCookie mentioned I ran the following query on the database.

SELECT s2.name, SUM(s1.value)
FROM v$sesstat s1 join v$statname s2 on s1.statistic# = s2.statistic#
WHERE开发者_StackOverflow社区 s2.name LIKE '%parse count%'
GROUP BY s2.name
ORDER BY 1,2;

The result is as below

"NAME"                         "SUM(S1.VALUE)"
"parse count (describe)"             0
"parse count (failures)"             29
"parse count (hard)"                 258
"parse count (total)"                11471

So the number of hard parses seem to be very low compared to the number of parses. Thanks to everyone for their responses :)

FINAL UPDATE:

The main issue for the parsing was because we had connection pooling turned off in the connection string. After turning on connection pooling I was able to completely resolve the parsing problem.

---

Start with this:

SELECT name, SUM(value)
  FROM v$sesstat s1 join v$statname s2 on s1.statistic# = s2.statistic#
 WHERE s1.name LIKE '%parse count%'
 GROUP BY name
 ORDER BY 1,2;

This will give you the number of hard parses and total parses. The parse_calls values in your query is total parses, hard and soft.

What does your SQL do? Not much cursor processing, mostly single statements? You are getting pretty much a 1-1 ratio of executions to parses, which, if they are soft parses means you are not doing much cursor processing.

EDIT:

Unless you can modify your code to open and hang on to a cursor for each of your SQL statements, and reuse them as much as possible within a session, I don't think you can avoid the parses.

---

A parse call has to occur any time a new cursor is created, even if the statement is in the library cache. It is the parse call that checks the library cache. If the statement is found in the library cache, it is a soft parse.

@DCookie has given an answer to your question about checking hard vs. soft parse count. I expect you will find most parse calls are soft parses. Note that you shouldn't expect the counts returned from v$sysstat to be very close to total parse calls from v$sql, since the former is a count since instance startup and the latter just shows the statements that are currently in the library cache.

The only way to avoid parse calls entirely at all is to keep a handle to an existing cursor, and execute it when needed, binding new values in when appropriate. This will occur sometimes by caching of cursors -- it's out of your explicit control, although I believe there are parameters you can change to affect it. In PL/SQL code you can explicitly hold onto cursors that you create and manage using the DBMS_SQL package. I would expect that C# has corresponding capabilities.

In any case, what you're looking at is probably not a problem. Just because counts seem high in no way implies that parsing is a bottleneck on your system.

First of all, you should check whether the SQL statements with those high parse counts are even in your control. When I did a modified version of your query on one of my systems:

select parse_calls, executions, parsing_schema_name,sql_text
 FROM v$sql
 ORDER BY parse_calls DESC;

I found that the statements with the highest number of parse calls were all recursive SQL parsed by SYS. This may not be the case for you depending on your usage, but it is something to check.