Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Help : Pro*C: Reparse of PL/SQL blocks ??
Does anyone know why in Pro*C Cursors 'OPEN'ed inside anonymous PL/SQL blocks get reparsed every time the block gets executed.
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 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
Received on Tue Jun 08 1999 - 12:44:47 CDT
![]() |
![]() |