Is It Possible To Improve The Performance Of System While Creating Visit Task In Visit Form
(Doc ID 2860477.1)
Last updated on APRIL 05, 2022
Applies to:
Oracle Complex Maintenance, Repair, & Overhaul - Version 12.2.9 and laterInformation in this document applies to any platform.
Goal
After upgrade to 12.2.9 release while creating visit task in visit form it takes more time.
From the tkprof file found the following sql queries taking time:
INSERT INTO PJI_FP_AGGR_XBS_T ( STRUCT_TYPE, PRG_GROUP, STRUCT_EMT_ID,
STRUCT_VERSION_ID, SUP_PROJECT_ID, SUP_ID, SUP_EMT_ID, SUBRO_ID, SUB_ID,
SUB_EMT_ID, SUP_LEVEL, SUB_LEVEL, SUB_ROLLUP_ID, SUB_LEAF_FLAG_ID,
SUB_LEAF_FLAG, RELATIONSHIP_TYPE, STATUS_ID, WORKER_ID )
VALUES
( 'WBS', NULL, :B15 , :B14 , :B13 , :B12 , :B11 , :B10 , :B9 , :B8 , :B7 -1,
:B6 , NULL, :B5 , :B4 , DECODE(:B3 , 'SHARE_FULL', 'WF', 'SHARE_PARTIAL',
DECODE(NVL(:B2 , 'N'), 'Y', 'WF', 'LW'), 'SPLIT_MAPPING', DECODE(NVL(:B2 ,
'N'), 'Y', 'LF', 'LW'), 'SPLIT_NO_MAPPING', DECODE(NVL(:B2 , 'N'), 'Y',
'LF', 'LW'), 'PJI$NULL', DECODE(NVL(:B2 , 'N'), 'Y', 'LF', 'LW')),
'children', :B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- --------------------
Parse 1 0.00 0.00 0 0 0 0
Execute 22252 67.08 67.06 0 908 70203 22252
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- --------------------
total 22253 67.08 67.06 0 908 70203 22252
SELECT WDT_CHILD1.SUB_LEVEL
FROM
PJI_FP_AGGR_XBS_T WDT_CHILD1 WHERE 1=1 AND WDT_CHILD1.SUP_ID = :B2 AND
WDT_CHILD1.SUP_ID = WDT_CHILD1.SUB_ID AND WDT_CHILD1.WORKER_ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- --------------------
Parse 1 0.00 0.00 0 0 0 0
Execute 22252 9.28 9.28 0 0 0 0
Fetch 22252 53.34 53.33 0 4481760 0 22252
------- ------ -------- ---------- ---------- ---------- ---------------------
total 44505 62.62 62.61 0 4481760 0 22252
STRUCT_VERSION_ID, SUP_PROJECT_ID, SUP_ID, SUP_EMT_ID, SUBRO_ID, SUB_ID,
SUB_EMT_ID, SUP_LEVEL, SUB_LEVEL, SUB_ROLLUP_ID, SUB_LEAF_FLAG_ID,
SUB_LEAF_FLAG, RELATIONSHIP_TYPE, STATUS_ID, WORKER_ID )
VALUES
( 'WBS', NULL, :B15 , :B14 , :B13 , :B12 , :B11 , :B10 , :B9 , :B8 , :B7 -1,
:B6 , NULL, :B5 , :B4 , DECODE(:B3 , 'SHARE_FULL', 'WF', 'SHARE_PARTIAL',
DECODE(NVL(:B2 , 'N'), 'Y', 'WF', 'LW'), 'SPLIT_MAPPING', DECODE(NVL(:B2 ,
'N'), 'Y', 'LF', 'LW'), 'SPLIT_NO_MAPPING', DECODE(NVL(:B2 , 'N'), 'Y',
'LF', 'LW'), 'PJI$NULL', DECODE(NVL(:B2 , 'N'), 'Y', 'LF', 'LW')),
'children', :B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- --------------------
Parse 1 0.00 0.00 0 0 0 0
Execute 22252 67.08 67.06 0 908 70203 22252
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- --------------------
total 22253 67.08 67.06 0 908 70203 22252
SELECT WDT_CHILD1.SUB_LEVEL
FROM
PJI_FP_AGGR_XBS_T WDT_CHILD1 WHERE 1=1 AND WDT_CHILD1.SUP_ID = :B2 AND
WDT_CHILD1.SUP_ID = WDT_CHILD1.SUB_ID AND WDT_CHILD1.WORKER_ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- --------------------
Parse 1 0.00 0.00 0 0 0 0
Execute 22252 9.28 9.28 0 0 0 0
Fetch 22252 53.34 53.33 0 4481760 0 22252
------- ------ -------- ---------- ---------- ---------- ---------------------
total 44505 62.62 62.61 0 4481760 0 22252
These sql queries are from the Oracle Project Foundation product. Is there any scope to improve the performance of the above sql queries?
Solution
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
| Goal |
| Solution |
| References |