Saving Changes In DCM Questions Slow After rebuild_respt_index (Doc ID 1430053.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Clinical - Version: 4.5.3 and later   [Release: 4.5.3 and later ]
This problem can occur on any platform.

Symptoms

You are modifying some questions in a DCM in the Maintain Library DCMs or Maintain Study DCMs screens in Test mode. After unchecking of 'Collect in Study' and 'Collect in Subset' check boxes for some questions you click the Save button. Prior to recently running the 'upgrade' script rebuild_respt_index.sql this Save process would only take a few seconds. However, since running that script it now take several minutes.

You did some performance analysis to identify the root cause and found the following in a tkprof output:
********************************************************************************


DELETE FROM RXC.DISCREPANCY_ENTRIEST
WHERE
RESPONSE_ID IN
  ( SELECT RESPONSE_ID
   FROM RXC.RESPONSEST RES
   WHERE
   DCM_QUESTION_ID = :B3
   AND CLINICAL_STUDY_ID = :B2
   AND (:B1 != 'INDICATOR' OR RES.REPEAT_SN > 1)
 )


call  count   cpu   elapsed   disk   query current rows
----  ----- ------  ------- ------ ------- ------- ----
Parse     1   0.00     0.00       0       0      0    0
Execute   4 263.21   318.48 1541082 1542562      0    0
Fetch     0   0.00     0.00       0       0      0    0
----  ----- ------  ------- ------ ------- ------- ----
total     5 263.21   318.48 1541082 1542562      0    0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE
0 NESTED LOOPS
0 SORT UNIQUE
0 TABLE ACCESS FULL RESPONSEST
0 INDEX RANGE SCAN DISCREPANCY_ENT_RES_NFK_IDXT (object id 1593639)



DELETE FROM RXC.RESPONSEST
WHERE
DCM_QUESTION_ID = :B3
AND CLINICAL_STUDY_ID = :B2
AND (:B1 != 'INDICATOR' OR REPEAT_SN > 1)


call   count    cpu elapsed    disk   query current rows
----   ----- ------ ------- ------- ------- ------- ----
Parse      1   0.00    0.00       0       2       0    0
Execute    4 230.08  224.76 1541058 1542532       0    0
Fetch      0   0.00    0.00       0       0       0    0
----   ----- ------ ------- ------- ------- ------- ----
total      5 230.08  224.76 1541058 1542534       0    0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE
0 TABLE ACCESS FULL RESPONSEST


ERROR MESSAGES:NA

OCCURRENCE PATTERNS: consistent since running the upgrade script file

Changes

Running rebuild_respt_index.sql

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms