Re: optimizer parameters

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Mon, 25 Apr 2011 11:08:15 +0100
Message-ID: <BANLkTingkQHp1oimaa9QWt82Yj6QDsynmA_at_mail.gmail.com>

I'd push back as well. You've clearly identified a specific slow process and query, the appropriate process for resolving that class of problem is pretty much exactly what you describe. The largest issue with the vendor's suggestion is that it is a systemwide change for a process problem. The change *will* affect other process within the system, some of them potentially adversely. Your suggested approach is unlikely to (though the resolution may affect directly related parts of the application either positively or negatively).

Issues that may come up.

1. Most likely, the vendor may have a set of server parameters required for vendor support.
2. You may as a result of the exercise want to change either the query or the physical or logical structure of the database. This can be problematic with vendor support.

Most vendors however do have good technical Oracle folks on board somewhere in the organisation - if you can find them and work with them this can be very fruitful, both for the immediate issue and for longer term vendor/customer relations.

On Sun, Apr 24, 2011 at 1:22 PM, ed lewis <eglewis71_at_gmail.com> wrote:

> Hi,
> 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.
>
> As a general practice, I leave them at their defaults, unIess
> the vendor specifically requires that they be modifed. I use
> system statistics instead. I haven't find the need to do otherwise.
> I've tested these parameters on queries, and have received various results.
> In some cases, it made use of an index, and in other cases,
> it had no impact.
>
> 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.
>
>
> Your input is appreciated.
>
> ed
>
>
> oracle 10.2.0.4
> solaris 10
> rac, asm 10.2.0.4
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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

Received on Mon Apr 25 2011 - 05:08:15 CDT