Performance Issue when Adding a Subline under a Usage Item, while Entering the Serial Number (Doc ID 2905157.1)

Last updated on OCTOBER 25, 2022

Applies to:

Symptoms

On : 12.2.4 version, Service Authoring

ACTUAL BEHAVIOR
---------------
Find that performance issue is seen when adding a subline under a usage item, while entering the serial number.

Here is the select giving the performance issue:

select *
from
 (select DECODE(SOURCE_OBJECT_CODE,'CP',:1,'CONTRACT_LINE',:2,NULL)
  ||';'||source_details reference, Name, Description, uom_code, id1, id2,
  object_code, source_id, counter_group_id,
template_counter_group_id,counter,
   serial_number, instance_number reference_number, external_reference from
  oks_usage_item_v where usage_item_id = :3 AND (rec_type = 'PARTY' OR (
  rec_type = 'CP' and :4 between TRUNC(NVL(start_date_active,:5)) and
  TRUNC(NVL(end_date_active, :6)) )) ORDER BY SOURCE_OBJECT_CODE) WHERE
  UPPER(REFERENCE) LIKE :7 AND (REFERENCE LIKE :8 OR REFERENCE LIKE :9)

EXPECTED BEHAVIOR
-----------------------
Expect no performance issue.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Using service contract manager responsibility, author a new contract.
2. Add a line as Usage and then add a subline.
3. Enter the serial number and the performance issue is observed.

Changes

Cause

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