Do you gather statistics in the way the CBO expects them?
You can't blame the optimizer unless you are sure you feed it with relevant statistics. There is the Auto Job which suits lots of cases, and there are some exceptions: volatile tables, partitioned tables, bulk load, histograms and bind variables, etc. You cannot gather all possible statistics everyday, and you cannot rely on stale ones. Let's define a few rules to know if you gather statistics in the right way, and how latest features can help in 12.1 and next releases.