Re: SQL optimisation

Having responded to Monty direct by mistake - Doh - I'll now copy the mail to the group.

I use toad to look at existing queries and tune through experience. Leccotech (www.leccotech.co.uk) provide a tool that automates the whole process and can really help out if you are a novice to tuning or have tons of stuff that really does need tuning.

Remember that moving to Oracle 9 you almost certainly want to switch to cost based optimiser so you'll need to analyse the database to generate meaningful statistics. It's almost certainly true that you won't have been using the cost based optimiser on Oracle 7. Do this and you might find that very little code needs tuning.

To tune those that need it - get explain plans of problem statements from the existing system as they run against Oracle 9 so you can capture the SQL easily and look to tune the worst offenders first. Don't try to tune everything until you know it's a problem. You'll end up wasting time taking milliseconds of lots of queries having no impact. It's usually the case IMHO that a few statements cause all the problem - the old 80:20 rule. If there are only a few to tune this group would probably help - if there are tens of queries to tune then you need to buy a tool.

Andy

"Monty" <mmontreaux_at_hotmail.com> wrote in message news:6284dd3.0211261313.5c5eebdb_at_posting.google.com...
> Excuse me all but I am writing/optimising considerable amounts of SQL
> written for Oracle 7 to be deployed on an Oracle 9 database. Some of
> the queries are very complex and verbose, > 900-100 characters.
>
> Might I ask the opinions of the forum as to what applications are
> there out there that could be used to analyse existing queries and
> propose alternate and more efficient constructs.
>
> Thank you
> Monty.
Received on Wed Nov 27 2002 - 08:31:00 CST