Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Is this a candidate for a stored outline?

Re: Is this a candidate for a stored outline?

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Sat, 03 May 2003 11:48:28 -0700
Message-ID: <3EB40EFC.67CFD85A@exxesolutions.com>


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
> >
> >

>

> 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.

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US