psftdba : Message: RE: PeopleSoft DBA Forum Question on Cursor

Message #3531 of 3996 |

From david.kurtz@... Fri Jan 09 02:06:52 2009
Return-Path: <david.kurtz@...>
X-Sender: david.kurtz@...
X-Apparently-To: psftdba@yahoogroups.com
X-Received: (qmail 17000 invoked from network); 9 Jan 2009 10:06:52 -0000
X-Received: from unknown (66.218.67.97)
by m55.grp.scd.yahoo.com with QMQP; 9 Jan 2009 10:06:52 -0000
X-Received: from unknown (HELO mail-ew0-f20.google.com) (209.85.219.20)
by mta18.grp.scd.yahoo.com with SMTP; 9 Jan 2009 10:06:51 -0000
X-Received: by ewy13 with SMTP id 13so10387381ewy.22
for <psftdba@yahoogroups.com>; Fri, 09 Jan 2009 02:06:50 -0800 (PST)
X-Received: by 10.210.12.13 with SMTP id 13mr14687565ebl.160.1231495610715;
Fri, 09 Jan 2009 02:06:50 -0800 (PST)
Return-Path: <david.kurtz@...>
X-Received: from GOFASTER4 (host86-133-233-127.range86-133.btcentralplus.com [86.133.233.127])
by mx.google.com with ESMTPS id i6sm8017709gve.26.2009.01.09.02.06.47
(version=SSLv3 cipher=RC4-MD5);
Fri, 09 Jan 2009 02:06:49 -0800 (PST)
To: <psftdba@yahoogroups.com>
References: <1210234844.660.68706.m46@yahoogroups.com> <460276.52876.qm@...>
Date: Fri, 9 Jan 2009 10:06:36 -0000
Organization: Go-Faster Consultancy Ltd.
Message-ID: <881EECDB6DE1433EB7B02B1F70C77D50@GOFASTER4>
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_002D_01C97241.F72900B0"
X-Mailer: Microsoft Office Outlook 11
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
In-Reply-To: <460276.52876.qm@...>
Thread-Index: AclyPwUMfHFvuXAjStyk4xwB5cjCAQAAE96Q
X-Originating-IP: 209.85.219.20
X-eGroups-Msg-Info: 1:12:0:0:0
From: "David Kurtz" <david.kurtz@...>
Reply-To: <david.kurtz@...>
Subject: RE: PeopleSoft DBA Forum Question on Cursor_sharing
X-Yahoo-Group-Post: member; u=84939950; y=Bdplo4o4f7q53cm-0cDuN9-5F9xyEv1cWZFaAZl1um2spWepVg
X-Yahoo-Profile: davidkurtz

------=_NextPart_000_002D_01C97241.F72900B0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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
<http://www.psftdba.com/>
DBA Blogs: PeopleSoft: http://blog.psftdba.com <http://blog.psftdba.com/> ,
Oracle: http://blog.go-faster.co.uk <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

------=_NextPart_000_002D_01C97241.F72900B0
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dus-ascii">
<STYLE type=3Dtext/css>#ygrp-mkp {
BORDER-RIGHT: #d8d8d8 1px solid; PADDING-RIGHT: 14px; BORDER-TOP: #d8d8d8 =
1px solid; PADDING-LEFT: 14px; PADDING-BOTTOM: 0px; MARGIN: 14px 0px; BORDE=
R-LEFT: #d8d8d8 1px solid; PADDING-TOP: 0px; BORDER-BOTTOM: #d8d8d8 1px sol=
id; FONT-FAMILY: Arial
}
#ygrp-mkp HR {
BORDER-RIGHT: #d8d8d8 1px solid; BORDER-TOP: #d8d8d8 1px solid; BORDER-LEF=
T: #d8d8d8 1px solid; BORDER-BOTTOM: #d8d8d8 1px solid
}
#ygrp-mkp #hd {
FONT-WEIGHT: bold; FONT-SIZE: 85%; MARGIN: 10px 0px; COLOR: #628c2a; LINE-=
HEIGHT: 122%
}
#ygrp-mkp #ads {
MARGIN-BOTTOM: 10px
}
#ygrp-mkp .ad {
PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; PADDING-TOP: 0=
px
}
#ygrp-mkp .ad A {
COLOR: #0000ff; TEXT-DECORATION: none
}
</STYLE>

<META content=3D"MSHTML 6.00.6000.16788" name=3DGENERATOR></HEAD>
<BODY>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D359484709-09012009><FONT face=3DA=
rial=20
color=3D#0000ff size=3D2>I have experimented with CURSOR_SHARING, and I wou=
ld not=20
recommend setting this parameter instance-wide to non-default values. =
It=20
is not always beneficial.  Every time I have tried it, I have alw=
ays=20
found something else in the system that reacts badly.  </FONT></SPAN><=
SPAN=20
class=3D359484709-09012009><FONT face=3DArial color=3D#0000ff size=3D2>Howe=
ver, I have=20
had some success setting it at session level for specific batch programs. I=
n=20
which cases, I set it with a trigger on PSPRCSRQST which only fires for=20
specified process - that way I don't have to customise any=20
applications.</FONT></SPAN></DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D359484709-09012009><FONT face=3DA=
rial=20
color=3D#0000ff size=3D2></FONT></SPAN> </DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D359484709-09012009><FONT face=3DA=
rial=20
color=3D#0000ff size=3D2>It can be effective to set cusror sharing to simil=
ar=20
on</FONT></SPAN></DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D359484709-09012009><FONT face=3DA=
rial=20
color=3D#0000ff size=3D2>* SQR processes with contain a lot of dynamically =
generated=20
SQL.  </FONT></SPAN></DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D359484709-09012009></SPAN><SPAN=20
class=3D359484709-09012009><FONT face=3DArial color=3D#0000ff size=3D2>* Ap=
plication=20
Engine processes with a lot of loops in their processing (and which do not =
use=20
the REUSE STATEMENT option).</FONT></SPAN></DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D359484709-09012009><FONT face=3DA=
rial=20
color=3D#0000ff size=3D2>These process can consume a lot of time of SQL par=
se. =20
</FONT></SPAN></DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D359484709-09012009><FONT face=3DA=
rial=20
color=3D#0000ff size=3D2></FONT></SPAN> </DIV><SPAN=20
class=3D359484709-09012009><FONT face=3DArial color=3D#0000ff size=3D2>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D359484709-09012009><FONT face=3DA=
rial=20
color=3D#0000ff size=3D2>Cursor Sharing is about whether Oracle needs to ha=
rd parse=20
a SQL statement, or whether it recognises a SQL statement with=20
different litterals as the same statement because litteral values have been=
=20
converted to bind variables.  </FONT></SPAN></DIV>
<DIV> </DIV>
<DIV dir=3Dltr align=3Dleft>The parse overhead can also be significantly in=
creased=20
(up to 4x) on unicode databases (prior to PeopleSoft Application version 9)=
=20
because PeopleSoft created length checking constraints on all character col=
umns=20
instead of using Character Semantics.</FONT></SPAN></DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D359484709-09012009><FONT face=3DA=
rial=20
color=3D#0000ff size=3D2></FONT></SPAN> </DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D359484709-09012009><FONT face=3DA=
rial=20
color=3D#0000ff size=3D2></FONT></SPAN> </DIV>
<DIV dir=3Dltr align=3Dleft><FONT=20
size=3D2>regards<BR>_________________________<BR>David Kurtz<BR>Go-Faster=20
Consultancy Ltd.<BR>tel: +44 (0)7771 760660<BR>fax: +44 (0)7092 348865<BR><=
A=20
href=3D"mailto:david.kurtz@...">mailto:david.kurtz@...=
.uk</A><BR>web:=20
www.go-faster.co.uk<BR>Book: PeopleSoft for the Oracle DBA: <A=20
href=3D"http://www.psftdba.com/">http://www.psftdba.com</A><BR>DBA Blogs:=20
PeopleSoft: <A href=3D"http://blog.psftdba.com/">http://blog.psftdba.com</A=
>,=20
Oracle: <A=20
href=3D"http://blog.go-faster.co.uk/">http://blog.go-faster.co.uk</A><BR>Pe=
opleSoft=20
DBA Forum: <A=20
href=3D"http://groups.yahoo.com/group/psftdba">http://groups.yahoo.com/grou=
p/psftdba</A></FONT>=20
</DIV>
<DIV> </DIV><BR>
<BLOCKQUOTE=20
style=3D"PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px soli=
d; MARGIN-RIGHT: 0px">
<DIV class=3DOutlookMessageHeader lang=3Den-us dir=3Dltr align=3Dleft>
<HR tabIndex=3D-1>
<FONT face=3DTahoma size=3D2><B>From:</B> psftdba@yahoogroups.com=20
[mailto:psftdba@yahoogroups.com] <B>On Behalf Of </B>SP<BR><B>Sent:</B>=20
Friday, January 09, 2009 3:27 AM<BR><B>To:</B>=20
psftdba@yahoogroups.com<BR><B>Subject:</B> PeopleSoft DBA Forum Question =
on=20
Cursor_sharing<BR></FONT><BR></DIV>
<DIV></DIV><FONT face=3DArial color=3D#0000ff size=3D2></FONT>
<TABLE cellSpacing=3D0 cellPadding=3D0 border=3D0>
<TBODY>
<TR>
<TD vAlign=3Dtop>
<DIV>We are (still) on ORA 9.2.0.8 with default setting for=20
CURSOR_SHARING. Heard some good thing about with setting of SIMILAR=
in=20
Oracle 10g.   </DIV>
<DIV> </DIV>
<DIV>Any success story for  9.2.0.8 / PeopleSoft HRMS with sel=
f=20
serivce / and CURSOR_SHARING=3DSIMILAR. </DIV>
<DIV> </DIV>
<DIV>Advance thanks for your input. </DIV>
<DIV> </DIV>
<DIV>SP </DIV></TD></TR></TBODY></TABLE><BR><SPAN style=3D"COLOR: w=
hite"=20
width=3D"1"></SPAN> </BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_002D_01C97241.F72900B0--

Fri Jan 9, 2009 10:06 am

Show Message Option

"David Kurtz" <david.kurtz@...>

davidkurtz

Offline

Send Email

Yahoo! Groups Tips

Did you know...

Messages

Yahoo! My Yahoo! Mail
	Sign In New User? Sign Up
		Tech - Groups - Help

Yahoo! Groups Tips

Did you know...

Best of Y! Groups

Messages