Re: optimizer parameters

From: Greg Rahn <greg_at_structureddata.org>
Date: Sun, 24 Apr 2011 20:38:40 -0700
Message-ID: <BANLkTi=30N5QD4y7c_k_8ZzT7qhU+ZVxzg_at_mail.gmail.com>



The problem with this vendor's recommendation is "problem scope". The scope of the problem here is query, but the scope of the suggested solution is global - that's a mismatch. Tuning "a" query should not involve making global changes. What this vendor is not considering (nor measuring) is the global impact of changing these optimizer parameters on all the other statements that run on this database. Modifying these parameters until the "good" plan is chosen is certainly possible, but who is keeping track of how many other regressions there might be? So, scope of the problem must match with scope of the solution.

Bottom line: There are numerous ways to tune "a" query, but making global changes w/o regression testing everything should not be on that list.

On Sun, Apr 24, 2011 at 5:22 AM, ed lewis <eglewis71_at_gmail.com> wrote:
>    I'm curious what other peoples's experience, suggestions
> are in regard to the use of the  "optimizer_index_caching", and
> "optimizer_index_cost_adj" parameters.
> ...
>     I'm not a fan of changing this in midstream for an app
> that has been running for almost 2 years. This particular vendor
> is asking to modify this, as a possible solution to a slow-running
> query. I'm pushing back, and would rather address this in other
> ways, such as tracing the query, and go from there.

-- 
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l
Received on Sun Apr 24 2011 - 22:38:40 CDT

Original text of this message