Keeping stored procedures in the shared pool ?
Date: 1995/12/29
Message-ID: <4c17i4$qne_at_news.Belgium.EU.net>#1/1
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 Received on Fri Dec 29 1995 - 00:00:00 CET