Re: Is this a candidate for a stored outline?

"Ryan" <rgaffuri_at_cox.net> wrote in message news:j7Esa.11337$g41.615572_at_news1.east.cox.net...
>
> "Ana C. Dent" <anacedent_at_hotmail.com> wrote in message
> news:QfDsa.618$MJ5.209_at_fed1read03...
> > Ryan wrote:
> > > 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.
> >
> > SHARED POOL <> DB_BLOCK_BUFFERS
> > SHARED POOL is for SQL & PL/SQL code
> > DB_BLOCK_BUFFERS is for data blocks.
> >
> > The 2nd run is faster than the 1st because
> > the data is already in memory!
> >
> > Enable SQL TRACE to get facts rather than draw conclusions
> > based upon idle speculation.
> >
>
> hmmm... I have 2 table sthat Im joining regularly based on different where
> clauses. Is it ever a good idea to run a cursor to just get those two
tables
> blocks into memory?

Hi Ryan,

If these two tables are regularly joined, then they should be naturally cached by Oracle automatically.

But then again ...

I would consider these two tables as possible candidates for the KEEP pool to ensure their "cachability" (spell checker just had a giggle with that one !!). They (should) then be cached indefinitely.

However all that said, what you initially described sounded as if it's the cost of the parse that's hurting performance.

As mentioned by Ana, trace and see what's really going on and where the issues really are ...

Cheers

Richard Received on Sat May 03 2003 - 07:59:28 CDT