Re: Percent of Shared Pool used

Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> wrote in message news:<E2F6A70FE45242488C865C3BC1245DA70354E27F_at_lnewton.leeds.lfs.co.uk>...
> Javier,
>
> this works for 8.05 to 9.2.0. Because you can specify the
> shared_pool_size with a 'k' or 'M' suffixes, in upper or lower case,
> there needs to be some hanky-panky goings on to get a number out of it
> all. The following simply uppercases everything and replaces 'K' with
> '000' or 'M' with '000000' (note, this is as per Oracle's docs - it is
> not a multiple of 1024 (for K) etc).
>
> SELECT
> ROUND(bytes/TO_NUMBER(UPPER(REPLACE(REPLACE(VALUE,'K','000'),'M','000000
> '))),4)*100 AS "Free_Mem_%"
> FROM v$sgastat,
> v$parameter
> WHERE v$sgastat.NAME = 'free memory'
> AND v$parameter.NAME = 'shared_pool_size'
> AND v$sgastat.pool = 'shared pool'
>
> The above will tell you the percentage free space in the shared_pool,
> but, don't go flushing it willy nilly - this is a seriously bad thing to
> do. It will impact on performance as procedures, and packages will need
> reloading when next accessed, SQL will all have to be reparsed (and a
> hard parse at that) etc etc. Sequences will also loas all their cached
> values.
>
> It's ok to monitor the shared pool usage, but don't flush it just for
> the hell of it. It's a very bad idea (tm).
>
> Cheers,
> Norman.
>
> -------------------------------------
> Norman Dunbar
> Database/Unix administrator
> Lynx Financial Systems Ltd.
> mailto:Norman.Dunbar_at_LFS.co.uk
> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------
>

  Flushing is probably only justified when you have *real* heavy   shared pool latch contention. But if you do want to size it,   just counting "free memory" is not enough. You should also take   into account "recreatable memory chunks" from x$ksmsp.

  Or you can sum up total space from v$sql where executions > 2.   Theorectically if a SQL is only executed once, it should not   stay in the pool anyway.

  Shared pool size goes the opposite way of buffer cache, it   should be as lean as possible, without causing excessive   shared pool latch contention. Received on Thu Feb 20 2003 - 19:48:17 CST