Reduce parsing requests in Oracle

Question

I notice that 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; 

Executing the above query gives the following result.

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

As I understand it, this is a serious lack of performance. All of these SQL statements are stored procedures or bind variables. I also reuse command objects that invoke stored procedures with C #.

How to reduce the number of parsing sessions?

Also, is there any method I can distinguish between hard parsing and soft parsing?

EDIT

As @DCookie mentioned, I ran the following query in the database.

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

Result below

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

Thus, the number of hard partitions seems very low compared to the number of parses. Thanks to everyone for their answers :)

FINAL UPDATE:

The main problem for parsing was that the connection pool was disconnected in the connection chain. After turning on the connection pool, I was able to completely solve the problem of parsing.

Answer 1

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 a number of hard parses and summary analyzes. The parse_calls values ​​in your request are complete analysis, hard and soft.

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

EDIT:

If you cannot change your code to open and insert a cursor for each of your SQL statements, and use them as much as possible in a session, I don’t think you can avoid parsing.

Answer 2

A parsing call should occur every time a new cursor is created, even if the statement is in the library cache. This is a parsing call that checks the library cache. If the statement is found in the library cache, this is soft parsing.

@DCookie answered your question about checking hard and soft parses. I expect you to find that most parsing sessions are soft showdowns. Note that you should not expect the counters returned from v$sysstat to be very close to the general parsing calls from v$sql , since the former is the count since the instance was started, and the latter just shows the statements that are currently time are in the library cache.

The only way to avoid parsing at all is to keep the handle of the existing cursor and execute it if necessary, binding new values ​​when necessary. This sometimes happens by caching cursors - this is from your explicit control, although I believe that there are parameters that you can change to affect it. In PL / SQL code, you can explicitly hold the cursors that you create and manipulate using the DBMS_SQL package. I would expect C # to have the appropriate capabilities.

In any case, what you are looking at is probably not a problem. Just because the counts seem high doesn't mean that parsing is a bottleneck in your system.

First of all, you should check if the SQL statements are with these high parses even in your control. When I made a modified version of your request 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 most parsing parses were recursive SQL processed by SYS. It may not be for you depending on your use, but there is something to check.