Question on Cursor_sharing
- We are (still) on ORA 22.214.171.124 with default setting for CURSOR_SHARING. Heard some good thing about with setting of SIMILAR in Oracle 10g.Any success story for 126.96.36.199 / PeopleSoft HRMS with self serivce / and CURSOR_SHARING=SIMILAR.Advance thanks for your input.SP
- 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
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
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: firstname.lastname@example.org [mailto:email@example.com] On Behalf Of SP
Sent: Friday, January 09, 2009 3:27 AM
Subject: PeopleSoft DBA Forum Question on Cursor_sharingWe are (still) on ORA 188.8.131.52 with default setting for CURSOR_SHARING. Heard some good thing about with setting of SIMILAR in Oracle 10g.Any success story for 184.108.40.206 / PeopleSoft HRMS with self serivce / and CURSOR_SHARING=SIMILAR.Advance thanks for your input.SP
We were using CURSOR_SHARING=EXACT in our old enviroment (220.127.116.11 db
with psoft 8.42) and we are using the same setting in our new
environment (10.2.0.3 with psoft 9 finance).
The primary benefit of setting it to SIMILAR is for reducing hard
parses of sql with hard coded literals so that optimizer will end up
choosing the same plan for similar looking sql's (havent tested how
effective it is ).
Once again, in a big system like peoplesoft, you cant switch this
parameter without extensive testing. We had set the parameter to
EXACT right from the beginning of the upgrade and whatever statements
had issues, we fixed it manually using a bunch of hints, manipulating
stats, freezing stats for some tables etc., We still have some heavy
I/O statements that can be tuned. But, overall users are happy with
the performance of online and batch. Will get back to this system
maybe after a month to fix the sql's that are using unnecessary I/O's
and hoping that I dont have to touch this db for the next 3 years till
the next upgrade.
--- In firstname.lastname@example.org, SP <sripingali@...> wrote:
> We are (still) on ORA 18.104.22.168 with default setting for
CURSOR_SHARING. Heard some good thing about with setting of SIMILAR in
> Any success story for 22.214.171.124 / PeopleSoft HRMS with self serivce /
> Advance thanks for your input.