Re: Reduce parsing
- From: Cary Millsap <cary.millsap@xxxxxxxxxxxx>
- To: tracy.rahmlow@xxxxxxxx
- Date: Mon, 23 Mar 2009 14:11:38 -0500
The 2:1 parse:exec phenomenon is something we saw a few years ago on a
PowerBuilder application. It's described in Case 3 of Optimizing Oracle
Performance
(pp337–344)<http://books.google.com/books?id=dN805AoUyc8C&printsec=frontcover&dq=millsap+holt&ei=493HScTbI5byygS9rLndDQ#PPA337,M1>.
In that case, there was a compile-time flag over which the developer had
control. The flag controls exactly the behavior that Mark described already.
I don't know if there's a similar flag in VB, but I'm expecting there is.
A couple of things you shouldn't lose sight of... Parse calls are expensive,
whether they're "hard" or "soft." I didn't realize how expensive they are
until I watched a Steve Adams presentation a few years ago. Soft parses
consume lots of Oracle kernel code path, and some of that code path is
serialized by latches. (...Meaning that adding hardware isn't going to
improve performance in high-concurrency situations.)
And, don't forget, every parse call is a network round-trip. No matter how
clever the Oracle kernel gets in short-cutting code path inside itself,
there's no way the kernel can un-spend the time a client program spent to
ship the Oracle kernel a dbcall that it shouldn't have.
Cary Millsap
http://method-r.com
http://carymillsap.blogspot.com
On Mon, Mar 23, 2009 at 12:29 PM, Tracy Rahmlow <tracy.rahmlow@xxxxxxxx>wrote:
>
> I think I failed to differentiate the difference between an actual parse
> and a parse call. Is the intent of the "parse calls" statistic to collect
> parse call "requests" rather than whether or not a parse (hard/soft)
> actually took place (therefore the usage of "call" in the name)? If this is
> true, then perhaps identifying statements with parse calls > executions does
> not necessary equate to a problem (i.e., it may be an issue but more
> research is required)
>
> Minimally, if "2" parse calls were invoked for the session wouldn't the
> second call reuse the open cursor and therefore bypass a soft/hard parse?
> Also, since it is a multi-user environment I assume a shareable cursor is
> found and that only a soft parse is required. Am I understanding this
> correctly?
>
> Outside the batch window, we generally always have more parse calls than
> executions and it appears to be related to many of our VB applications (we
> have a mix of VB6/VB8). Is this the experience of others that use VB or are
> we unique?
>
> Thanks
>
>
>
> *"Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>*
>
> 03/20/2009 04:34 PM
> To
> "tracy.rahmlow@xxxxxxxx" <tracy.rahmlow@xxxxxxxx>, "oracle-l@xxxxxxxxxxxxx"
> <oracle-l@xxxxxxxxxxxxx> cc
> Subject
> RE: Reduce parsing
>
>
>
>
> Hi Tracy,
>
> The ‘parse calls’ statistic is count of how many times the client program
> calls the database ‘PARSE()’ call. The **only** way to reduce that, is to
> modify your client program to make fewer parse calls.
>
> It’s possible to reduce the impact of each parse call, by setting
> ‘session_cached_cursors’ to a reasonable number, perhaps 50 or 100, and
> possibly ‘cursor_space_for_time=true’, but this will NOT change the parse
> calls statistic.
>
> As to why parse calls is 2x the executions, the following is pure
> speculation, since I’m not a VB programmer.
>
> In an ideal world, the program would parse each SQL just once, and then
> execute as many times as needed, possibly binding new values to bind
> variables before each call. This is what PL/SQL does. Many applications
> will parse once per execution. This is not uncommon for clients such as
> SQL*Plus, for example. The really bad programs do things like parse a
> statement to see if the database connection is alive, or to validate that a
> dynamically generated SQL is syntactically correct, and then parse again to
> execute. I’ve even seen/heard of code that parses a SQL and then NEVER
> executes it. So, anyhow, you end up with 2 parses/execution. However,
> sometimes, depending on the language, and the library used for Oracle
> communication, it’s possible to disable the SQL syntactic check or the
> database connection check, which would drop the number of parses from 2x
> executions to equal to executions. What the exact root cause is in VB, and
> whether or not it can be disabled, I have no idea.
>
> Hope that helps,
>
> -Mark
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Tracy Rahmlow*
> Sent:* Friday, March 20, 2009 5:09 PM*
> To:* oracle-l@xxxxxxxxxxxxx*
> Subject:* Reduce parsing
>
>
> The following was identified within v$sql in a 10.2.0.4 database -
>
> SQL> select parse_calls, executions, sql_text, module from v$sql
> 2 where sql_text like '%INTADM.MASK_DATA(:V%'
> 3 and module = 'POLDOC.EXE';
>
> PARSE_CALLS EXECUTIONS
> ----------- ----------
>
> SQL_TEXT
>
>
> --------------------------------------------------------------------------------
> MODULE
>
> ----------------------------------------------------------------
>
> 206855 103410
>
> begin :V00001 := INTADM.MASK_DATA(:V00002,:V00003,:V00004); end;
>
> POLDOC.EXE
>
> The referenced object is a function that is parsed 2x more than it is
> executed. This is generated from a VB application. We have other function
> calls that behave similarly (i.e., 2:1 parse to execute). What would cause
> this statement to be parsed more than executed? Are there any options
> (other than removing the call) to reduce the number of parse calls relative
> to the executions?
>
> Thanks
> ******************************************************************************
>
>
> "This message and any attachments are solely for the intended recipient and
> may contain
> confidential or privileged information. If you are not the intended
> recipient, any disclosure,
> copying, use, or distribution of the information included in this message
> and any attachments is
> prohibited. If you have received this communication in error, please notify
> us by reply e-mail and
> immediately and permanently delete this message and any attachments. Thank
> you."
>
> ******************************************************************************
>
Other related posts:
