DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: hitratio is low in sql area

  1. #1
    Join Date
    Apr 2001
    Posts
    20
    How can we keep dynamically created SQL statements with no possibility
    for reuse from eating up library cache.

    Details:
    We are aware of the need to eliminate literal SQL when possible by using
    bind variables, because it repeated literal statements use up library
    cache (shared pool.) However, we have a small set of fairly frequently
    used queries which need to be dynamically created. They will be highly
    individual in terms of their exact form and the tables accessed, and
    therefore have almost no possibility for reuse.

    What strategies are available to keep these queries from using up a lot of the library
    cache?
    Is there a way to segregate them and limit their use of pool?
    Is there a way to keep them from being cached, or to have them eliminated first?

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Try setting the

    ALTER SYSTEM SET CURSOR_SHARING=FORCE;

    Remember it would result in a high soft parsees ...


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


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