my Siebel sandpit

This case study ('The Case of the Stubborn Cost Based Optimizer') on Metalink (Note 343226.1) makes interesting reading.

The paper covers troubleshooting techniques when diagnosing poor performance for a vendor application and covers bind variables versus literals, the use of 10046/10053 trace, tkprof, weird decisions (defects) by CBO and yet another underscore parameter to favour indexes over full table scans.