Re: Percent of Shared Pool used

On Mon, 24 Feb 2003 17:02:01 +0200, Tanel Poder wrote:

> Hi!
>
> Let's put it simple:
> During an Apps upgrade you have to run lots of tasks (100000+ scripts ran by
> AutoUpgrade or AutoPatch).
> There are several different types of scripts, starting from inserts/updates
> to DDL and stored procedure creation and running.
>
> I have noticed several times, that when you have about 3000-6000 tasks left
> when applying the maintenance pack d-driver, the simple tasks which normally
> should run faster, get quite slow. v$session_wait shows waits on latches, I
> didn't even verify which latches were we talking about, just flushed the
> shared_pool. And the tasks started completing faster immediately. I have
> seen it several times enough, so it can't just be a coincidence.
>
> When you are able to run 2 tasks per second before and 4 tasks after flush -
> I call that performance improvement. Simple.
>

Not that simple, really. Supose it was the shared pool latch you were waiting on. Then of course flushing would 'improve' things, but to understand why you have to know what that latch is doing.

Put simply, the latch allows you into the shared pool to find some memory for the placement of your latest piece of SQL and its execution plan. If your shared pool is enormous, and fragmented, then you can spend an age searching for a suitable piece of free memory. So you hold the latch for a relatively long time. And people start queuing up behind you as a result, and things slow down.

So yes, a flush of the shared pool will sort all of that. By wiping out your library cache, you leave behind a massive, non-fragmented, piece of memory, and it takes no time at all to find a free piece for your next SQL statement.

But that's not performance tuning! That's dealing with a fragmentation/memory over-alocation problem with the nuclear option (a bit like 'I have a problem with Windows... therefore I must reboot'). The more appropriate response should be: why am I parsing so much that I am constantly searching for free memory in the library cache; why is my shared pool so big that a search for free memory within it takes so long; why have I not configured the shared_pool_reserved_size parameter appropriately so that large allocations of non-fragmented memory can be made easily; and so on.

Now given that an application upgrade script isn't written by you (therefore, no chance to write it such that it doesn't hard parse every statement); and that it doesn't happen very often (so big, bad, bold overkill steps like a flush are of little lasting significance and a quick fix is better than none); then yes, I'll buy that flushing the shared pool might be appropriate in certain circumstances (as you'd expect: they wouldn't have invented the command if it was never to be used!). But not any circumstance that involves proper, on-going performance tuning.

Regards
HJR Received on Mon Feb 24 2003 - 12:48:40 CST