Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> CURSOR_SHARING=SIMILAR


From: Leng Kaing <>
Date: Tue, 6 Feb 2007 09:37:21 +1100
Message-ID: <>

Hi All,

Is anyone using the CURSOR_SHARING=SIMILAR out there? Our program has a lot of literals (yeah, try getting the developers to change!) and I was wondering if we could try and use CURSOR_SHARING=SIMILAR to help the shared pool...

We've got databases running Oracle to I've encountered bugs in Oracle 8 but was hoping that by it's all very safe now. Can I turn it on again? Have you got any nasty side effects to share.

Also, in my search of google I came across this blog: t.html

"Oracle does not recommend setting CURSOR_SHARING to FORCE in a DSS environment or if you are using complex queries. Also, star transformation is not supported with CURSOR_SHARING set to either SIMILAR or FORCE"

I haven't seen this documented in metalink, ie. star transformation is not supported. Is this correct? And how does a star transformation come about? I thought that they are generated via a HINT, init.ora parameter or when the optimizer thinks it's appropriate. If it's the latter (ie. optimizer) I'd have a very hard time deciding what queries will be affected by the CURSOR_SHARING=SIMILAR hint. How do you guys deal with it? We have clients who keeps on asking if they can turn this parameter on and I keep on quoting bugs and stuff. I'd really like to know if anyone is using it successfully out there.

TIA, Leng.

Leng Kaing

Senior Oracle DBA

Hansen Technologies; 2 Frederick St; Doncaster 3108

Ph: +61-3-9840-3832


This e-mail and any attachments are confidential and may contain legally privileged information and/or copyright material of Hansen Technologies Limited or third parties. Copying, distributing, disclosing, commercialising or otherwise acting in reliance on this e-mail and any attachments is strictly prohibited unless you are the addressee of this e-mail and have written permission to do so. If you have received this e-mail in error please delete this e-mail (including any copies and attachments) and contact Hansen Technologies Limited by return e-mail or by telephone on + 61 3 9840 3000. Any views expressed in this e-mail are those of the individual sender and may not necessarily reflect the views of or be a commitment by the organisation, except where the individual sender has the authority and expressly states them to be so.

Received on Mon Feb 05 2007 - 16:37:21 CST

Original text of this message