There are whole books written on this subject.
1) If you have the diagnostics and tuning pack, consider doing an SQL Tuning advisor check. It's not always perfect, but sometimes it gives quick wins.http://www.oracle-base.com/articles/10g ... ng-10g.php
2) Get the execution plan, either directly, or via SQL Trace.http://www.oracle-base.com/articles/9i/dbms_xplan.phphttp://www.oracle-base.com/articles/mis ... tkprof.php
3) Check the cardinality of each step in the execution plan. Does it hit approximately the correct number of rows? If not, then it means your stats are not accurately representing your data.
4) Do the order of the steps in the execution plan look correct? Typically you would expect Oracle to use the smaller sets as the driving sets.
5) Do the join operations look appropriate for the size of the sets being joined. For example, large join operations probably should not use nested loops joins as hash joins will be more efficient and vice versa.
6) In the SQL Trace output, you will be able to see what most of the elapsed time is being spent on. This might help point you in the direction of what to do next.