Re: Shared Pool Size and ORA-4031

lsantos_at_centroin.com.br (Luis Santos) wrote:

> I'm a DBA for a Oracle 7.2.2 database based on a Digital Unix v3.2c
> Digital Alpha 1000 Server. We are receiving several errors ORA-4031
> (Out of space in shared pool size).
 

> This error occurs (as sayed by Oracle) because a large package or
> cursor cannot be allocated in the shared pool. This is due inexistance
> of a contiguous free memory area in the shared pool.
 

> Oracle recommends me to, frequently, run the command ALTER SYSTEM
> FLUSH SHARED_POOL, to avoid this problem. I'm runnig it each four
> hours.
 

> But I notice that my performance have decreased.

There are two solutions to your problem.

1. When starting up the database you could 'pin' any large packages in the shared pool. I think the command is EXECUTE DBMS_PACKAGE.KEEP('package_name'); This will ensure that your packages take up the first x bytes of contiguous space, leaving the rest for your dynamic data.
2. Use the init.ora paramter SHARED_POOL_RESERVED_SIZE. This reserves space n the shared pool for large packages - see your README in rdbms/doc for details. This solution will need tuning. -- Peter Moore. DBA, IS Ops, Sequent Computer Systems Ltd, Weybridge, UK.