Re: large pool always filling up

Mr Boombastic wrote:
> Thanks for everyone's replies so far.
>
> Jonathan, we don't get messages like the one you mention below. What
> actually happens is that we constantly run a report on the database
> which tells us the large pool usage. When we see that we only have x
> Mb free we start calling the users up and ask them to log off the
> system if possible. This is our current workaround to the problem.

But what *problem*?

That a cache begins to fill up with used memory is expected behaviour, is it not? Otherwsie, it is just wasted memory.

> Would increasing the shared_pool_size and/or the large_pool_size
> resolve the issue? How can we identify problem queries?

I still don't see what the actual issue is. You monitor memory usage. When you see it go up, you apparently ask people to log off so it goes down. Never mind, it seems, that its *designed* to go up!

I think the more interesting question therefore becomes: what happens if you don't ring around and ask people to log off?

And how does anyone ever identify problem queries? Use Statspack and find the expensive ones. Keep an eye of v$sort_usage for expensive sorters. Use Enterprise Manager top sessions. Use tracing. And so on. The rules on how you do it don't change just because you're using MTS.

> Occasionally users have reported seeing TNS errors which occur at or
> around the time the large pool is full or nearly full. I dont recall
> from memory the exact ORA or TNS error number but will post back in
> this thread when I have the full details hopefully tomorrow.

OK, well that might be of more use then.

Regards
HJR Received on Tue Nov 30 2004 - 15:36:15 CST