Re: Percent of Shared Pool used

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.

Tanel.

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:pan.2003.02.24.10.03.51.493388_at_yahoo.com.au...
> On Mon, 24 Feb 2003 01:00:13 +0200, Tanel Poder wrote:
>
> >> If you're only using 70% of your shared pool, then you've over-sized
it.
> >> Free memory inside a cache is wasted memory. And the very last thing
you
> >> want to do is to flush the shared pool.
> >
> > Hi,
> >
> > flushing shared pool has helped me with performance during Oracle
> > Applications upgrades several times. Of course this isn't a normal
> > circumstance where thousands of packages are created in very short
time..
> >
> > Tanel.
>
>
> There are ZERO circumstances under which flushing the shared pool would
> have helped you with performance. It might well have saved your ass when
> the thing was fragmented to buggery, but that's not quite the same thing.
> Every flush of the pool would have caused extra parsing, and that would
> have actually slowed things down... which is a darn'd site better than not
> being able to place the thing in the library cache at all, I suppose. But
> it certainly isn't a performance enhancement.
>
> Unless of course you can enlighten me on the exact nature of this
> performance assistance you gained from such flushing activity??
>
> HJR
>
Received on Mon Feb 24 2003 - 09:02:01 CST