Re: ORA-04031 and shared pool

Hi again Everyone.

Thanks for all the answers.

Concerning my problem, here is a little bit of a discovery I made which I cannot get any clarification
to. I ran the following statement:

select
sql_text
from
v$sqlarea

To my surprise I got less than 200 records back. I checked again and the shared pool was still at over 75% full. Here is how I check how full the shared pool is:

select

    to_number(p.value) "Total Pool",
    s.bytes "Free Bytes",
    round(( s.bytes / p.value ) * 100,1) "Free" from

    v$parameter p,
    v$sgastat s
where
p.name = 'shared_pool_size' and
s.name = 'free memory'

I don't understand how these two results make sense together.

Furthermore I still don't understand what "aging out" means. The LRU algorithm sound like some sort of stack but I don't see where it is described in any detail.

I'll see if I can get the patch level and post it tomorrow.

Regards,
Mario

"MT" <mtechera_at_wpmc.com> wrote in message news:9mgsa2$ie7$1_at_news.online.de...
> Hi Everyone:
>
> I'm working on 8.1.7. I have a SHARED_POOL of 75 MB
> (my SHARED_POOL_RESERVED_SIZE = 7.5 MB) and am still getting that dreaded
> ORA-04031. This happens although I can see that the free memory in the shared pool
> is over 20%.
>
> I have tried as an emergency measure to use:
>
> ALTER SYSTEM FLUSH SHARED_POOL;
>
> but this does not reduce the shared memory usage.
>
> Does anyone know why the shared pool may refuse to flush? I have tried this on an
> identical system and the shared pool usage drops immediately.
>
> Does anyone know why, when the shared pool still has over 20% free space,
> Oracle gives 04031? I though that it used an LRU algorithm. If this is the case,
> why aren't older queries simply being aged out?
>
> I am also now in the hunt for failure to use bind variables. Does anyone have any
> good scripts or links to scripts that can help me pull out everything in the shared pool
> for analysis?
>
> Any help and ideas are welcome.
>
> Regards to all,
> Mario
>
>
>
>
Received on Wed Aug 29 2001 - 14:38:20 CDT