"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
news:41acf645$0$12650$afc38c87_at_news.optusnet.com.au...
> Alan wrote:
> > "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,
>
> What makes you think I didn't or don't?
>
> > 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.
>
> I don't bet. That's why I'm asking him to clarify. And, if *you* care to
> read *all* his posts, you'll discover that he'll be reporting back
> tomorrow with all the relevant details. So we can leave betting and
> guessing until then.
>
> > Note that he also mentioned that he is using MTS.
>
> Obviously. That's why he wrote, "And yes, we use MTS on the database.".
>
> HJR
Having a bad day? I can't recall you ever writing a message in this manner,
unless someone personally attacked you. You inferred a lot of attitude into
my message, and there was none intended by me. I simply noticed that
everyone was focusing on the large pool problem, probably because the OP
mentioned that it was what he thought the problem was. The problem could
easily be something else entirely, as the actual problem is that he is
getting TNS errors. BTW, I have (had?) nothing but the greatest respect for
you. Your now-removed-from-availability document on backup and recovery is
outstanding, and should be required reading. It is better than anything
Oracle ever published.
Received on Wed Dec 01 2004 - 09:20:29 CST
