| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Help : Pro*C: Reparse of PL/SQL blocks ??
A copy of this was sent to "Uri Margalit" <uri_at_hypernix.co.il>
(if that email address didn't require changing)
On Wed, 9 Jun 1999 22:17:36 +0200, you wrote:
>What about a cursor returns from stored procedure, why is also reparsing
>every execution ?
>the cursor is opened in a stored procedure.
>
exactly -- the cursor is OPEN'ed in the stored procedure.
The cursor is *closed* elsewhere. plsql may or may not be the environment closing the cursor. therefore, plsql *cannot* cache the cursor as it does normally. plsql can only cache those objects it has 100% control over. since the returned cursor might go back to
we cannot cache it.
Note that if you open the EXACT same query, the second and subsequent parses are 'soft' parses and happen almost instantly (shared sql kicking in). While not as fast as not reparsing at all -- its the next best thing.
>Uri
>
>
>Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
>news:37666001.23271041_at_newshost.us.oracle.com...
>> A copy of this was sent to Mirza Mohsin Beg <mbeg_at_netearnings.com>
>> (if that email address didn't require changing)
>> On Tue, 08 Jun 1999 10:44:47 -0700, you wrote:
>>
>> >
>> >
>> >Does anyone know why in Pro*C Cursors 'OPEN'ed inside anonymous PL/SQL
>> >blocks get reparsed every time the block gets executed.
>> >
>>
>> ref cursors are *always* reparsed upon open. They are not cached like
>other
>> plsql implicit and explicit cursors are -- they cannot be. The reason
>is -- the
>> client (pro*c in this case) is the person responsible for closing them.
>>
>> Plsql cannot cache something it does not 100% control. Since the proc
>code
>> typically would be:
>>
>> EXEC SQL allocate :theCursor;
>>
>> exec sql execute
>> begin
>> open :theCursor for
>> select id
>> .......
>> from the_table
>> where Id = :localId;
>> end;
>> end-exec;
>>
>> exec sql :theCursor; <<<<--------
>>
>> and plsql has no control over the CLOSEing of the cursor (as it would if
>it was
>> a plsql cursor only), it must really PARSE and OPEN it the next time
>around.
>>
>>
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
Current article is "Fine Grained Access Control", added June 8'th
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA--
 |
 |