Re: Oracle Query Tuning

manoj.lahoti_at_gepex.ge.com wrote in message <885079291.2007040019_at_dejanews.com>...
>I'm trying run a select query against 3 oracle tables with 250,000 ,45000
>and 5000 records respectively. I have indexes on almost all the fields
>that i'm using in where clause of the query.
 <snip>
> Any suggestions to improve the query response would be
>appreciated.

Indexes will not always solve performance problems. First thing to do is put the SQL thru EXPLAIN PLAN to find out how Oracle is going to process the query. Nested loops usually sucks when it comes to joining a small table with a very large table - in this case a hash join performs much better. Play around with hints, explain plan again and run a few test queries. When these tests run, look at stats and events of the process to find out what Oracle is spending most of it's time doing. You can also look at using the parallel query option and changing the SQL to make use of full tablescans instead.

There's no magic-trick-out-the-hat way to solve a performance problem. ;-)

regards,
Billy Received on Wed Jan 21 1998 - 00:00:00 CST