| 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 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--
 |
 |