Re: How to tune a huge SELECT ?

"Phil Singer" <psinger1_at_chartermi.net> wrote in message news:3D7BBF6E.769F79E8_at_chartermi.net...
> Daniel Morgan wrote:
> >
> > Christian Svensson wrote:
> >
> > > Greetings,
> > >
> > > We have in our Datawarehouse solution Cognos as end user application.
> > > Cognos reads from our different datamarts to build its own cubes.
> > >
> > > Since these SELECT get a lot of data, we are talking about approx 100
> > > million rows. It takes approx 7-8 hours for Cognos to select this data
> > > into a Cognos tempfile where it later use this to build the cubes.
> > >
> > > What I wonder is what Oracle setting/tuning can you make when you want
> > > to get this amount of data from the database ?
> > >
> > > I am open for any suggestions.
> > >
> > > Thanks.
> > >
> > > Cheers !
> > >
> > > /Christian
> >
> > Can you control the SQL statements? If so then EXPLAIN PLAN each and
 every
> > one. See if indexes are being used. See if the correct indexes are being
> > used. Determine what is taking the most time. Attack it first. And are
 you
> > trying to move that 100 million rows across some wire to another
 machine?
> > If so don't leave your network out of the calculation.
>
> To expound on this just a little more, create a file which
> is as large as the result set from your query.
> Place it on the same (if possible)
> or a similar (if not possible) drive where your table
> is physically located. Then do an ftp of that big file to
> wherever COGNOS is going to be using the results.
> The result is a bound of your potential speed;
> you are not going to be able to get your query to
> run much faster than this (regardless of RDBMS).
>
> If there is no network involved, and you can get
> the copy done in 10 minutes, then tuning will do some
> good. If Daniel has the right idea, and you have
> a slow network and this copy takes 7 hours, you
> know exactly where the problem is.
>
>
> > Daniel Morgan
>
> --
> Phil Singer | psinger1_at_chartermi.net
> Oracle DBA

Easy. Set FAST=TRUE.
Alternatively, read Kyte and Lewis.

Paul Received on Thu Sep 12 2002 - 16:33:56 CDT