Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: large pool always filling up
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.
Also strongly consider moving to Oracle 9.2 or even 10.1.
9.2. has improved MTS (and renamed it to "shared server", btw).