RE: Reduce parsing
- From: Tracy Rahmlow <tracy.rahmlow@xxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 23 Mar 2009 12:29:52 -0500
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: