CURSOR_SHARING=SIMILAR

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 9.2.0.3 to 9.2.0.8. I've encountered bugs in Oracle 8 but was hoping that by 9.2.0.8 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:

http://oracle-online-help.blogspot.com/2006/12/cursorsharing-do-we-use-i 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

Notice:

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.

--
http://www.freelists.org/webpage/oracle-l