Parse once execute many
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Parse once execute many

  1. #1
    Join Date
    Nov 2000
    Posts
    440

    Parse once execute many

    We have about 15 packages with queries done in the form below.

    open curs;
    fetch curs into l_var;
    close curs;


    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
    open;
    end if;
    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???

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width