Folks, Just to be clear and to not send folks down an incorrect path.
Setting the cursor sharing option for Oracle has a SIGNIFICANT impact on performance. Setting at the Oracle level and then telling the AR System through the ar.conf setting is important. (you may be able to just set it at the ar.conf level -- check with SQL logging turned on during a startup and see if we set it at that time -- and not set it at the Oracle system level. but the documentation on the issue has the right set of directions on what to do.) We have found that cursor sharing of forced or shared causes CLOB operations to have problems under some conditions. The operation seems to hang within Oracle. The same operation with cursor sharing set to exact has no problem at any time. So, there is logic that checks the setting you have made for cursor sharing and if it is forced or shared, we change it to exact and then change it back to forced or shared as you had it configured. If you have not set ar.conf settings, we don't change the cursor sharing around these operations you will not see the commands that are reflected in this log, we just let the database configuration do what it does. So, the setting is critical to move away from the default to the one we recommend in the documentation for best performance. It is important to set the ar.conf setting to the option you configured. All of this together lets us do the right thing to avoid unexpected hangs with Oracle and to get the best possible performance out of the database layer. There is no bug here and the setting is important to make to get the best throughput from the system. Doug Mueller ________________________________ From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Guillaume Rheault Sent: Monday, November 29, 2010 12:41 PM To: [email protected] Subject: Re: ORACLE Cursor Sharing ** Hi Conny, Is that a bug? is BMC support aware of this? Guillaume ________________________________ From: Action Request System discussion list(ARSList) [[email protected]] on behalf of Conny Martin [[email protected]] Sent: Monday, November 29, 2010 3:13 PM To: [email protected] Subject: AW: ORACLE Cursor Sharing ** Frank, IMHO this parameter does not have an effect on performance. Somtimes arserverd issues an "alter session set cursor_sharing = exact" for some reason. To reset it back to it's desired value you have to specify it in ar.cfg <SQL > <TID: 1081108800> <RPC ID: 0000013703> <Queue: Fast > <Client-RPC: 390620 > <USER: xxx > /* So Nov 14 2010 08:21:24.2067 */INSERT INTO B233C536870999 (C536870999, entryId) VALUES (:x,'W00000015085672') <SQL > <TID: 1081108800> <RPC ID: 0000013703> <Queue: Fast > <Client-RPC: 390620 > <USER: xxx > /* So Nov 14 2010 08:21:24.2068 */Set LOB into the above row ... <SQL > <TID: 1081108800> <RPC ID: 0000013703> <Queue: Fast > <Client-RPC: 390620 > <USER: xxx > /* So Nov 14 2010 08:21:24.2068 */ALTER SESSION set CURSOR_SHARING = EXACT <SQL > <TID: 1081108800> <RPC ID: 0000013703> <Queue: Fast > <Client-RPC: 390620 > <USER: xxx > /* So Nov 14 2010 08:21:24.2070 */OK <SQL > <TID: 1081108800> <RPC ID: 0000013703> <Queue: Fast > <Client-RPC: 390620 > <USER: xxx > /* So Nov 14 2010 08:21:26.9983 */OK <SQL > <TID: 1081108800> <RPC ID: 0000013703> <Queue: Fast > <Client-RPC: 390620 > <USER: xxx > /* So Nov 14 2010 08:21:26.9983 */ALTER SESSION set CURSOR_SHARING = FORCE Don't know why, but always when inserting an attachment these alter session statements are issued. Kind Regards Conny ________________________________ Von: Action Request System discussion list(ARSList) [mailto:[email protected]] Im Auftrag von Frank Caruso Gesendet: Montag, 22. November 2010 19:46 An: [email protected] Betreff: ORACLE Cursor Sharing ** Does anyone know what effect setting the Oracle Cursor Sharing parameter in the ar.cfg has on performance? I understand what cursor sharing is and have it set at the Oracle (database) level but want to know why it needs to be set in the ARS server config file. Thank you, _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
