Parse once execute many
We have about 15 packages with queries done in the form below.
fetch curs into l_var;
if the procedure is called more than once.(and the init parameter session_cache_cursor = 0).
Then we get x parse and x execute .
I want of course 1 parse and execute many.
If i change the cursor with the following
if cursor not open then
fetch curs into var;
1. i have to put the init parameter open_cursor = high value then right?
2.Since i dont close the cursor, will i get in trouble later???
0) since you intend to reuse the cursors they must always be the same, so x-1 of your parses are "soft". Could be worse - in fact, the anticipatd gain might not justify recoding.
1) open_cursors should only need to be increased by 15.
2) you should only be using (max) 15 of them for your procedures - my guess is that won't cause you any pain at all.
(all these are per session values).
Click Here to Expand Forum to Full Width