| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Cursor Sharing
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:
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!
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).
-----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-LReceived on Thu Jul 25 2002 - 01:03:23 CDT
(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).
 |
 |