You should follow the recommendation of the vendor.
There are many 3rd party applications that use literals instead of bind
variables and the only way to keep the app under control is to use
cursor_sharing on a specific version or because of specific database bugs in
specific patch levels. Most of the bugs that Mladen speaks were related to
cursor_sharing =SIMILAR/FORCE although there were a few related to EXACT. In
11.2 Oracle eliminated SHARING and flipped it to FORCE on startup.
When they say Oracle recommends it, they could be speaking of a particular
SQL statement they are using that requires it unless they rewrite it and
they decided not to rewrite their code to use BINDS requiring cursor_sharing
and then using certain constructs that then causes problems such as
Select COUNT(1) is faster when cursor_sharing=exact compared to
cursor_sharing=similar (Doc ID 867342.1)
Bug 13023854 - Long parse time / hang / ORA-979 for SQL with nested CASE
expressions with CURSOR_SHARING enabled (Doc ID 13023854.8)
Or because of problems they have seen on certain versions of databases along
with cursor_sharing
Database Hangs with ORA-7445 [kglic0()+1172], PMON Failed to Acquire Latch,
ORA-29771 (Doc ID 2128960.1)
Vendors are normally trying to create stability around their applications
and sometimes it requires certain parameters to be set at the database level
because of al variables in play.
Matthew Parker
Chief Technologist
Dimensional DBA
425-891-7934 (cell)
D&B 047931344
CAGE 7J5S7
Dimensional.dba@xxxxxxxxxxx
<http://www.linkedin.com/pub/matthew-parker/6/51b/944/> View Matthew
Parker's profile on LinkedIn
www.dimensionaldba.com <http://www.dimensionaldba.com/>
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mladen Gogala
Sent: Wednesday, August 10, 2016 9:06 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: case insensitvity and cursor sharing
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 Bug 13023854 - Long parse time / hang / ORA-979 for SQL with nested CASE
expressions with CURSOR_SHARING enabled (Doc ID 13023854.8)
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