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 ?
>
> andre vergison

Andre,
We have been using KEEP/UNKEEP packages for a while now. Our application is very database-centric, in that, all business rules are in the database. FORMS, our front-ends, is bare of any SQL as such. All they 've are hooks to these procedural objects. Pinning of the core routines in the shared pool has resulted in considerable performance improvements.

You might, depending on your application size, suitably size your shared pool. Having done this, try the following :

alter system flush shared_pool;
alter system flush shared_pool;
BEGIN

    sys.dbms_shared_pool.keep( 'DIUTIL' );
    sys.dbms_shared_pool.keep( 'STANDARD' );
    sys.dbms_shared_pool.keep( 'DBMS%' );  -- if using DBMS% packages 

    sys.dbms_shared_pool.keep( 'your core packages' ); END; Run this script immly after database startup. Pinning of DIUTIL prevents any ORA-4031 errors.

Also note that you can only pin packages. You might want to enclose standalone procedures/functions into packages.

-- 
Cheers

Sridhar Subramaniam
Avion Consulting Services
Sydney - Australia
Email : ssubrama_at_nibucorp.ccdn.otc.com.au / avion_at_ozemail.com.au

Disclaimer : All opinions are truly and just mine.