ORA-4031/Shared Pool

We have been wrestling with this error for a while. I fully understand why we get this error (usually when trying to run larger packages).

Does anyone have a decent strategy for working with this problem (ORA-4031)? We are running 7.2.2.3 with a shared pool of 18 MB. We are getting the error because of fragmentation.

We have started to experiment with DBMS_SHARED_POOL.KEEP(). Does this procedure need to be called everytime the database is started? Also has anyone had luck using SHARED_POOL_RESERVED_SIZE and SHARED_POOL_RESERVED_MIN_ALLOC in their init files to solve this. (We have found these to be a bit buggy in their implementation).

Thanks for any ideas.

-- 

+---------------------------------------+-------------------------------+


| David Kaiser                          | e-mail: davidk_at_sfmic.com     

|

| Project Manager                       |    WWW: http://www.sfmic.com 

|

| State Fund Mutual Insurance Company   |                              

|

| 7500 Flying Cloud Drive, Suite 900    |  voice: 612.914.1317         

|

| Eden Prairie, MN 55344  USA           |    fax: 612.944.1169         

|

+---------------------------------------+-------------------------------+