Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Shared Pool: Can it be filled *over* 100% ?

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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 14 Aug 2004 09:54:55 +0000 (UTC)
Message-ID: <cfknhf$abe$1@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
> >
Received on Sat Aug 14 2004 - 04:54:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US