Re: Is this a candidate for a stored outline?

From: Ryan <rgaffuri_at_cox.net>
Date: Sat, 03 May 2003 16:55:20 GMT
Message-ID: <YvSsa.13602$g41.975790@news1.east.cox.net>

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:SQOsa.27782$1s1.410790_at_newsfeeds.bigpond.com...
> "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

>
>

they are used in a batch process in the evenings. The problems with that process is that we have about 20 selects and such from them. Problem is that each select has its own filter... so the whole table never gets stored in memory.

So I toyed with the idea of running a cursor against each table at the start of the process, simple to load them into memory. I think Ill try it on Monday. Received on Sat May 03 2003 - 11:55:20 CDT