Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: A question about V$SGASTAT
"David" <hongzi_at_263.net> wrote in message news:<9rlkgn$k9b$1_at_mail.cn99.com>...
> Hi,I have a question about the view V$SGASTAT.
> I am not a DBA,so I don't understand the column of the view V$SGASTAT.
>
> I fount that in the column shared pool miscellaneous more and more,
> the column shared pool free less and less.Then in the end users can not
> connect to server,
> How to resolve it ? Thanks!
David,
1- What version of Oracle are you using? 2- What Oracle error message are you getting? 3- How large is your shared pool, how many concurrent sessions doesyour system support, and do you use MTS or dedicated servers?
My best guess is ORA-04031 which equates to running out of shared pool memory. The fix for this is first to make sure you use the dbms_shared_pool.keep routine to pin all large or heaviy used user written packages on instance startup plus the in-use Oracle supplied packages (standard, dbms_standard, dbms_utility etc..).
Then if you still have a problem you add memory to the shared pool. There are ways to estimate how large your shared pool needs to be but looking all that up would take too much time.
Any other error message would probably have a different fix. From version 8.1 to 8.1.7.2 there are at least 20 shared pool memory bug fixes so upgrading to the final release is also a consideration depending on what version you are sitting on. The 04031 info is valid for all versions 7.0 - 8.1. Pinning is usually the first step to take unless your pool is extrememely undersized to begin with.
![]() |
![]() |