Re: Keeping stored procedures in the shared pool ?

In article <4c17i4$qne_at_news.Belgium.EU.net>, avergison_at_infosoft.be says...
*>
*>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 ?
*>
*>andre vergison
*>

Try this: grant execute on the package to sys, logon as sys and at the command line:
begin
dbms_shared_pool.keep ('owner.package_name'); end
it should work Received on Tue Jan 02 1996 - 00:00:00 CET