Here are the resources I find most useful for Oracle SQL query optimisartion, ordered from basic to advanced.
Guide to understanding the basics of indexing and the various merges in line with their effects on execution plans
How-to manual from Oracle themselves on how to use autotrace in SQLPlus
The most complete reference on Oracle hints I’ve come across
A collection of specific, and advanced, query tuning techniques
Also, here is the 1979 seminal paper from P. Selinger of IBM, which forms the roots of access plan optimisation in most current day databases; and addresses the basic concepts that need to be taken into account when optimising queries.
Access path selection in a relational database management system
This one here does not have anything to do directly with query optimisation, but I found that attempting to optimise queries without first understanding the algorithms behind them is a fool’s errand. So here is a great post on the basic algorithms behind nested loop join, merge join and hash join (code in C#).