Greg over at Structured Data has a fantastic piece titled "Troubleshooting Bad Execution Plans" which is a must-read for all. Learning about the GATHER_PLAN_STATISTICS hint alone was worth the price of admission.

Also time for me to re-evaluate the changes I made to OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ a few versions back:

SQL> show parameters optimizer_index

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
optimizer_index_caching              integer     90
optimizer_index_cost_adj             integer     10