Poor Of Performance Of "Collect Level Values" request (Doc ID 2704255.1)

Last updated on AUGUST 25, 2020

Applies to:

Symptoms

On : 12.1.3 version, OTHER

ACTUAL BEHAVIOR
---------------
'Collect Level Values" Concurrent program is taking more than 5 hours to complete.

Below is the query which is taking huge time:

insert /*+ ALL_ROWS */ into MSD_ST_LEVEL_VALUES ( instance, level_id,
level_value, sr_level_pk, level_value_desc, attribute1, attribute2,
attribute3, attribute4, attribute5, dp_enabled_flag, last_update_date,
last_updated_by, creation_date, created_by ) select '1', 1, LEVEL_VALUE,
LEVEL_VALUE_PK, LEVEL_VALUE_DESC, attribute1, attribute2, attribute3,
attribute4, attribute5, dp_enabled_flag, sysdate, 4198, sysdate, 4198 from
MSD_SR_PRD_IT_V

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.10 0.18 0 0 0 0
Execute 1 18915.48 20368.69 1017066 2934805347 706607 303408
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 18915.59 20368.87 1017066 2934805347 706607 303408

EXPECTED BEHAVIOR
-----------------------
Expect the program to complete in less than 3 hours.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Enter the application.
2. Run the program.
3. See the above issue

Cause

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