My Oracle Support Banner

Slowness Observed While Inserting Person-person Relationship Using PersonPerson_DTO (Doc ID 2402389.1)

Last updated on MARCH 06, 2019

Applies to:

Oracle Financial Services Revenue Management and Billing - Version and later
Information in this document applies to any platform.


On :CCB 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_...............

Improved performance.

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.


To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.