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

From: Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de>
Date: Fri, 13 Aug 2004 08:51:13 +0200
Message-ID: <2o36unF6dq11U1@uni-berlin.de>

"David Fitzjarrell" <fitzjarrell_at_cox.net> schrieb im Newsbeitrag news:9711ade0.0408121405.61d2d845_at_posting.google.com...
> "Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message
news:<2o0jciF5efc1U1_at_uni-berlin.de>...
> > "Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> schrieb im Newsbeitrag
> > news:2o0is0F5funcU1_at_uni-berlin.de...
> > > Hello,
> > >

...
>
> The query is, indeed, wrong. It should be this:
>
> prompt
> prompt Übersicht Ausnutzung des Shared Pool:
>
> select a.spused/(1024*1024) Shared_Pool_Used,
> b.spsize/(1024*1024) Shared_Pool_Size,
> ((b.spsize/(1024*1024)) - (a.spused/(1024*1024)))
Shared_Pool_Avail,
> (a.spused/b.spsize)*100 Shared_Pool_Pct
> from
> (select sum(bytes) spused from v$sgastat where pool = 'shared pool'
> and name != 'free memory') a,
> (select max(value) spsize from v$parameter where name =
'shared_pool_size') b;
>
> This will eliminate the cartesian product present in the previous query.
>
> David Fitzjarrell

Thanks David, but still I see this:

SQL> @qpool

Shared Pool Size = 95,37 MB [ Dave = 95,367431640625 ]
Shared Pool used = 95,13 MB [ Dave = 95,127834320068359375 ]
Shared Pool free = ,24 MB [ Dave = ,239597320556640625 ]

Usage = 99,75 % [ Dave = 99,748764 ]

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> @qpool

Shared Pool Size = 95,37 MB [ Dave = 95,367431640625 ]
Shared Pool used = 95,9 MB [ Dave = 95,896331787109375 ]
Shared Pool free = -,53 MB [ Dave = -,528900146484375 ]

Usage = 100,56 % [ Dave = 100,554592 ]

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> @qpool

Shared Pool Size = 95,37 MB [ Dave = 95,367431640625 ]
Shared Pool used = 95,8 MB [ Dave = 95,799686431884765625 ]
Shared Pool free = -,43 MB [ Dave = -,432254791259765625 ]

Usage = 100,45 % [ Dave = 100,453252 ]

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> and climbing.

Meanwhile I use an anonymous block
and seperated the queries:

set serveroutput on

DECLARE    Cursor Cur_Stat
   Is
   Select

Sum(Bytes)/(1024*1024) Shared_Pool_Used From

      v$Sgastat
   Where Pool = 'shared pool'
   And Name != 'free memory'
   ;
   nUsed Number (5,2);

   Cursor Cur_Par
   Is
   Select

Max(Value)/(1024*1024) Shared_Pool_Size From

      v$Parameter
   Where Name In
   (

      'shared_pool_size'--,
--      'shared_pool_reserved_size'

   )
   ;
   nSize Number (5,2);
   nFree Number (5,2);
   nProzent Number (5,2);

   Cursor Cur_DaveFitzjerrald
   Is
   Select

      A.Spused/(1024*1024) Shared_Pool_Used,
      B.Spsize/(1024*1024) Shared_Pool_Size,
      ((B.Spsize/(1024*1024)) - (A.Spused/(1024*1024))) Shared_Pool_Avail,
      (A.Spused/B.Spsize)*100 Shared_Pool_Pct

From
(

      Select Sum(Bytes) Spused
      From v$Sgastat
      Where Pool = 'shared pool'
      And Name != 'free memory'

) A,
(

      Select Max(Value) Spsize
      From v$Parameter
      Where Name = 'shared_pool_size'

) B;
Rec_Dave Cur_DaveFitzjerrald%Rowtype;

BEGIN Open Cur_Stat;
Fetch Cur_Stat Into nUsed;

Open Cur_Par;
Fetch Cur_Par Into nSize;

Open Cur_DaveFitzjerrald;
Fetch Cur_DaveFitzjerrald Into Rec_Dave;

nFree := nSize - nUsed;
nProzent := nUsed * 100 / nSize;

   DBMS_OUTPUT.PUT_LINE('Shared Pool Size = '||nSize||' MB [ Dave =
'||Rec_Dave.Shared_Pool_Size||' ]');
   DBMS_OUTPUT.PUT_LINE('Shared Pool used = '||nUsed||' MB [ Dave =
'||Rec_Dave.Shared_Pool_Used||' ]');
   DBMS_OUTPUT.PUT_LINE('Shared Pool free = '||nFree||' MB [ Dave =
'||Rec_Dave.Shared_Pool_Avail||' ]');
   DBMS_OUTPUT.PUT_LINE('Usage = '||nProzent||' %  [ Dave =
'||Rec_Dave.Shared_Pool_Pct||' ]');

   Close Cur_Stat;

Close Cur_Par;
Close Cur_DaveFitzjerrald;

END;
/

So, the join is not the crucial point.

Additional information: my settings are:

SQL> select name, value from v$parameter 2 where name like 'shared_pool%';

NAME

VALUE

shared_pool_size
100000000

shared_pool_reserved_size
10000000

Now I am not sure: is shared_pool_reserved_size *a part of* the shared pool size as defined or is it *added* to it ?

If I alter my second cursor to

Select

Sum(Max(Value)/(1024*1024)) Shared_Pool_Size From

      v$Parameter
   Where Name In
   (

      'shared_pool_size',
      'shared_pool_reserved_size'

   )
   Group By Name
   ;

Then I see:

SQL> @qpool

Shared Pool Size = 104,9 MB [ Dave = 95,367431640625 ]
Shared Pool used = 98,26 MB [ Dave = 98,282196044921875 ]
Shared Pool free = 6,64 MB [ Dave = -2,914764404296875 ]

Usage = 93,67 % [ Dave = 103,056352 ]

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> @qpool

Shared Pool Size = 104,9 MB [ Dave = 95,367431640625 ]
Shared Pool used = 98,82 MB [ Dave = 98,8189239501953125 ]
Shared Pool free = 6,08 MB [ Dave = -3,4514923095703125 ]

Usage = 94,2 % [ Dave = 103,619152 ]

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> which would make more sense ...

Any comments on that ?
TIA, Jan Received on Fri Aug 13 2004 - 01:51:13 CDT

Re: Shared Pool: Can it be filled *over* 100% ? - maybe a solution ...

Re: Shared Pool: Can it be filled over 100% ? - maybe a solution ...