Re: Shared Pool: Can it be filled *over* 100% ?

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<cfknhf$abe$1_at_hercules.btinternet.com>...
> The "shared pool" reported by v$sgastat is actually
> the SGA heap - and the 'shared_pool_size' set in
> the init.ora (spfile) demands memory for one of the
> main functions of the heap's usage; there are plenty
> of other components of the heap that are defined by
> other init.ora (spfile) parameters (e.g. sessions, processes
> and so on - which can add a LOT of memory, and easily
> push your formula over 100%).
>
> One feature of the 'shared_pool_size' is that Oracle
> automatically reserves 5% of it as the 'reserved pool',
> as part of a strategy for minimising fragmentation of
> the heap. This reserved memory is reported in v$sgastat
> as part of the 'free memory'.
>
> Consequently, I don't think the query you have really
> tells you anything useful. For a simple guideline on the
> size of the shared pool - if the 'free memory' component
> of the 'shared pool' pool is less than 5% of the parameter
> 'shared_pool_size', then Oracle has had to release some
> of the reserved memory: but even that doesn't tell you
> very much.
>
> In passing, code like yours becomes clearer (in my opinion)
> if you make use of in-line views to avoid repeating messy bits.
> e.g, something like the following avoids the proliferation of
> brackets and 1024's:
>
> select
> sg.sp_used,
> pa.sp_size,
> pa.sp_size - sg.sp_used sp_avail,
> 100* sg.sp_used / pa.sp_size sp_pct
> from
> (select sum(a.bytes)/(1024*1024) Sp_Used
> from v$sgastat a
> where a.pool = 'shared pool'
> and a.name != 'free memory'
> ) sg,
> (
> select value/(1024*1024) sp_size
> from
> v$parameter b
> where b.name = 'shared_pool_size'
> ) pa
> ;
>
>
> --
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/seminar.html
> Optimising Oracle Seminar - schedule updated July 27th
>
>
>
>
> "Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message
> news:2o0is0F5funcU1_at_uni-berlin.de...
> > Hello,
> >
> > Oracle EE 8.1.7.3 on AIX 5,
> > 1 instance on 1 central Bull Escala Server (8GB RAM), Dedicated Server
> > Configuration
> > 200 users using Oracle Forms 5 as inhouse app over C/S.
> >
> > Symptom: For the first time on the instance I saw ORA-04031 on some rare
> > occasions.
> > So, before taking action, I wanted to see how the shared pool is used, and
> I
> > tried the following SQL
> > taken from Mike Aults´ book "Oracle Administration and Management":
> >
> > prompt
> > prompt Übersicht Ausnutzung des Shared Pool:
> >
> > select
> > sum(a.bytes)/(1024*1024) Shared_Pool_Used
> > ,max(b.value)/(1024*1024) Shared_Pool_Size
> > ,(max(b.value)/(1024*1024))-(sum(a.bytes)/(1024*1024))
> Shared_Pool_Avail
> > ,(sum(a.bytes)/max(b.value))*100 Shared_Pool_Pct
> > from
> > v$sgastat a,
> > v$parameter b
> > where a.pool = 'shared pool'
> > and a.name != 'free memory'
> > and b.name = 'shared_pool_size'
> > ;
> >
> > Yesterday I saw Shared_Pool_Pct constantly in a bandwidth between 88 and
> 95
> > %.
> > So I followed Mikes´ advice to increase shared_pool_size and
> > shared_pool_reserved_size
> > and rebounced the instance over night.
> >
> > This morning I was one of the first being in place
> > and I saw Shared_Pool_Pct about 80%; so I thought - OK.
> >
> > Two hours later, most users are already working meanwhile,
> > I see:
> >
> > Übersicht Ausnutzung des Shared Pool:
> >
> > SHARED_POOL_USED SHARED_POOL_SIZE SHARED_POOL_AVAIL SHARED_POOL_PCT
> > ---------------- ---------------- ----------------- ---------------
> > 100,34935 95,3674316 -4,9819183 105,22392 --<-
> > !!! *over* 100 % and climbing ????
> >
> > 1 Zeile wurde ausgewählt.
> >
> > * Wenn Shared_Pool_Pct ständig über 90%: => shared_pool_size erweitern !
> >
> > I also have a small Oracle Form for Session observation where I can
> requery
> > this value 10 times in a second.
> > I can see the trend going constantly up.
> >
> > Additional information: 40 users use about 2000 SQL areas in total, the
> top
> > 6 are:
> >
> > Select
> > Username,
> > Sum(Sharable_Mem) Sharable,
> > Sum(Persistent_Mem) Persistent,
> > Sum(Runtime_Mem) Runtime,
> > Count(*) Areas,
> > Sum(Sharable_Mem+Persistent_Mem+Runtime_Mem) Sum_Mem
> > From JG_SQL_Summary
> > Group By Username
> > Order By 2 Desc;
> > Übersicht Ausnutzung des Shared Pool:
> >
> > USERNAME SHARABLE PERSISTENT RUNTIME AREAS
> > SUM_MEM
> > ------------------------------ ---------- ---------- ---------- ----------
> -
> > ---------
> > MASTERMIND 10758622 671096 2594084 692
> > 14023802 --the owner of 80% of the apps
> > PAYROLL 7110912 1027704 1811984 655
> > 9950600 --the owner of the rest of the apps
> > KLOSTERMAUER 6689916 96312 1370896 51
> > 8157124 --a user
> > ZIEGELSTEIN 3739888 176076 1051944 128
> > 4967908 --another user
> > SERVICEACCOUNTING 2900458 76076 687496 58
> > 3664030 --and yet another ...
> > SYSTEM 2770015 155240 1012380 190
> > 3937635
> > [real names altered except SYSTEM]
> > ...
> >
> > where JG_SQL_Summary is the following view:
> >
> > Select
> > Username,
> > Sharable_Mem,
> > Persistent_Mem,
> > Runtime_Mem
> > From
> > V$Sqlarea A,
> > Dba_Users B
> > Where A.Parsing_User_Id = B.User_Id
> >
> > Right now I am doing some RTFM again, but maybe somone has a tip,
> > maybe there is something wrong with the query for Shared_Pool_Pct ?
> >
> >
> > Any comments are greatly appreciated.
> >
> > TIA, Jan
> >
> >

Jan,

As I believe I said in my books, the code can return greater than 100% due to the reason Jonathan states. Anyone who relies 100% on a GUI is not a real DBA in my opinion Omlet. You need to know the under lying tables and structures, then you might understand wha the GUI is telling you.

As to 15 years in a specific job making you beyond reproach, tell that to career politicians.

I provide the script as a guideline, just to give people a heads up that their pool may be full of something besides useful code, the other two pieces of code that go along with it that actually show you how memory is being used and who is using it tell the whole story. This is just the patients temperature, as no good doctor would rely 100% on temperature as anything other than an indicator, this script should not be anything more than an indicator to check further.

MIke Ault Received on Sat Aug 14 2004 - 13:35:33 CDT