[DB-SIG] SQL statement parse for Oracle

Anthony Tuininga anthony@computronix.com
Fri, 14 Dec 2001 10:27:42 -0700


No, the reparse does take place. It may find something in the cache 
which reduces the cost considerably, but it does not eliminate it like 
avoiding the call to parse would in fact do. The only other possibility 
I can think of, is to keep a copy of the string that was last parsed 
around and then perform a compare internally to determine if the string 
matches and if so, don't bother with the parse. That method, however, 
adds overhead to all calls whereas the introduction of a parse() and 
executeparsed() method eliminates the problem in the cases that matter 
(high number of iterations executed).

Anthony

Eugene V . Dvurechenski wrote:

>btw, Oracle should not reparse a statement if it matches anything 
>that was already parsed. hence, if you use bind variables you have
>good chances to get your statement cached and avoid extra times
>to parse it again. well, in case of "assembled" statements you
>may loos such a feature...
>
>On Fri, Dec 14, 2001 at 09:28:24AM +0200, Jekabs Andrushaitis wrote:
>
>>In the Oracle world SQL statement parsing can take considerable
>>
>CPU/Block IO
>
>>for complex statements. Well, I am using pre-historic Oracle DB module
>>(looks like
>>some OLD DCOracle to me:), however I did not see anything related to
>>
>this in
>
>>DBAPI too.
>>What I am talking about is API to allow to parse statement for a
>>
>cursor (for
>
>>Oracle
>>OCI it is done by 'oparse' calls) without executing it. It could be
>>
>used to
>
>>speed
>>up things in situation where same statement has to be executed several
>>
>times
>
>>with different bind variables for example. Or when you want
>>
>application uses
>
>>persistand DB connection and you can create several cursors for each
>>operation
>>you need to perform thus eliminating cost of parsing each statement
>>
>over and
>
>>over
>>again...
>>
>>>From DB-API's point of view it could be Python methods for cursor
>>
>object:
>
>>	dbc.parse('some statement here')
>>	dbc.executeparsed([some bind variables here])
>>
>>This can be a very useful feature for DBAPI to have, since in case of
>>
>Oracle
>
>>and
>>complex statements executed many times it can make the difference
>>
>visible.
>
>>I have made made these patches for my OLD OLD Oracle module (sadly I
>>
>have
>
>>to stick with it for now cause of other patches weve made in it -
>>
>Python DB
>
>>connection
>>object creation from embedded SQL connections, various memory leak
>>
>fixes
>
>>which
>>were critical in the environment I am using this module etc:)...
>>
>>Maybe I am totally missing something about DBAPI spec, and if above
>>described
>>features do exist in the spec, then ignore this silly mail :)
>>
>>Also, I am not aware of RDBMS supporting parsing without executing,
>>
>but
>
>>Oracle
>>certainly is one of those :)
>>
>