Re: Keeping stored procedures in the shared pool ?

Andre Vergison wrote:
>
> Has anybody already used the keep and unkeep functions of
> the dbms_shared_pool package ?
>
> We have an application that needs to run a specific stored procedure from
> time to time, let's say every X minutes. But the first time it is loaded
> in the shared pool which takes about 11 seconds (7 cpu secs and about
> 1600 disk i/os in tkprof output). Then it is executed a couple of times
> and each execution takes only about 0.2 seconds. After X minutes it may
> still still be present in the sga and then it's ok, but most of the time
> it isn't and it has to be reloaded. X may vary widely, so making the
> shared pool very very big isn't the solution.
>
> The idea is to use the keep function to fix it in the sga at server
> startup time, and avoid the 11 second delay during the day.
>
> Who has encountered similar stored procedure behaviours ? Is the keep
> function a solution ?

I have used the keep function and had it work. There is an Oracle bulletin about pinning packages in the shared pool, but I can't find the # - you can search in RTSS to get it.

However, I believe that you can only pin packages in the shared pool, not procedures and functions. Therefore you would have to put the stored procedure in a package. This is all under Oracle 7.1.6. Received on Mon Jan 01 1996 - 00:00:00 CET