Re: case insensitvity and cursor sharing
Date: Thu, 11 Aug 2016 08:17:11 -0500
Message-ID: <CAJvnOJbqKupEcQQdryeF0oc+UVxETtLNfTEZ4_WdZEpSGaPtmQ_at_mail.gmail.com>
Mladen-
I think you mean it cant use histograms, not cant use statistics. Bind
variable peeking involves looking at the value of a bind variable the first
time the query is parsed, and then making a plan based on those values. If
unusual values are used, the plan can be wrong when there are histograms on
the columns in the query.
On Wed, Aug 10, 2016 at 11:05 PM, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:
> On 08/10/2016 03:37 PM, ed lewis wrote:
>
> Hello,
> We are currently using a third party app, which
> uses an Oracle database configured for "case-insensitivity".
>
> The vendor *usually* recommends that we set
> "cursor_sharing" to "force" when using the app.
> With the "case-insensitive" database, they are saying
> the "cursor_sharing" must be set to "exact", as per Oracle.
>
> I've been unable to find where that is documented.
> Must "cursor_sharing" be set to "exact" in this case ?
>
> Appreciate your help.
>
> Oracle 11.2.0.3
>
> Ec
>
>
>
> Hi Ed,
>
> I agree that there is an amazing lack of case sensitivity with today's
> youth. As per setting cursor_sharing to "force", it has nothing to do with
> case sensitivity. Setting cursor_sharing to "force" simply replaces all the
> constants on the right side of the equal sign with the system generated
> bind variables. Depending on your version, there are gazillion bugs related
> to the forced cursor bondage. As of Oracle 11G, you can use bind aware
> cursors. There is an excellent article about that, by Maria Colgan, here:
>
> https://blogs.oracle.com/optimizer/entry/how_do_i_force_a
>
> The problem with forced cursor bondage is that optimizer usually cannot
> use statistics, since it doesn't have the right values. Optimizer has to
> rely on bind variable peeking instead. The greater number of bind variables
> you have, the greater is the chance that one of them will not be a "typical
> representative" of the variables used during so called "normal operations"
> and that the generated plan will simply be wrong. Vendors using forced
> cursor bondage also tend to use things like optimizer_index_cost_adj, which
> is also a bad idea. That is, essentially, turning the clock back, to the
> times of the rule based optimizer and its 20 access paths, neatly ranked in
> a table.
>
> The mere fact that you turned to this list with such question tells me
> that there is a trouble in paradise. Forcing the cursor sharing in all
> cases, by replacing constants with system generated bind variables, usually
> creates more problems than it solves. Depending on version, it also
> significantly increases your chances of having fun with ORA-00600 and
> ORA-07445. In other words, CURSOR_SHARE=FORCE is big no-no. It's usually
> used by the application developers who are still stuck with Oracle 9i,
> along with optimizer_index_cost_adj.
>
> Regards
>
> --
> Mladen Gogala
> Oracle DBA
> Tel: (347) 321-1217
>
>
-- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.' -- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 11 2016 - 15:17:11 CEST