I’m confused about the execute-to-parse ratio in Statspack/automatic workload repository reports. I have observed this ratio as 12.02 percent. My team is suggesting a change to the CURSOR_SHARING parameter value—to SIMILAR—to improve this ratio. Is that a correct approach? Can you please explain what this ratio actually means?
The last part of that question should throw up a bunch of red warning flags. You are looking at a ratio: the execute-to-parse ratio. You feel it is “bad” and would like to fix it, so you suggest changing a parameter to fix it. But then you ask for the ratio to be explained, meaning you’re not really sure what it is, what it means, and if the value you have is bad. Not only that, but the suggestion to change CURSOR_SHARING cannot and will not change the execute-to-parse ratio.
OK, first of all, let’s start with an explanation of what the execute-to-parse ratio is. It is a measure of how many times, on average, your SQL statements are executed rather than parsed. The way this ratio is computed, it will be a number near 100 percent when the application executes a given SQL statement many times over but has parsed it only once. (An application must parse a SQL statement at least once to execute it but needs to parse the SQL statement only once to execute it over and over again.) This ratio will be near 0 percent if the application parses a statement every time it executes it (parse count = execute count). This ratio will go negative if the application parses more often than it executes (and that would definitely be a sign that something is seriously wrong in the application). The formula (from Statspack) is simply
'Execute to Parse %:’, round(100*
(1-:prse/:exe),2)
As you can see, if :prse (parse count) is about the same as :exe (execute count), the execute-to-parse ratio will be 0 percent. If :exe is much larger than :prse, :prse/:exe will be near zero and the execute-to-parse ratio itself will be near 100 percent. If :prse is greater than :exe, the execute-to-parse ratio will go negative (indicating “this is bad”).
So that is, technically speaking, what the ratio is. A number near 100 percent would be great but might not be attainable. A negative number should definitely be avoided—it would indicate that the application actually parses a SQL statement but never executes it. The application developers would need to be educated, because a parse is an expensive operation and their goal is to reduce the number of parse calls—not create extra, unnecessary ones!
So what about the observed percentage, 12 percent? There is room for improvement there, but it cannot come from CURSOR_SHARING. Changing that parameter from its default (and preferred) setting of EXACT to SIMILAR might change the type of parsing happening (the parameter change can convert a hard parse into a soft parse), but it will never reduce the number of parse calls made by the application. After the parameter change, the application will still be calling “prepare statement” to parse SQL—under the covers, it might be a soft parse, but it will still be a parse.
In short, your colleagues’ suggestion would do nothing to alter the execute-to-parse ratio and could, in fact, cause major issues in your already-running system. Don’t even consider changing the CURSOR_SHARING parameter in order to try to fix your execute-to-parse ratio.
The only way to modify the execute-to-parse ratio would be to alter the two variables used in the formula. You can change either (1) the number of times you parse or (2) the number of times you execute.
I vote for the first option. Your application should look for ways to reduce the number of times it parses SQL, and how to do this varies from environment to environment. Using JDBC, for example, you can enable JDBC statement caching (a quick search for “JDBC statement cache” will turn up plenty of information). Using .NET, you can also enable statement caching (a quick search for “Oracle .NET statement cache” will turn up lots of information as well).
My preferred way to improve your execute-to-parse ratio, however, is to move all the SQL out of the client application and into stored procedures. PL/SQL is a statement caching machine—it has, from its very beginning, kept a cache of cursors open for us. When you say “close this cursor” in PL/SQL, PL/SQL tells you, “OK, it’s closed,” but it really isn’t. The PL/SQL engine smartly keeps the cursor open, knowing that you are just going to call that stored procedure and execute that SQL again.
Suppose you have a subroutine in your client application that is called 10,000 times a day. Further, suppose that it executes five SQL statements and those statements are parsed and executed every single time that routine runs. You will be doing 50,000 parse calls and 50,000 executes.
If you were to move those five SQL statements into a stored procedure and execute just one PL/SQL call in the client application—even assuming that you didn’t cache that statement in the client—you would now have
- 10,000 parse calls for the PL/SQL block
- 5 parse calls for the 5 SQL statements
- 10,000 execute calls for the PL/SQL block
- 50,000 execute calls for the SQL in the PL/SQL code
So now you will have 10,005 parse calls and 60,000 executes (plus many fewer round-trips between client and server). The parse calls (be they hard or soft parses) are extremely expensive. Having this one application instance cut down the number of parse calls to 20 percent of what it used to be will have an impact on performance and scalability—a profound impact.
In short, the only way to really affect the execute-to-parse ratio is to change the number of execute or parse calls—and this is something the application developer has to do. No magic parameter can be set.
