HTB: Bulk Loader Has Poor Performance With CTB_CORE_IFC_CNCP_MAP_GT Global Temp Table
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.
HTB 6.1.1 on RHEL 5.
DB instance is on 220.127.116.11.
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.
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.
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.
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.
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