My Oracle Support Banner

SQL Script Hdi_seed_data.sql Is Running For A Long Time During OHF 7.3 Upgrade (Doc ID 2714470.1)

Last updated on SEPTEMBER 30, 2020

Applies to:

Oracle Healthcare Foundation - Version 7.3 and later
Information in this document applies to any platform.

Symptoms

Oracle Healthcare Foundation - Version 7.1.1 and later


ACTUAL BEHAVIOR
============
When upgrading Oracle Healthcare Foundation (OHF) from version 7.1.1 to 7.3, it was found that "Hdi_seed_data.sql" keeps running for a very long time:

Note: When this performance issue occurred, HDI.HDI_CD_REPOSITORY_CD_TYP table had 8.8 million entries.

/* Formatted on 9/23/2020 9:48:25 AM (QP5 v5.360) */
INSERT INTO HDI_X_SEED_DATA_TEMP
  SELECT DISTINCT
  HDI_X_SEED_DATA.CD
  || '~'
  || HDI_X_SEED_DATA.SYS_CD
  || '~'
  || HDI_X_SEED_DATA.SYS_VERSION
  || '~'
  || HDI_X_SEED_DATA.TYP_CD,
  1,
  SYSDATE,
  'HDI_CD_REPOSITORY_CD_TYP',
  UPPER ( :B1),
  'insert into HDI_CD_REPOSITORY_CD_TYP
  (id,int_id,dat_src_num_id,src_eff_from_dt,src_eff_to_dt,cd_int_id, cd_dat_src_num_id,
  cd_src_changed_on_dt, typ_int_id, typ_dat_src_num_id,created_by_user_id,created_by_user_ds_num_id, changed_by_user_id,
  changed_by_user_ds_num_id,src_created_on_dt, src_changed_on_dt,insert_dt,enterprise_id,delete_flg,request_id) -- -AO bug 23135148
  values(:id,'''
  || HDI_X_SEED_DATA.CD
  || '~'
  || HDI_X_SEED_DATA.SYS_CD
  || '~'
  || HDI_X_SEED_DATA.SYS_VERSION
  || '~'
  || HDI_X_SEED_DATA.TYP_CD
  || ''',1, sysdate, to_date(''12/31/9999'',''mm/dd/yyyy''),'''
  || A.INT_ID
  || ''','
  || A.DAT_SRC_NUM_ID
  || ',to_date('''
  || TO_CHAR (A.SRC_CHANGED_ON_DT, 'mm/dd/yyyy')
  || ''', ''mm/dd/yyyy''),'''
  || HDI_CD_TYP.INT_ID
  || ''','
  || HDI_CD_TYP.DAT_SRC_NUM_ID
  || ', ''EHA_USER'',1, ''EHA_USER'', 1, sysdate, sysdate, s

EXPECTED BEHAVIOR
=============

Expect Hdi_seed_data.sql  to complete in few minutes or less. 

STEPS
============
The issue can be reproduced at will with the following steps:
1. Start OHF 7.3 upgrade,
2. Encounter long running SQL from Hdi_seed_data.sql 

Changes

 

Cause

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
Symptoms
Changes
Cause
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.