RE: CURSOR_SHARING=SIMILAR

We are using SIMILAR selectively for some programs and for some users
(in 9.2.0.7). For third-party applications we had to use this because we
could not get those fixed. Actually there is one database used by a third-party application that we have even set FORCE for the instance
(this is 9.0.1).

I have not seen any problems with this usage, but you can still have many versions if you use SIMILAR, especially if you have histograms. SIMILAR can decrease the possibility of shared pool errors but may not get rid of them completely.

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Leng Kaing Sent: Tuesday, February 06, 2007 12:37 AM To: oracle-l_at_freelists.org
Subject: 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.

Bu mesaj ve onunla iletilen tum ekler gonderildigi kisi ya da kuruma ozel ve Bankalar Kanunu geregince, gizlilik yukumlulugu tasiyor olabilir. Bu mesaj, hicbir sekilde, herhangi bir amac icin cogaltilamaz, yayinlanamaz ve para karsiligi satilamaz; mesajin yetkili alicisi veya alicisina iletmekten sorumlu kisi degilseniz, mesaj icerigini ya da eklerini kopyalamayiniz, yayinlamayiniz, baska kisilere yonlendirmeyiniz ve mesaji gonderen kisiyi derhal uyararak bu mesaji siliniz. Bu mesajin iceriginde ya da eklerinde yer alan bilgilerin dogrulugu, butunlugu ve guncelligi Bankamiz tarafindan garanti edilmemektedir ve bilinen viruslere karsi kontrolleri yapilmis olarak yollanan mesajin sisteminizde yaratabilecegi zararlardan Bankamiz sorumlu tutulamaz.

This message and the files attached to it are under the privacy liability in accordance with the Banking Law and confidential to the use of the individual or entity to whom they are addressed. This message cannot be copied, disclosed or sold monetary consideration for any purpose. If you are not the intended recipient of this message, you should not copy, distribute, disclose or forward the information that exists in the content and in the attachments of this message; please notify the sender immediately and delete all copies of this message. Our Bank does not warrant the accuracy, integrity and currency of the information transmitted with this message. This message has been detected for all known computer viruses thence our Bank is not liable for the occurrence of any system corruption caused by this message

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