Re: Peoplesoft Question
- From: Jeremy Schneider <jeremy.schneider@xxxxxxxxxxxxxx>
- To: info@xxxxxxxxxxxxxxx
- Date: Fri, 20 Jul 2012 16:38:13 -0400
Oracle Support made several suggestions, most of which were worthless
(such as increasing the SGA size because we were parsing a lot).
But as *we* all know, Peoplesoft uses many hopelessly broken SQL
statements which are only usable if the database acts like we're still
living in the mid-90s. Thus, buried in the recommendations, Oracle
Support recommended to set the underscore parameters for disabling
subquery unnesting and hash-based group-by aggregations.
As soon as we disabled subquery unnesting, the parse time for this SQL
statement went from 2 seconds to nearly instant. (It was easy to test
at a session level.) If I didn't already understand that peoplesoft
exists in the alternate reality of another universe, I would be
horrified. But luckily I only feel the satisfaction of going into the
weekend with one less open issue in by queue.
Thanks all for the suggestions, and if you have any further ideas
they're most welcome. :)
Cheers,
Jeremy
--
Jeremy Schneider
Chicago
+1 312-725-9249
http://www.ardentperf.com
On 07/20/2012 03:57 PM, David Kurtz wrote:
> 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@xxxxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Johan Muller
> Sent: 20 July 2012 19:39
> To: jeremy.schneider@xxxxxxxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxxx> 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
>>
>>
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>
>
> --
> Johan Muller
> Oracle DBA
> 817 | 715 | 4813 or 817 | 247 | 0260 fax 817 | 523 | 4942 Text :
> 8177154813@xxxxxxxxxxx
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
