Percent of Shared Pool used

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Thu, 20 Feb 2003 17:00:14 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA70354E27F@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


-------------------------------------

-----Original Message-----
From: Javier Villegas [mailto:mask_at_impsat1.com.ar] Posted At: Thursday, February 20, 2003 4:33 PM Posted To: server
Conversation: Percent of Shared Pool used Subject: Percent of Shared Pool used

I want to make a query for find the percent of Shared Pool used, and if the
percent is grater than 70 % run the alter system flush shared_pool;

Do you know how can I do that ?

Thanks in advance

Javier Villegas Received on Thu Feb 20 2003 - 11:00:14 CST