RE: Peoplesoft Question

From: David Kurtz <info_at_go-faster.co.uk>
Date: Fri, 20 Jul 2012 20:57:05 +0100
Message-ID: <00cc01cd66b1$d6f86520$84e92f60$_at_go-faster.co.uk>

I think Duncan's blog posting is a separate issue. App Engine frequently resolves internal bind variables to literals before submitting them to the database. This behaviour can be adjusted with the ReUseStatement flag on the AE steps so that binds hit the database as binds.

However, in this case I think this is not the issue, because nearly all of what this process does is in PeopleCode (PeopleSoft's proprietary language), and again seems to be doing.

Jeremy - if you can find a way for me to see the trace file (or a profile of it) and if you can also obtain an AE timings report (AETrace=1159) and a PeopleTools trace (SQLTrace=7), I'd be happy to have a look.

If we run out of PeopleSoft options, then a possible option would be to set cursor sharing at session level for just this AE process with a trigger on PSPRCSRQST when RUNSTATUS='7'.

regards

---

David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
mailto:david.kurtz_at_go-faster.co.uk
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

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Johan Muller
Sent: 20 July 2012 19:39
To: jeremy.schneider_at_ardentperf.com
Cc: oracle-l_at_freelists.org
Subject: Re: Peoplesoft Question
Importance: High

This may or may not be the answer but it has a very interesting solution:

http://peoplesofttipster.com/2011/02/03/error-in-recruitment-build-applicant -search/

A quote from the article:

"Interesting, so I have a missing dll. Using Process Monitor (the SysInternals one, not PeopleSoft Process Monitor) I can see that it's looking for the dll in '<ps_home>\verity\winx86\_nti40\bin\'.

Once I located a copy of the DLL and copied it there the HRS_SRCH_APP process ran fine and the directories were created for all languages.

So why is this file missing? I believe it's an issue of Oracle assuming that Microsoft bundles it in Windows, and Microsoft no longer doing so. See this from Microsoft:

    The shared CRT DLL has been distributed by Microsoft in the past as a shared system component. This may cause problems when you run applications that are linked to a different version of the CRT on computers that do not have the correct versions of the CRT DLL installed. This is commonly referred to as the "DLL Conflict" problem.  To address this issue, the CRT DLL is no longer considered a system file, therefore, distribute the CRT DLL with any application that relies on it."

Hope it helps.

On Fri, Jul 20, 2012 at 1:21 PM, Jeremy Schneider <jeremy.schneider_at_ardentperf.com> wrote:
> Quick question for any peoplesoft DBAs out there...
> I'm trying to help a client troubleshoot a peoplesoft issue. This is
> more of an app tier question... but I just thought maybe someone in
> this list has run across it or knows if there's already a peoplesoft
> patch or known fix.
>
> Process Scheduler Job HRS_SRCH_IDX (Building the Applicant Index
> Search) should be running in 2-4 hrs for us, but after upgrading to
> peoplesoft 9 on oracle 11r2 it's running 20+ hrs before we kill it.
> The process seems to be spending 94% of its time parsing one specific SQL.
>
> During a 2.5 hour 10046 trace of the HRS_SRCH_IDX process...
> 1) 58,000 SQL statements were parsed in less than one microsecond each
> (probably "repeat" SQL w/bind vars)
> 2) 7,349 SQL statements were parsed in 1 to 10 centiseconds (probably
> unique SQL w/o bind vars)
> 3) One specific SQL executed 3,408 times and always took 2-3 seconds
> to parse
> 4) This SQL executes almost instantly, but the 3,408 parses account
> for 2hr16min out of the 2hr25min trace. In other words, parsing for
> this single SQL is the problem
> 5) This SQL is a simple select against the peoplesoft view
> PS_HRS_APPITM03_VW for a single HRS_PERSON_ID/HRS_PROFILE_SEQ and it
> does not use bind variables
> 6) I manually used "explain plan" to testing the parse time for this
> SQL on two different test systems. Results consistent with above
findings.
>
> Anyone heard of a peoplesoft patch that changes this view definition
> or the SQL executed by this process? (Perhaps to use bind vars?) For
> obvious reasons I'm holding off on suggesting anything that involves
> cursor_sharing in case we can get a peoplesoft solution.
>
> -Jeremy
>
> P.S. props to mr trace for instantly turning an 80M tracefile into the
> stats mentioned above :)
>
> --
> Jeremy Schneider
> Chicago
>
> +1 312-725-9249
> http://www.ardentperf.com
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
Johan Muller
Oracle DBA
817 | 715 | 4813 or  817 | 247 | 0260 fax 817 | 523 | 4942 Text :
8177154813_at_tmomail.net
--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jul 20 2012 - 14:57:05 CDT