Re: Significance of specific database parameter difference

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
>
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

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