• Post Reply Bookmark Topic Watch Topic
  • New Topic

optimizing sql query

 
arun ramk
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,
please tell me what are all the steps need to be followed to best optimize a sql query. I have lot of queries joining 3 to 4 tables taking more than a minute to execute. Give me some tips on how to use hints for index or hash joins in those queries. Any material or good tutorial for optimizing sql queries will be really helpful. I am using Oracle 9i.
Thanks in advance.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Any query optimisation is data specific, so making general suggestions without profiling is not going to be that helpful.

Start by profiling the troublesome queries. Have a read of the Oracle explain plan documentation (in the Oracle docs: "Database Performance Tuning Guide and Reference"). Look at the output this generates. You'll be able to check for things like where the query does full table scans, which suggests an index might be helpful.

You can also use TKPROF (see the same Oracle docs) that will help you examine the resources used by specific SQL.
 
Ganesh Bhambure
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Any query optimisation is data specific, so making general suggestions without profiling is not going to be that helpful.


I agree with Paul.

Here is Link that may be useful for you
 
Pat Farrell
Rancher
Posts: 4678
7
Linux Mac OS X VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Paul Sturrock:Any query optimisation is data specific, so making general suggestions without profiling is not going to be that helpful.


I don't completely agree with Paul. Here is a very useful general suggestion:

Don't do multi-way joins. They are slow.

They are also good bugfarms.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35743
412
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Pat Farrell:
Don't do multi-way joins. They are slow.

I'd say don't do UNNECESSARY multi-way joins. If they are needed to get the data, what other choices are you left with. A bunch of single queries and doing the joins yourself?

They aren't always slow. If the tables involved are tiny or you have proper indexes, they can be quite fast.
 
Pat Farrell
Rancher
Posts: 4678
7
Linux Mac OS X VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is "unnecessary"?

While indexes help, some multi-way joins are just slow, especially as you get to five, seven and bigger numbers.

There are two standard optimizations you can do:
  • denormalize
  • do common joins and store them in a temp table


  • There are no global rules about when either of these is better than doing it the obvious multi-join way.

    The difficulty of making general comments on this is why DBAs make big bucks
     
    • Post Reply Bookmark Topic Watch Topic
    • New Topic
    Boost this thread!