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
Received on Tue Nov 30 2004 - 16:37:58 CST
