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 18:11:03 -0700
Message-ID: <3EB468A7.85E8E381@exxesolutions.com>


Ryan wrote:

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

Can you run them in parallel rather than serially?

--
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 - 20:11:03 CDT

Original text of this message

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