RE: Reduce parsing
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "tracy.rahmlow@xxxxxxxx" <tracy.rahmlow@xxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 20 Mar 2009 17:32:05 -0400
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
Other related posts:
