Ryan <rgaffuri_at_cox.net> schreef in berichtnieuws
gbDsa.11286$g41.571997_at_news1.east.cox.net...
| We do bulk processing every night. I noticed that if I run SQL statement,
| then flush the shared pool, then run it again it still runs alot faster
the
| second time. Since the blocks have been flushed from memory this leads me
to
| believe that its the reuse of the outline leading to the performance
| increase.
|
| Now the size of our tables typically increases every night. Now the
explain
| plan will be the same, however, does the stored outline store more than
| that? Such as specific data about which blocks to use? Would this be a
good
| candidate?
|
Just my 2c. I've read the thread but the focus is on the buffer cache.
Ryan says he flushes the shared pool and then "..the blocks have been
flushed out..." but that's not true: all sql and pl/sql code is flushed, not
the data blocks. So I suspect the problem is in the shared pool (and I have
had problems there, believe me).
Because the 2nd run after a flush of the shared pool is faster then the 1st
run, my guess is that the 1st run is slow because of a fragmented shared
pool. Getting the sql and pl/sql in there takes a relative long time to make
free space where that's not an issue in the 2nd run (because big part of the
shared pool is already free). You can prove if this is a valid theses by
running utlbstat/estat or statspack before and after the 1st and 2nd run.
When it's true, the 1st run must show considerable latch contention on the
shared pool latch and the library cache latch.
When this is not the case the output can still give some insight. Less
physical IO should be seen when having more blocks in the buffer cache in
the 2nd run is the cause of it as suggested in other posts.
Received on Sat May 03 2003 - 15:21:33 CDT
