AE:PTSF_GENFEED SES Process Running For Long Time In Pre- Processing Step In Incremental Mode

(Doc ID 2235575.1)

Last updated on MAY 03, 2017

Applies to:

PeopleSoft Enterprise HCM Human Resources - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

On : 9.2 version, Employee/Organization Development

ACTUAL BEHAVIOR
---------------
AE:PTSF_GENFEED SES process running for long time in Pre- Processing step in incremental mode

Problem Statement:-

AE:PTSF_GENFEED SES process running for 10+ Hrs in Pre- Processing step(AE library: HRS_APP_SCH) while generating feed for search index: HC_HRS_APP_INDEX.

The below SQL is captured in DB AWR report on daily basis as the same SQL is available in Pre- Processing step(AE library: HRS_APP_SCH) and search index: HC_HRS_APP_INDEX feed processing have been scheduled for daily basis in our production.

INSERT INTO PS_HRS_APPACMP_TMP ( HRS_PERSON_ID , HRS_PROFILE_SEQ , JPM_PROFILE_ID , JPM_CAT_ITEM_ID , JPM_CAT_TYPE , JPM_CAT_ITEM_QUAL , JPM_CAT_ITEM_QUAL2 , JPM_DESCR90 ) SELECT DISTINCT A.HRS_PERSON_ID , A.HRS_PROFILE_SEQ , A.JPM_PROFILE_ID , A.JPM_CAT_ITEM_ID , A.JPM_CAT_TYPE , A.JPM_CAT_ITEM_QUAL , A.JPM_CAT_ITEM_QUAL2 , A.JPM_DESCR90 FROM PS_HRS_APPITM03_VW A , PS_HRS_APPI_ALL_VW B WHERE A.HRS_PERSON_ID = B.HRS_PERSON_ID AND A.HRS_PROFILE_SEQ = B.HRS_PROFILE_SEQ;

Our observations and commentary:-

As per our initial analysis, The view:PS_HRS_APPITM03_VW returns vallue from below two set of data(Record:ps_hrs_app_items data and Profile content setup data).

1.Record:ps_hrs_app_items having 2216687 rows in our system.
2.The Profile content setup have been additionaly increased around 1000+ rows in last two months in our system as new competency have been brought in to our application. The count details are below,
TO_CHAR(LASTUPDDTTM,'YYYYMM') COUNT(JPM_CAT_TYPE)
201610 59
201609 900
201608 52
201606 2
201601 1
201512 25
201511 9
201510 25
201509 7
201508 2
201507 3
201504 563
201409 3
201408 105
201407 1
201406 5
201212 5
200712 5
200601 3


STEPS
-----------------------
The issue can be reproduced with the following steps:
1. Navigation : PeopleTools > Search Framework > Administration > Schedule Search Index
2. Add Run Control ID
3. Enter Search Definition : HC_HRS_APP_INDEX
4. Select Incremental index
5. Save and Run the Process

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
1.Resourcing module functionality impacted on applicant's data search.
2.Impacting system performance and it hold other scheduled jobs from on time execution.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms