-
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?
-
Try setting the
ALTER SYSTEM SET CURSOR_SHARING=FORCE;
Remember it would result in a high soft parsees ...
Sam
