Re: Is this a candidate for a stored outline?

"Anton Buijs" <remove_aammbuijs_at_xs4all.nl> wrote in message news:3eb424d0$0$49103$e4fe514c_at_news.xs4all.nl...
> 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.
>
>

thanks, Ill note that. I dont have any proclivity with Stats pack. We dont have any DBAs on the team. DBAs manage the servers, backup recovery and such for a variety of teams. No time to do this for us. Im a developer and I know I need to learn how to use Statspack, I just haven't had the time yet. Its on my ever growing list... For now, I have to use the tools available to me. I know its not hard, but I have deadlines.

Would you recommend the Burleson Statspack book? He seems to be fairly controversial in DBA circles. Received on Sat May 03 2003 - 18:37:05 CDT