Re: Help : Pro*C: Reparse of PL/SQL blocks ??

What about a cursor returns from stored procedure, why is also reparsing every execution ?
the cursor is opened in a stored procedure.

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.
>
>
> >Please see an excerpt (edited) from the explain plan. Why does the SQL
> >statement get reparsed, everytime it is executed, whereas the PL/SQL
> >block does not? (By the way this only happens if the cursor is 'OPEN'ed
> >inside a PL/SQL block. If you 'DECLARE' the cursor in Pro*C then this
> >does not happen)
> >
> >1)
> >Pro*C code:-
> >
> >{
> > exec sql end declare section;
> > sql_cursor theCursor;
> > int localId = 125;
> > exec sql end declare section;
> >
> > EXEC SQL allocate :theCursor;
> >
> > exec sql execute
> > begin
> > open :theCursor for
> > select id
> > .......
> > from the_table
> > where Id = :localId;
> > end;
> > end-exec;
> >
> >}
> >
> >
> >2)
> >The plan was as follows:-
> >
> >begin open :theCursor for select id ...
> >.........from the_table where Id = :localId
> > ; end ;
> >
> >call count cpu elapsed disk query
> >current rows
> >------- ------ -------- ---------- ---------- ---------- ----------
> >----------
> >Parse 1 0.00 0.02 0 0
> >0 0
> >Execute 5 0.01 0.01 0 0
> >0 5
> >Fetch 0 0.00 0.00 0 0
> >0 0
> >------- ------ -------- ---------- ---------- ---------- ----------
> >----------
> >total 6 0.01 0.03 0 0
> >0 5
> >
> >
> >
> >SELECT ID,.......
> >FROM
> >THE_TABLE WHERE ID = :b1
> >
> >
> >call count cpu elapsed disk query
> >current rows
> >------- ------ -------- ---------- ---------- ---------- ----------
> >----------
> >Parse 5 0.00 0.00 0 0
> >0 0
> >Execute 5 0.00 0.00 0 0
> >0 0
> >Fetch 10 0.00 0.00 0 15
> >0 5
> >------- ------ -------- ---------- ---------- ---------- ----------
> >----------
> >total 20 0.00 0.00 0 15
> >0 5
> >
> >-M
> >ps: please email me directly also
>
>
> 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
> --
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation Received on Wed Jun 09 1999 - 15:17:36 CDT