Hi "Tom"-

We have an outside vendor's product hitting our database and never using bind variables. Every statement is unique. We can't change it, we're stuck with it.

This outside program is a very high volume. These constantly unique statements are literally pushing perfectly good but less used cached queries out of the sqlarea. The reports we only run once a month never have a chance to get reused, and these reports would run much faster if they used the shared pool. We have nothing in the sqlarea that hasn't been used in two weeks, by next month end the cache is gone. The shared pool is 20480 MB, if that matters. I don't know if that's huge or tiny.

These EDI pooled statements are very easy to identify, so I thought why don't we purge some of these before they push out what we want to keep? It seems you can purge selectively by address/has_value.

I thought writing a little maintenance program to run nightly and get rid of these cached queries using DBMS_SHARED_POOL.PURGE would solve the problem. The DBA says I'm nuts, and dangerous. Do you have a better idea? Just make the pool bigger? Pin the queries that are getting pushed out? I'm not a fan of pinning because what we DON'T need is way more obvious in the SQLAREA than what we might need. It's also a shorter list, the first half of the sql_text is always the same.

-John