Whole books have been written on this subject, so I'm not sure what I can impart that is of use in a forum post.
1) Check the cardinality of the operations. That is, how many rows Oracle is processing for each step.
What is important here is the estimated number of rows match the actual number of rows. When Oracle generates an execution plan it uses database statistics to decide how expensive operations will be. If the stats are not representative of the data, then it may pick the wrong execution plan. A single way to check this is to compare the estimated cardinality with the actual cardinality. There is an example of this here:http://www.oracle-base.com/articles/9i/ ... stics_hint
If the actual and expected cardinalities do not match (or are close), then Oracle is using unrepresentative stats to make its decisions, which is not good.http://www.oracle-base.com/articles/mis ... istics.php
2) The next step is to sense check the operations. Do the execution plan seem sensible. For example, if you are expecting to return a single row, is a full table scan of 1 billion rows sensible? Likewise, if you are expecting to visit 1 billion rows, using an index to access them is probably not a great idea. This sense check may identify obvious issues, like you are trying to use a full table scan, where an index scan would be more appropriate, or vice versa.