Don,
Sounds like you have a VP there who is willing to suffer pain to cure
the disease. Many places don't have high-level people like that.
Last place I worked I used CURSOR_SHARING=FORCE because the programmers
didn't want to code prepared statements (most of the statements were
"select... where username = 'literal string'.
I PROVED to them that they were the cause of the shared pool allocation
errors by pulling the statements out of the sql area. Didn't matter, it
would have put them behind schedule. VP agreed with the programmers, I
put cursor sharing on. Solved the problem
Of course, the complicated stuff the DBAs wrote worked slower but no
one seemed to care
Rachel
- Don Granaman <granaman_at_cox.net> wrote:
> Actually, "CURSOR_SHARING=FORCE" is in the index. ;-)
>
> The short (?) story is that it is a crutch. It can provide some
> relief from
> applications that pour out tons of nearly identical SQL -varying only
> in
> literal values, but it can also cause some significant problems.
> Also,
> there are a lot of bugs with it in all the versions I've used it in
> (8.1.6.x
> and 8.1.7.x). My experience is that it (a) works and (b) helps
> significantly in about 30% of the system where I've tried it. Bugs
> include
> things like:
>
> 1) "... where col1='X'and col2='Y' (in 8.1.6.x, it substitutes wrong
> and
> generates an error. Fixed in 8.1.7)
>
> 2) If the first value in a bind list is a null, it can generate a
> process
> crash with an ORA-07445 (in 8.1.7.1.? at least)
>
> 3) Java thin clients can return wrong results (hearsay - from Stan
> Yelliot)
>
> --- Moral of the story - test it *very thoroughly*!
>
> It can also cause some significant problems, even when it works
> "correctly".
> For example:
>
> *) It cures the most obvious symptoms, but not the disease.
> Developers
> often like to think otherwise and simply continue bad coding
> practices.
> CURSOR_SHARING still doesn't avoid a soft parse. Applications with
> lots of
> literals typically don't reuse cursors either. Partially "masking"
> serious
> design and coding flaws with "magic bullets" like
> CURSOR_SHARING=FORCE
> doesn't actually solve the much larger systemic problems. It is
> likely to
> buy you some time and fewer headaches with thrashing in the shared
> pool, but
> it still isn't very scalable in the long run.
>
> *) *ALL* literals get substituted. This can throw the optimizer off.
> Examples:
> (1) "where ... and 1 = 2" ("Oh! I don't really need to fetch any
> rows!"
> isn't obvious.)
> (2) When a literal causes the optimizer to use histograms well.
> STATUS_CODE
> has possible values of 'OPEN and 'CLOSED'. 99% of all records have
> 'CLOSED', but 99% of all queries are for 'OPEN'. DBAs would gladly
> "suffer"
> an extra statement with literals rather than suffer a poor execution
> plan
> for 99% of the executions.
>
> I consider using CURSOR_SHARING=FORCE like I would consider using a
> tourniquet - its preferrable to sudden death, but it isn't applicable
> in
> every case and is rarely a great long term solution. About three
> days ago,
> I had this discussion with a (very technical) VP. *Everything* is
> written
> with literals. Every literal statement is "prepare()"ed. I
> explained the
> basic issues to him and his preference was not to use it. He wants
> to
> force a resolution of the deeper issues by letting the situation
> become so
> bad soon that it forces a better, more permanent solution - before
> the
> rapidly increasing transaction volume REALLY hits the fan. The
> CURSOR_SHARING=FORCE safety valve is something we are reserving as a
> last,
> temporary resort.
>
> Don Granaman
> [OraSaurus]
>
> BTW: Hi Mike!
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, July 24, 2002 7:23 PM
>
>
> Mike,
> What is the version of the database? Some versions of 8.1.7 had a few
> bugs
> when this parameter was set to FORCE. I suggest searching Metalink.
> But it
> does work as advertised in later releases. I would also recommend
> reviewing
> Tom Kytes' book to read about his views in using this parameter at
> the
> instance level (my boss is reading my copy, so I can't give you page
> #s).
>
> - Kirti
>
> -----Original Message-----
> Sent: Wednesday, July 24, 2002 6:08 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Has anyone set Cursor Sharing to Force ?
> I have a new system that we have to support
> and there is alot literals filling up the
> pool. I have never changed this parameter
> from the default as many seemed to think the
> jury was still out on it. However, due to
> my situation, I figured I would try it out.
> If anyone has any experience with this one
> I would be curious to know what happened.
>
> Mike
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Johnson, Michael
> INET: Michael.Johnson_at_oln-afmc.af.mil
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
> Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Deshpande, Kirti
> INET: kirti.deshpande_at_verizon.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
> Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Don Granaman
> INET: granaman_at_cox.net
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
> Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu Jul 25 2002 - 08:38:23 CDT
