I did this on 6.0.1 and 7.0.01. The difference is in the amount of memory allocated in the shared pool (drops to ~1/5 of original allocation) and the number of hard parses. When the cursor_sharing is set to exact, there is nearly a 1:1 ration between sql statements and hard parses. This drops very significantly (maybe 1/100 to 1/1000, depending on how your server is used). It's more of a scalability issue that this addresses than anything. If running 32-bit Oracle, you can only address 4gb of memory; this can easily be exhaused due to the poor usage of the shared pool because every statements gets its own cursor if the number of threads is increased beyond a certain limit.
Axton Grams On 2/13/07, Grooms, Frederick W <[EMAIL PROTECTED]> wrote:
** Does anyone using Remedy 6.3 (against Oracle 10g) have their cursor_sharing set to similiar, and did they notice any improvments? Fred ------------------------------ *From:* Action Request System discussion list(ARSList) [mailto: [EMAIL PROTECTED] *On Behalf Of *Axton *Sent:* Tuesday, February 13, 2007 12:41 PM *To:* [email protected] *Subject:* Re: Oracle LOB relocation Slightly OT, but you may want to instruct your dba's to set the oracle instance parameter 'cursor_sharing' to similar or force. This will greatly reduce the size requirements of oracle's shared pool and greatly reduce hard parses. This whitepaper covers some of the details: http://documents.bmc.com/supportu/documents/78/31/67831/67831.pdf My favorite quote from this doc: "Any application that uses all literals without cursor sharing enabled is not a very scalable application." Axton Grams __20060125_______________________This posting was submitted with HTML in it___
_______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
