Re: Shared Pool Size and ORA-4031

In article <4veik5$ojf_at_scel.sequent.com>, Peter Moore <ptmoore_at_sequent.com> wrote:
>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.
>

Can I ask a follow-up question to this? We experience a similar problem to Luis's symptoms. Spuriously, our system reports these 4031 errors, in an environment that, to my knowledge, only uses one package. It's the dbms_session package, which we use to identify clients connecting to the database, and the activity each is performing.

Does this seem likely to be causing the problem? I know that each of about 20 workstations uses the package (each screen of a windows app) so perhaps this could be causing the problem. Does anyone think this is likely?

Aside from this one package, the applications use a lot of shared memory because they use many different sql-statements, one after another. It's complex validation of user input, on any of hundreds of windows. As a theory, maybe we need to tune this SHARED_POOL_RESERVED_SIZE parameter much smaller than it's default, because we use very few packages. Does this idea have any merit?

We recently doubled our SHARED_POOL_SIZE parameter, and found that the problem continued. So it's at least time to try something new.

Thanks for any insights.

Kevin Fries

-- 
-------------------------------------------------------------
Kevin Fries                         kelfink_at_ecst.csuchico.edu
CPD/PB, C Developer/DBA  http://www.ecst.csuchico.edu/~kelfink