My Oracle Support Banner

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 later
Information 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



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


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