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

• Post for FREE via your newsreader at post.usenet.com ****

Hi Jan

> 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'

In many situations Oracle allocates more memory than specified by the INIT.ORA parameters. Therefore this formula makes no sense, or at least should be used with care... A plain select on V$SGASTAT is better...

> 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.

The shared pool is always completely used, the only exception is when it is oversized, then you will start seeing a large 'free memory'. Of course if you have ORA-04031 errors, it is probably too small.

Chris

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

• Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=