Tuning SQL queries is a very large topic. You can start with Oracle's Performance Tuning Guide.
One of the best books on the market is Guy Harrison's Oracle SQL High Performance Tuning. The book is a bit dated, but it does a really good job of showing the SQL tuning process. Here is a more up-to-date book.
Disclaimer: The links above direct you to Amazon.com. These links are, in no way, meant as an attempt to force you to order the books from Amazon. Feel free to order from any other source.
This was first published in May 2006