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

From: Mirza Mohsin Beg <mbeg_at_netearnings.com>
Date: Tue, 08 Jun 1999 10:44:47 -0700
Message-ID: <375D568F.6B9402B2@netearnings.com>

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 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 Received on Tue Jun 08 1999 - 12:44:47 CDT