Re: How to tune a huge SELECT ?

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