Slowness Observed While Inserting Person-person Relationship Using PersonPerson_DTO
Last updated on MAY 29, 2018
Applies to:Oracle Financial Services Revenue Management and Billing - Version 188.8.131.52.0 and later
Information in this document applies to any platform.
On :CCB 184.108.40.206.0 version, BT - Batch
Slowness observed while inserting person-person relationship using PersonPerson_DTO.
Custom batch which maintains Person/Account and Person Person relationship.
Last execution of the batch had around 6000 person/account maintenance records and around 6000 records for person person and it took 7+ hours to complete the execution on production server.
Have some degree of multi-threading but not all the records could be processed in parallel due to business reasons.
On further investigation, it is observed that interaction on PersonPerson_DTO for add/update takes a long time. If we try to add 1 parent-child records using PersonPerson_DTO then system retrieves all the existing children of Person1 and Person2.
In our setup it is usual to have 100,000+ relationship records for many Person object.
AWR shows unusually high number of execution for one specific query that involves CI_PER_PER and CI_PER_PER_CHAR table.
select personpers0_.PER_REL_TYPE_CD as PER1_873_1_, personpers0_.PER_ID1 as PER2_873_1_, personpers0_.PER_ID2 as PER3_873_1_, personpers0_.START_DT as START4_873_1_, personpers0_.VERSION as VERSION873_1_, personpers0_.END_DT as END6_873_1_, personpers0_.FINAN_REL_SW as FINAN7_873_1_, personpers1_.PER_REL_TYPE_CD as PER1_873_3_, personpers1_.PER_ID1 as PER2_873_3_, personpers1_.PER_ID2 as PER3_873_3_, personpers1_.START_DT as START4_873_3_, personpers1_.PER_REL_TYPE_CD as PER1_3_, personpers1_.PER_ID1 as PER2_3_, personpers1_.PER_ID2 as PER3_3_, personpers1_.START_DT as START4_3_, personpers1_.CHAR_TYPE_CD as CHAR5_3_, personpers1_.EFFDT as EFFDT3_, personpers1_.PER_REL_TYPE_CD as PER1_1050_0_, personpers1_.PER_ID1 as PER2_1050_0_, personpers1_.PER_ID2 as PER3_1050_0_, personpers1_.START_DT as START4_1050_0_, personpers1_.CHAR_TYPE_CD as CHAR5_1050_0_, personpers1_.EFFDT as EFFDT1050_0_, personpers1_.VERSION as VERSION1050_0_, personpers1_.SRCH_CHAR_VAL as SRCH8_ 1050_0_, personpers1_.CHAR_VAL as CHAR9_1050_0_...............
The issue can be reproduced at will with the following steps:
1. Execute Custom batch.
The issue has the following business impact:
The performance issue in affecting our SLA and ability to complete processing on time.
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