Re: Mechanism Behind Optimizer Cost Caclculation

A couple of key points to remember for future reference.

1. How, and when exactly, were the stats collected. Cursor don't always get invalidated immediately when stats are collected so the cursor you're showing could have been from a previous statement
2. If you add the 'peeked_binds' format option to the call to dbms_xplan.display_cursor() it will (usually) show you the actual value used to optimise the query.

There also the detail that the actual min() and max() are irrelevant to the optimizer, what matters are the values stored in usr_tab_cols (or the two endpoints in user_tab_histograms). In this case, though the 10053 trace you supply later shows that the dictionary stats match the min() and max()

I think the "cardinality = 1 " is probably an indication that the cursor you've reported was optimized for an earlier pair of inputs. (A quick check on the 10053 trace tends to confirm this.)

Regards
Jonathan Lewis

--
http://www.freelists.org/webpage/oracle-l