count of parse / execute

From: Star Night <star_night_at_my-deja.com>
Date: Wed, 27 Sep 2000 02:19:26 GMT
Message-ID: <8qrlf8$tt2$1@nnrp1.deja.com>

Hi,

From an anonymous block I am calling a stored procedure to insert into a table.
The anonymous block contains a cursor through which it loops and for every element it calls the stored procedure. When I traced the session, I found that for each call to the stored procedure, the insert statement within the stored procedure was parsed.

i.e if the cursor looped through 100 times, 100 times the stored procedure was called and every time the same statement was parsed. And that resulted in bad performance.

What are the parameters to look for so that I can avoid this excessive parsing.

I have tried increasing the shared_pool_size, increasing the open_cursors, using the session_cached_cursors etc but in vain.

Any help is appreciated.
Thanks
Suresh

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Sep 26 2000 - 21:19:26 CDT