Re: Is this a candidate for a stored outline?

"Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message news:3EB40EFC.67CFD85A_at_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)
>
>

DBMS_STATS is run nightly after the batch loads. Stats are good up to a point, since we are in the process of loading alot of data so they get skewed during the load and scrubbing process. There is just alot of work to do. Trying to do what I can to speed them up. We are having issues getting all the nightly loads done. Received on Sat May 03 2003 - 18:32:30 CDT