Re: Deadly sins againts database performance/scalability

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<bq9n88$cdr$1$8300dec7_at_news.demon.co.uk>...
> Answer in-line
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK___November
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1069957744.97573_at_yasure...
> > Niall Litchfield wrote:
> >
> > > Actually I'm beginning to regret that post now.
> > >
> > > As Joel rightly says there is a real danger in creating a list like that
> > > one. In my case my post might just have suggested that one should always
> use
> > > bind variables (I believe you could read it that way :) ) and this is of
> > > course untrue, one should always use them, except where they are
> > > inappropriate.
> > >
> >
> > And they are in appropriate ... when?
> >
>
> DSS systems where the queries are few and
> large, and the optimiser's need for precise inputs
> is more important than a (relatively) minor increase
> in CPU and latch costs on parse/optimise.

I also disgree that you should never create intermediate tables and drop them. I think this is taken to an extreme. Now in a high transaction system you should not because it causes latch contention and affects throughput.

However, in a DSS system or for nightly batch processes, Ive found VERY large improvements in performance by doing create table as nologging in paralel. Its much faster than inserting into a global temp table. you dont have to worry about throughput if your looking at 5-10 users on the system max. Now you need to document it well(which most people dont do), so people know what to alter if you need to scale to more users. If I only have a certain window of time to get a series of batch processes done, Ill use temp tables like this. I find they are very useful when working with remote databases. You do a quick create table as to get just the data you need, then do your joins to the local table, so your not pushing data across a DB link for 10 different queries.

However, this will not scale to alot of users. So it needs to be well documented. Received on Mon Dec 01 2003 - 12:57:13 CST