"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
news:41abc00d$0$17542$afc38c87_at_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
Actually, if you read all of his posts, he is making an assumption that the
large pool usage is the problem. His symptom is TNS errors, and he has
noticed that this occurs when the large pool is nearly full. I bet this is a
red herring. Note that he also mentioned that he is using MTS.
Received on Tue Nov 30 2004 - 15:39:26 CST
