Well said. However, I do prefer manipulating system statistics instead
of setting optimizer_index_cost_adj. The effect is exactly the same.
Setting SREAD to 0.1*MREAD has exactly the same effect on the plans as
setting optimizer_index_cost_adj to 10. I've recently been told to not
gather system statistics for various reasons which are beyond the scope
of this discussion. However, both setting optimizer_index_cost_adj and
setting the system statistics have one large problem: they are both
instance-wide. To tell the truth, once or twice I did resort to taking
shortcuts and using optimizer_index_cost_adj in the opt_param hint but I
am trying to avoid that parameter as much as possible. I am also trying
to avoid optimizer_mode=first_rows_<n> as much as possible. It is all
well and kosher as long as the DBA knows what are the consequences and
as long as there aren't any unforeseen consequences.
On 4/20/21 6:38 PM, Michael Brown wrote:
At the time, those parameters were crucial (as was Tim’s paper). But this discussion on those parameters is based around the same issue as Cary Millsap’s talk that used the Kegworth plane crash as an example. What was great advise for version X may not apply to version Y and may even be exactly the wrong advise.--
It is critical to re-examine parameters as part of every upgrade to be sure they still apply.
--
Michael Brown
