Search the web
Sign In
New User? Sign Up
psftdba · PeopleSoft DBA Forum
  • Members Only
  • Post
  • Files
  • Photos
  • Links
  • Database
  • Polls
  • Members
  • Promote
  • Groups Labs (Beta)
? Already a member? Sign in to Yahoo!

Yahoo! Groups Tips

Did you know...
Hear how Yahoo! Groups has changed the lives of others. Take me there.

Best of Y! Groups

   Check them out and nominate your group.
Having problems with message search? Fill out this form to ensure your group is one of the first to be migrated to the new message search system.

Messages

   Messages Help
Message #
Search:
Advanced
Question on Cursor_sharing   Message List  
Reply | Forward Message #3531 of 3932 |
RE: PeopleSoft DBA Forum Question on Cursor_sharing

I have experimented with CURSOR_SHARING, and I would not recommend setting this parameter instance-wide to non-default values.  It is not always beneficial.  Every time I have tried it, I have always found something else in the system that reacts badly.  However, I have had some success setting it at session level for specific batch programs. In which cases, I set it with a trigger on PSPRCSRQST which only fires for specified process - that way I don't have to customise any applications.
 
It can be effective to set cusror sharing to similar on
* SQR processes with contain a lot of dynamically generated SQL. 
* Application Engine processes with a lot of loops in their processing (and which do not use the REUSE STATEMENT option).
These process can consume a lot of time of SQL parse. 
 
Cursor Sharing is about whether Oracle needs to hard parse a SQL statement, or whether it recognises a SQL statement with different litterals as the same statement because litteral values have been converted to bind variables. 
 
The parse overhead can also be significantly increased (up to 4x) on unicode databases (prior to PeopleSoft Application version 9) because PeopleSoft created length checking constraints on all character columns instead of using Character Semantics.
 
 
regards
_________________________
David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
mailto:david.kurtz@...
web: www.go-faster.co.uk
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle: http://blog.go-faster.co.uk
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
 

From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of SP
Sent: Friday, January 09, 2009 3:27 AM
To: psftdba@yahoogroups.com
Subject: PeopleSoft DBA Forum Question on Cursor_sharing

We are (still) on ORA 9.2.0.8 with default setting for CURSOR_SHARING. Heard some good thing about with setting of SIMILAR in Oracle 10g.  
 
Any success story for  9.2.0.8 / PeopleSoft HRMS with self serivce / and CURSOR_SHARING=SIMILAR.
 
Advance thanks for your input.
 
SP



Fri Jan 9, 2009 10:06 am

Show Message Option
davidkurtz
Offline Offline
Send Email Send Email

Forward 		Message #3531 of 3932 |
Expand Messages Author Sort by Date

We are (still) on ORA 9.2.0.8 with default setting for CURSOR_SHARING. Heard some good thing about with setting of SIMILAR in Oracle 10g.     Any success... 		SP
sripingali
Online Now Send Email 		Jan 9, 2009
9:45 am

I have experimented with CURSOR_SHARING, and I would not recommend setting this parameter instance-wide to non-default values. It is not always beneficial....
David Kurtz
davidkurtz
Offline Send Email 		Jan 9, 2009
10:06 am

SP, We were using CURSOR_SHARING=EXACT in our old enviroment (9.2.0.3 db with psoft 8.42) and we are using the same setting in our new environment (10.2.0.3...
shajivps
Offline Send Email 		Jan 9, 2009
5:18 pm
< Prev Topic  |  Next Topic >
Message #
Search:
Advanced
Copyright © 2009 Yahoo! Inc. All rights reserved.
Privacy Policy - Terms of Service - Guidelines - Help