Peoplesoft Question
Date: Fri, 20 Jul 2012 14:21:51 -0400
Message-ID: <5009A1BF.5000301_at_ardentperf.com>
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-lReceived on Fri Jul 20 2012 - 13:21:51 CDT