Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Is this a candidate for a stored outline?
Ryan wrote:
> "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
> >
> >
>
>
Before you go to the exotic ... consider the mundane. Is anyone running DBMS_STATS to update data dictionary statistics before and after this batch process?
If not ... doing so will likely yield more benefit than any other single action.
-- Daniel Morgan http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp damorgan_at_x.washington.edu (replace 'x' with a 'u' to reply)Received on Sat May 03 2003 - 13:48:28 CDT