Re: ORA-4031/Shared Pool

David Kaiser wrote:
> .....
> 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? ....

The answer to this question is defenitly YES. When database is started, its shared_pool is empty, so you have to pin desired objects in it! Besides, DB startup is the most apropriate time to execute this procedure on your large and frequently used objects (packages, functions/procedures), because free space of shared pool will still be contigous after loading desired objects.

To be sure not to forget executing this procedure immediately after DB startup, you should put coresponding statement into your startup script.

> .... 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).
>

I'm not familiar with the above parameters, so I can not give you any help here.

-- 
 ===============================================================
 ! Jurij Modic                            Republic of Slovenia !
 !  tel: +386 61 178 55 14                Ministry of Finance  !
 !  fax: +386 61  21 45 84                Zupanciceva 3        !
 !  e-mail: jurij.modic_at_mf.sigov.mail.si  Ljubljana 1000       !
 ===============================================================