HTB: Bulk Loader Has Poor Performance With CTB_CORE_IFC_CNCP_MAP_GT Global Temp Table (Doc ID 2093492.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Healthcare Transaction Base - Version 6.1 and later
Information in this document applies to any platform.

Symptoms

HTB 6.1.1 on RHEL 5.
 DB instance is on 11.2.0.3.

ACTUAL BEHAVIOR
---------------
 HTB Bulk Loader has poor performance with CTB_CORE_IFC_CNCP_MAP_GT Global Temp Table (GTT).
For example, the new Lab results take a many as 9 to 12 hours to complete instead of just 1.5 hours.
Appears to be much worse in Production. Do not know if it is configuration or statistics related or the fact that there is MUCH more data in production.

Details
----------
Loading a new Lab datatype where every single record includes 3 or more participations to roles using both the participation functionCode and the role interpretationCode elements.
During bulk loading the validation procedures that join with the CTB_CORE_IFC_CNCP_MAP_GT global temp table drastically slow down.

Need direction what should be done to support the use of this global temp table. Most other datatypes slow down on these steps to update the following tables, but given the number of elements in each, they complete.

CTB_CORE_ACT_CD_IFC
CTB_CORE_ACT_REL_CD_IFC
CTB_CORE_ENTY_CD_IFC
CTB_CORE_ENT_LNG_CD_IFC
CTB_CORE_PRTCPTN_CD_IFC
CTB_CORE_ROLE_CD_IFC


Checked the user_tab_statistics and user_tab_col_statistics and there are no statistics on this GTT. Need to know if there is something that can be done to improve the bulk loader PL/SQL performance with this GTT.

EXPECTED BEHAVIOR
-----------------------
Expecting the new Lab results should take less hours to complete. For example, the new Lab results take a many as 9 to 12 hours to complete instead of just 1.5 hours.

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