Re: Shared Pool Size and ORA-4031

Kevin,

It's pretty difficult to say how often and which package will cause the problem.

If you have a system with many frequent transactions, especially if they are not unique then you may find that you still get this error, even if you re-double the SHARED_POOL_SIZE.

If I remember correctly, the minimum size of SHARED_POOL_RESERVED_SIZE is 10% of SHARED_POOL_SIZE. I don't have the details to hand. You do need to explcitly set something up your init.ora to use this feature, it is not on by default. Check your README files for details. Certainly, decreasing the reserved space will not help you.

My favoured solution (simply because it was the first one I encountered and it doesn't require tuning) is to PIN the packages on database startup.

Cheers,

Pete

kelfink_at_ecst.csuchico.edu (Kevin Fries) wrote:

> 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.

--
Peter Moore.
DBA, IS Ops,
Sequent Computer Systems Ltd, Weybridge, UK.