Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: large pool always filling up

Re: large pool always filling up

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Tue, 30 Nov 2004 08:21:29 +1100
Message-ID: <41ab92c7$0$19383$afc38c87@news.optusnet.com.au>


Kenneth Koenraadt wrote:

> On 29 Nov 2004 12:10:18 -0800, shaggy_at_popstar.com (Mr Boombastic)
> wrote:
> 
> 

>>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?
> 
> 
> 
> Moving from a dedicated server configuration to an MTS configuration
> causes most of the PGA parts to move into the SGA. So MTS does need a
> lager SHARED_POOL_SIZE, but this demand should be more than
> outweighted by the savings in PGA.
> 
> So the answer is quite simple : Increase your SHARED_POOL_SIZE and be
> happy for the PGA saved.

Er, actually nearly all the UGA components move into the LARGE pool when MTS is configured.

So a modest increase of the shared pool is certainly in order. But the bulk of the increase should be in the large pool... and I think the OP already knows that. His concern is that his large pool keeps being filled, and how does he go about tracing the session(s) that are causing the large pool to be consumed.

HJR Received on Mon Nov 29 2004 - 15:21:29 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US