Re: large pool always filling up

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Tue, 30 Nov 2004 11:34:17 +1100
Message-ID: <41abc00d$0$17542$afc38c87@news.optusnet.com.au>

Gerry Sinkiewicz wrote:

> "Mr Boombastic" <shaggy_at_popstar.com> wrote in message
> news:76dee8e3.0411291210.5b03b342_at_posting.google.com...
> 

>>Hello
>>
>>We are running an Oracle 8.1.7.4 database in production and we have a
>>problem on one of our databases where the large pool is always filling
>>up and on an almost daily basis gets to more than 90 - 100 % full. How
>>do we identify any problem queries? Statspack info on the large pool
>>is limited, as is anything on Metalink.
>>
>>And yes, we use MTS on the database. I'm not sure where to start the
>>tuning process really as previous investigations have all yielded a
>>blank (where we mostly used Statspack for analysis).
>>
>>One possible avenue i have in mind is to investigate the dispatcher
>>process (v$dispatcher, v$dispatcher_rate) and also identifying any
>>shared server contention. Would reducing any MTS help our issue?
>>
>>Im not sure where else to look really. Maybe an experienced DBA out
>>there can help and point me in the right direction?

> 
> 
> How big is you large_pool?
> 
> If it is less than 300MB, think larger.

Without more detail from the OP, I think that such advice takes us nowhere. If we read his post literally, then his large pool is actually too large (he reports it only being between 90% and 100% full, meaning potentially 10% is just wasted memory), and he should therefore actually reduce its size.

He doesn't report getting ORA-04031 error messages, for example. He merely seeks to know which queries are consuming the large pool.

Generic advice on how to size caches is not wise at the best of times (I very rarely have ever needed a large pool bigger than 48M, for example, but I wouldn't want to read too much into that number either). But when it doesn't even answer the actual question asked...

Regards
HJR Received on Mon Nov 29 2004 - 18:34:17 CST