Home » SQL & PL/SQL » SQL & PL/SQL » Insert Statement taking more than 2 hrs for 4 million records (Oracle 11gR2)
Insert Statement taking more than 2 hrs for 4 million records [message #673926] Tue, 18 December 2018 04:38 Go to next message
srinivas.k2005
Messages: 394
Registered: August 2006
Senior Member
Hi,

I have an insert statement and it takes 2 hrs to get inserted, I have attached explain plan and few statistics from TOAD. In Toad it shows that Sort Output is taking more time (Attached the sort output and the big query in word doc attached), but I cannot see any sort operation in the query. Can you please help me with this performance issue.

Thanks,
SRK
Re: Insert Statement taking more than 2 hrs for 4 million records [message #673927 is a reply to message #673926] Tue, 18 December 2018 04:48 Go to previous messageGo to next message
John Watson
Messages: 7811
Registered: January 2010
Location: Global Village
Senior Member
You know as well as I do that what you have posted is totally unreadable. You have been told how to use [code] tags before. If you want help, act your age, and follow the forum rules.
Re: Insert Statement taking more than 2 hrs for 4 million records [message #673934 is a reply to message #673926] Tue, 18 December 2018 06:31 Go to previous messageGo to next message
EdStevens
Messages: 1047
Registered: September 2013
Senior Member
Many sites block attachments. Many people at sites that do not block them refuse to open them for the same reason others block them.
Re: Insert Statement taking more than 2 hrs for 4 million records [message #673949 is a reply to message #673927] Wed, 19 December 2018 03:39 Go to previous messageGo to next message
srinivas.k2005
Messages: 394
Registered: August 2006
Senior Member
Sorry, formatted it

I have an insert statement and it takes 2 hrs to get inserted, I have explain plan and few statistics from TOAD. In Toad it shows that Sort Output is taking more time, but I cannot see any sort operation in the insert statement. Can you please help me with this performance issue.

Attached the picture which shows me Sort output operation is taking more time

Below is the insert statement, Please let me know if you need more details


INSERT /*+ append parallel(6) */
      INTO  RDS_CLIENT_FX_MARGIN (CLIENT_PROGRAM_PKG_GRP_ID,
                                  REF_DATA_CYCLE_DAY_OF_WEEK,
                                  REFERENCE_DATA_CYCLE_DATE,
                                  CP_ORG_ID,
                                  CP_ORG_REG_OFFICE_ID,
                                  CP_ORG_BRANCH_OFFICE_ID,
                                  CP_PPG_ID,
                                  BASE_ISO_CURREANCY_CODE_3,
                                  QUOTE_ISO_CURREANCY_CODE_3,
                                  ISO_COUNTRY_CODE_2,
                                  CHANNEL_CODE,
                                  TRANSACTION_TYPE,
                                  LOW_TIER,
                                  HIGH_TIER,
                                  FX_CLIENT_MARGIN_PCT,
                                  FX_RATE_SERVER_FREQ_ID)
   SELECT CPPG.CLIENT_PROGRAM_PKG_GRP_ID,
          :B4,
          :B1,
          CPPG.CP_ORG_ID,
          CPPG.CP_ORG_REG_OFFICE_ID,
          CPPG.CP_ORG_BRANCH_OFFICE_ID,
          PPG.CP_PPG_ID,
          DECODE (PPG.MCP_FLAG,
                  1, PPG.ISO_CARD_CURRENCY_CODE,
                  RCO_1.ISO_CURRENCY_CODE),
          RCO_2.ISO_CURRENCY_CODE,
          PPG.ISO_BASE_COUNTRY_CODE,
          FCM.CHANNEL_CODE,
          FCM.TRANSACTION_TYPE,
          FCM.LOW_TIER,
          FCM.HIGH_TIER,
          FCM.FX_CLIENT_MARGIN_PCT,
          DECODE (FCM.TRANSACTION_TYPE,
                  1, CPPG.FX_RATE_SERVER_FREQ_LOAD,
                  2, CPPG.FX_RATE_SERVER_FREQ_RELOAD,
                  3, CPPG.FX_RATE_SERVER_FREQ_UNLOAD,
                  4, CPPG.FX_RATE_SERVER_FREQ_CASHOUT,
                  5, CPPG.FX_RATE_SERVER_FREQ_PURSETRANS,
                  CPPG.FX_RATE_SERVER_FREQ_LOAD)
     FROM RDS_CLIENT_PROGRAM_PKG_GRP CPPG,
          RDS_PROGRAM_PACKAGE_GROUP PPG,
          CONTRACT CON,
          CONTRACT_PPG_MAP CPM,
          FX_CLIENT_MARGIN FCM,
          RDS_COUNTRY RCU,
          RDS_CURRENCY RCO_1,
          RDS_CURRENCY RCO_2
    WHERE     CPPG.REFERENCE_DATA_CYCLE_DATE = :B1
          AND CPPG.REF_DATA_CYCLE_DAY_OF_WEEK = :B4
          AND PPG.REFERENCE_DATA_CYCLE_DATE = :B1
          AND PPG.REF_DATA_CYCLE_DAY_OF_WEEK = :B4
         AND RCU.REFERENCE_DATA_CYCLE_DATE = :B1
          AND RCU.REF_DATA_CYCLE_DAY_OF_WEEK = :B4
          AND RCO_1.REFERENCE_DATA_CYCLE_DATE = :B1
          AND RCO_1.REF_DATA_CYCLE_DAY_OF_WEEK = :B4
          AND RCO_2.REFERENCE_DATA_CYCLE_DATE = :B1
          AND RCO_2.REF_DATA_CYCLE_DAY_OF_WEEK = :B4
          AND CPPG.PROGRAM_PACKAGE_GROUP_ID = PPG.PROGRAM_PACKAGE_GROUP_ID
          AND (   (    CON.CONTRACT_LEVEL = 1
                   AND CON.ASSOC_ORG_HIERARCHY_ID =
                          CPPG.CLIENT_ORGANISATION_ID)
               OR (    CON.CONTRACT_LEVEL = 2
                   AND CON.ASSOC_ORG_HIERARCHY_ID =
                          CPPG.CLIENT_REGIONAL_OFFICE_ID))
          AND CON.CONTRACT_ID = FCM.CONTRACT_ID
          AND CON.PRODUCT_TYPE_ID = PPG.PRODUCT_TYPE_ID
          AND RCU.ISO_COUNTRY_CODE_2 = PPG.ISO_BASE_COUNTRY_CODE
          AND RCU.CURRENCY_ID = RCO_1.CURRENCY_ID
          AND FCM.QUOTE_CURRENCY_ID = RCO_2.CURRENCY_ID
          AND CON.ACTIVE_FLAG = :B3
          AND CON.LIVE_DATE <= :B1
          AND CON.WORKFLOW_STATUS = :B2
          AND CON.EFFECTIVE_START_DATE <= :B1
          AND CON.EFFECTIVE_END_DATE > :B1
          AND FCM.ACTIVE_FLAG = :B3
          AND FCM.LIVE_DATE <= :B1
          AND FCM.WORKFLOW_STATUS = :B2
          AND FCM.EFFECTIVE_START_DATE <= :B1
          AND FCM.EFFECTIVE_END_DATE > :B1
          AND CPM.ACTIVE_FLAG = :B3
          AND CPM.LIVE_DATE <= :B1
          AND CPM.WORKFLOW_STATUS = :B2
          AND CPM.EFFECTIVE_START_DATE <= :B1
          AND CPM.EFFECTIVE_END_DATE > :B1
          AND CON.CONTRACT_ID = CPM.CONTRACT_ID
          AND CPM.PROGRAM_PKG_GRP_ID = PPG.PROGRAM_PACKAGE_GROUP_ID
          AND NOT EXISTS
                     (SELECT 1
                        FROM FX_PROMOTION_CPPM_MAP PCM,
                             RDS_CLIENT_PROGRAM_PKG_GRP CPPG_1,
                             FX_CLIENT_PROMOTION FCP,
                             FX_CLIENT_PROMOTION_DETAIL CPD
                       WHERE     CPPG_1.REFERENCE_DATA_CYCLE_DATE = :B1
                             AND CPPG_1.REF_DATA_CYCLE_DAY_OF_WEEK = :B4
                             AND PCM.CLIENT_PROGRAM_PKG_GRP_ID =
                                    CPPG_1.CLIENT_PROGRAM_PKG_GRP_ID
                             AND PCM.ACTIVE_FLAG = :B3
                             AND PCM.LIVE_DATE <= :B1
                             AND PCM.WORKFLOW_STATUS = :B2
                             AND PCM.EFFECTIVE_START_DATE <= :B1
                             AND PCM.EFFECTIVE_END_DATE > :B1
                             AND PCM.FX_CLIENT_PROMOTION_ID =
                                    FCP.FX_CLIENT_PROMOTION_ID
                             AND FCP.ACTIVE_FLAG = :B3
                             AND FCP.WORKFLOW_STATUS = :B2
                             AND FCP.EFFECTIVE_START_DATE <= :B1
                             AND FCP.EFFECTIVE_END_DATE > :B1
                             AND FCP.FX_CLIENT_PROMOTION_ID =
                                    CPD.FX_CLIENT_PROMOTION_ID
                             AND CPPG.CLIENT_PROGRAM_PKG_GRP_ID =
                                    CPPG_1.CLIENT_PROGRAM_PKG_GRP_ID
                             AND FCM.QUOTE_CURRENCY_ID =
                                    FCP.QUOTE_CURRENCY_ID
                             AND FCM.CHANNEL_CODE = CPD.CHANNEL_CODE
                             AND FCM.TRANSACTION_TYPE = CPD.TRANSACTION_TYPE
                             AND FCM.LOW_TIER = CPD.LOW_TIER
                             AND FCM.HIGH_TIER = CPD.HIGH_TIER)

Explain Plan of the insert statement

<ExplainPlan>
  <PlanElement id="0" operation="INSERT STATEMENT" optimizer="ALL_ROWS" search_columns="0" cost="192">
    <PlanElements>
      <PlanElement id="1" operation="LOAD AS SELECT" search_columns="0" qblock_name="SEL$C772B8D1">
        <PlanElements>
          <PlanElement id="2" operation="PX COORDINATOR" search_columns="0">
            <PlanElements>
              <PlanElement object_ID="0" id="3" operation="PX SEND" option="QC (RANDOM)" object_node=":Q1014" object_owner="SYS" object_name=":TQ10014" other_tag="PARALLEL_TO_SERIAL" search_columns="0" cost="192" cardinality="30,632" bytes="11,670,792" distribution="QC (RANDOM)" cpu_cost="56,487,582" io_cost="188" time="2">
                <PlanElements>
                  <PlanElement id="4" operation="HASH JOIN" option="RIGHT ANTI BUFFERED" object_node=":Q1014" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="192" cardinality="30,632" bytes="11,670,792" cpu_cost="56,487,582" io_cost="188" access_predicates="&quot;CPPG&quot;.&quot;CLIENT_PROGRAM_PKG_GRP_ID&quot;=&quot;ITEM_1&quot; AND &quot;FCM&quot;.&quot;QUOTE_CURRENCY_ID&quot;=&quot;ITEM_2&quot; AND &quot;FCM&quot;.&quot;CHANNEL_CODE&quot;=&quot;ITEM_3&quot; AND &quot;FCM&quot;.&quot;TRANSACTION_TYPE&quot;=&quot;ITEM_4&quot; AND &quot;FCM&quot;.&quot;LOW_TIER&quot;=&quot;ITEM_5&quot; AND &quot;FCM&quot;.&quot;HIGH_TIER&quot;=&quot;ITEM_6&quot;" time="2">
                    <PlanElements>
                      <PlanElement id="5" operation="PX RECEIVE" object_node=":Q1014" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="2" cardinality="1" bytes="78" cpu_cost="2,906" io_cost="2" time="1">
                        <PlanElements>
                          <PlanElement object_ID="1" id="6" operation="PX SEND" option="HASH" object_node=":Q1012" object_owner="SYS" object_name=":TQ10012" other_tag="PARALLEL_TO_PARALLEL" search_columns="0" cost="2" cardinality="1" bytes="78" distribution="HASH" cpu_cost="2,906" io_cost="2" time="1">
                            <PlanElements>
                              <PlanElement id="7" operation="VIEW" object_node=":Q1012" object_name="VW_SQ_1" object_type="VIEW" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="2" cardinality="1" bytes="78" cpu_cost="2,906" io_cost="2" qblock_name="SEL$683B0107" time="1">
                                <PlanElements>
                                  <PlanElement id="8" operation="NESTED LOOPS" object_node=":Q1012" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="2" cardinality="1" bytes="231" cpu_cost="2,906" io_cost="2" qblock_name="SEL$683B0107" time="1">
                                    <PlanElements>
                                      <PlanElement id="9" operation="NESTED LOOPS" object_node=":Q1012" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="2" cardinality="1" bytes="231" cpu_cost="2,906" io_cost="2" time="1">
                                        <PlanElements>
                                          <PlanElement id="10" operation="NESTED LOOPS" object_node=":Q1012" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="2" cardinality="1" bytes="214" cpu_cost="74" io_cost="2" time="1">
                                            <PlanElements>
                                              <PlanElement id="11" operation="NESTED LOOPS" object_node=":Q1012" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="2" cardinality="1" bytes="135" cpu_cost="37" io_cost="2" time="1">
                                                <PlanElements>
                                                  <PlanElement id="12" operation="PX BLOCK" option="ITERATOR" object_node=":Q1012" other_tag="PARALLEL_COMBINED_WITH_CHILD" search_columns="0">
                                                    <PlanElements>
                                                      <PlanElement object_ID="2" id="13" operation="TABLE ACCESS" option="FULL" object_node=":Q1012" object_owner="REF_OWNER" object_name="FX_CLIENT_PROMOTION_DETAIL" object_type="TABLE" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="2" cardinality="1" bytes="65" cpu_cost="0" io_cost="2" qblock_name="SEL$683B0107" access_predicates=":Z&gt;=:Z AND :Z&lt;=:Z" time="1"/>
                                                    </PlanElements>
                                                  </PlanElement>
                                                  <PlanElement object_ID="3" id="14" operation="TABLE ACCESS" option="BY INDEX ROWID" object_node=":Q1012" object_owner="REF_OWNER" object_name="FX_CLIENT_PROMOTION" object_type="TABLE" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="0" cardinality="1" bytes="70" cpu_cost="37" io_cost="0" qblock_name="SEL$683B0107" filter_predicates="(&quot;FCP&quot;.&quot;ACTIVE_FLAG&quot;=:B3 AND &quot;FCP&quot;.&quot;WORKFLOW_STATUS&quot;=:B2 AND &quot;FCP&quot;.&quot;EFFECTIVE_START_DATE&quot;&lt;=:B1 AND &quot;FCP&quot;.&quot;EFFECTIVE_END_DATE&quot;&gt;:B1)">
                                                    <PlanElements>
                                                      <PlanElement object_ID="4" id="15" operation="INDEX" option="RANGE SCAN" object_node=":Q1012" object_owner="REF_OWNER" object_name="FCP_PK" object_type="INDEX (UNIQUE)" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="1" cost="0" cardinality="1" cpu_cost="37" io_cost="0" qblock_name="SEL$683B0107" access_predicates="&quot;FCP&quot;.&quot;FX_CLIENT_PROMOTION_ID&quot;=&quot;CPD&quot;.&quot;FX_CLIENT_PROMOTION_ID&quot;"/>
                                                    </PlanElements>
                                                  </PlanElement>
                                                </PlanElements>
                                              </PlanElement>
                                              <PlanElement object_ID="5" id="16" operation="TABLE ACCESS" option="BY INDEX ROWID" object_node=":Q1012" object_owner="REF_OWNER" object_name="FX_PROMOTION_CPPM_MAP" object_type="TABLE" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="0" cardinality="1" bytes="79" cpu_cost="37" io_cost="0" qblock_name="SEL$683B0107" filter_predicates="(&quot;PCM&quot;.&quot;ACTIVE_FLAG&quot;=:B3 AND &quot;PCM&quot;.&quot;WORKFLOW_STATUS&quot;=:B2 AND &quot;PCM&quot;.&quot;LIVE_DATE&quot;&lt;=:B1 AND &quot;PCM&quot;.&quot;EFFECTIVE_START_DATE&quot;&lt;=:B1 AND &quot;PCM&quot;.&quot;EFFECTIVE_END_DATE&quot;&gt;:B1)">
                                                <PlanElements>
                                                  <PlanElement object_ID="6" id="17" operation="INDEX" option="RANGE SCAN" object_node=":Q1012" object_owner="REF_OWNER" object_name="FPC_UK_PRO_CLI_GRP_ID" object_type="INDEX (UNIQUE)" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="1" cost="0" cardinality="1" cpu_cost="37" io_cost="0" qblock_name="SEL$683B0107" access_predicates="&quot;PCM&quot;.&quot;FX_CLIENT_PROMOTION_ID&quot;=&quot;FCP&quot;.&quot;FX_CLIENT_PROMOTION_ID&quot;"/>
                                                </PlanElements>
                                              </PlanElement>
                                            </PlanElements>
                                          </PlanElement>
                                          <PlanElement id="18" operation="PARTITION LIST" option="SINGLE" object_node=":Q1012" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="0" cardinality="1" partition_id="18" partition_start="KEY" partition_stop="KEY" cpu_cost="2,832" io_cost="0">
                                            <PlanElements>
                                              <PlanElement object_ID="7" id="19" operation="INDEX" option="RANGE SCAN" object_node=":Q1012" object_owner="RDD_OWNER" object_name="I_CPP_IDX_1" object_type="INDEX" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="2" cost="0" cardinality="1" partition_id="18" partition_start="KEY" partition_stop="KEY" cpu_cost="2,832" io_cost="0" qblock_name="SEL$683B0107" access_predicates="&quot;CPPG_1&quot;.&quot;REFERENCE_DATA_CYCLE_DATE&quot;=:B1 AND &quot;PCM&quot;.&quot;CLIENT_PROGRAM_PKG_GRP_ID&quot;=&quot;CPPG_1&quot;.&quot;CLIENT_PROGRAM_PKG_GRP_ID&quot;"/>
                                            </PlanElements>
                                          </PlanElement>
                                        </PlanElements>
                                      </PlanElement>
                                      <PlanElement object_ID="8" id="20" operation="TABLE ACCESS" option="BY LOCAL INDEX ROWID" object_node=":Q1012" object_owner="RDD_OWNER" object_name="RDS_CLIENT_PROGRAM_PKG_GRP" object_type="TABLE" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="0" cardinality="1" bytes="17" partition_id="18" partition_start="1" partition_stop="1" cpu_cost="2,832" io_cost="0" qblock_name="SEL$683B0107"/>
                                    </PlanElements>
                                  </PlanElement>
                                </PlanElements>
                              </PlanElement>
                            </PlanElements>
                          </PlanElement>
                        </PlanElements>
                      </PlanElement>
                      <PlanElement id="21" operation="PX RECEIVE" object_node=":Q1014" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="190" cardinality="30,632" bytes="9,281,496" cpu_cost="55,373,926" io_cost="186" time="2">
                        <PlanElements>
                          <PlanElement object_ID="9" id="22" operation="PX SEND" option="HASH" object_node=":Q1013" object_owner="SYS" object_name=":TQ10013" other_tag="PARALLEL_TO_PARALLEL" search_columns="0" cost="190" cardinality="30,632" bytes="9,281,496" distribution="HASH" cpu_cost="55,373,926" io_cost="186" time="2">
                            <PlanElements>
                              <PlanElement id="23" operation="HASH JOIN" object_node=":Q1013" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="190" cardinality="30,632" bytes="9,281,496" cpu_cost="55,373,926" io_cost="186" access_predicates="&quot;FCM&quot;.&quot;QUOTE_CURRENCY_ID&quot;=&quot;RCO_2&quot;.&quot;CURRENCY_ID&quot;" time="2">
                                <PlanElements>
                                  <PlanElement id="24" operation="PX RECEIVE" object_node=":Q1013" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="2" cardinality="183" bytes="3,660" cpu_cost="9,494" io_cost="2" time="1">
                                    <PlanElements>
                                      <PlanElement object_ID="10" id="25" operation="PX SEND" option="BROADCAST" object_node=":Q1010" object_owner="SYS" object_name=":TQ10010" other_tag="PARALLEL_TO_PARALLEL" search_columns="0" cost="2" cardinality="183" bytes="3,660" distribution="BROADCAST" cpu_cost="9,494" io_cost="2" time="1">
                                        <PlanElements>
                                          <PlanElement id="26" operation="PX BLOCK" option="ITERATOR" object_node=":Q1010" other_tag="PARALLEL_COMBINED_WITH_CHILD" search_columns="0" cost="2" cardinality="183" bytes="3,660" partition_id="26" partition_start="KEY" partition_stop="KEY" cpu_cost="9,494" io_cost="2" time="1">
                                            <PlanElements>
                                              <PlanElement object_ID="11" id="27" operation="TABLE ACCESS" option="FULL" object_node=":Q1010" object_owner="RDD_OWNER" object_name="RDS_CURRENCY" object_type="TABLE" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="2" cardinality="183" bytes="3,660" partition_id="26" partition_start="KEY" partition_stop="KEY" cpu_cost="9,494" io_cost="2" qblock_name="SEL$C772B8D1" access_predicates=":Z&gt;=:Z AND :Z&lt;=:Z" filter_predicates="&quot;RCO_2&quot;.&quot;REFERENCE_DATA_CYCLE_DATE&quot;=:B1" time="1"/>
                                            </PlanElements>
                                          </PlanElement>
                                        </PlanElements>
                                      </PlanElement>
                                    </PlanElements>
                                  </PlanElement>
                                  <PlanElement id="28" operation="HASH JOIN" object_node=":Q1013" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="187" cardinality="30,632" bytes="8,668,856" cpu_cost="54,249,182" io_cost="184" access_predicates="&quot;CON&quot;.&quot;CONTRACT_ID&quot;=&quot;FCM&quot;.&quot;CONTRACT_ID&quot;" time="2">
                                    <PlanElements>
                                      <PlanElement id="29" operation="PX RECEIVE" object_node=":Q1013" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="16" cardinality="738" bytes="166,788" cpu_cost="8,635,881" io_cost="15" time="1">
                                        <PlanElements>
                                          <PlanElement object_ID="12" id="30" operation="PX SEND" option="BROADCAST" object_node=":Q1011" object_owner="SYS" object_name=":TQ10011" other_tag="PARALLEL_TO_PARALLEL" search_columns="0" cost="16" cardinality="738" bytes="166,788" distribution="BROADCAST" cpu_cost="8,635,881" io_cost="15" time="1">
                                            <PlanElements>
                                              <PlanElement id="31" operation="HASH JOIN" object_node=":Q1011" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="16" cardinality="738" bytes="166,788" cpu_cost="8,635,881" io_cost="15" access_predicates="&quot;CPPG&quot;.&quot;PROGRAM_PACKAGE_GROUP_ID&quot;=&quot;PPG&quot;.&quot;PROGRAM_PACKAGE_GROUP_ID&quot;" filter_predicates="((&quot;CON&quot;.&quot;CONTRACT_LEVEL&quot;=1 AND &quot;CON&quot;.&quot;ASSOC_ORG_HIERARCHY_ID&quot;=&quot;CPPG&quot;.&quot;CLIENT_ORGANISATION_ID&quot;) OR (&quot;CON&quot;.&quot;CONTRACT_LEVEL&quot;=2 AND &quot;CON&quot;.&quot;ASSOC_ORG_HIERARCHY_ID&quot;=&quot;CPPG&quot;.&quot;CLIENT_REGIONAL_OFFICE_ID&quot;))" time="1">
                                                <PlanElements>
                                                  <PlanElement id="32" operation="PX RECEIVE" object_node=":Q1011" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="12" cardinality="805" bytes="129,605" cpu_cost="3,801,826" io_cost="11" time="1">
                                                    <PlanElements>
                                                      <PlanElement object_ID="13" id="33" operation="PX SEND" option="HASH" object_node=":Q1009" object_owner="SYS" object_name=":TQ10009" other_tag="PARALLEL_TO_PARALLEL" search_columns="0" cost="12" cardinality="805" bytes="129,605" distribution="HASH" cpu_cost="3,801,826" io_cost="11" time="1">
                                                        <PlanElements>
                                                          <PlanElement id="34" operation="HASH JOIN" option="BUFFERED" object_node=":Q1009" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="12" cardinality="805" bytes="129,605" cpu_cost="3,801,826" io_cost="11" access_predicates="&quot;CON&quot;.&quot;PRODUCT_TYPE_ID&quot;=&quot;PPG&quot;.&quot;PRODUCT_TYPE_ID&quot; AND &quot;CON&quot;.&quot;CONTRACT_ID&quot;=&quot;CPM&quot;.&quot;CONTRACT_ID&quot;" time="1">
                                                            <PlanElements>
                                                              <PlanElement id="35" operation="PX RECEIVE" object_node=":Q1009" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="2" cardinality="1,118" bytes="51,428" cpu_cost="364,675" io_cost="2" time="1">
                                                                <PlanElements>
                                                                  <PlanElement object_ID="14" id="36" operation="PX SEND" option="HASH" object_node=":Q1007" object_owner="SYS" object_name=":TQ10007" other_tag="PARALLEL_TO_PARALLEL" search_columns="0" cost="2" cardinality="1,118" bytes="51,428" distribution="HASH" cpu_cost="364,675" io_cost="2" time="1">
                                                                    <PlanElements>
                                                                      <PlanElement id="37" operation="PX BLOCK" option="ITERATOR" object_node=":Q1007" other_tag="PARALLEL_COMBINED_WITH_CHILD" search_columns="0" cost="2" cardinality="1,118" bytes="51,428" cpu_cost="364,675" io_cost="2" time="1">
                                                                        <PlanElements>
                                                                          <PlanElement object_ID="15" id="38" operation="TABLE ACCESS" option="FULL" object_node=":Q1007" object_owner="REF_OWNER" object_name="CONTRACT" object_type="TABLE" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="2" cardinality="1,118" bytes="51,428" cpu_cost="364,675" io_cost="2" qblock_name="SEL$C772B8D1" access_predicates=":Z&gt;=:Z AND :Z&lt;=:Z" filter_predicates="(&quot;CON&quot;.&quot;EFFECTIVE_END_DATE&quot;&gt;:B1 AND &quot;CON&quot;.&quot;WORKFLOW_STATUS&quot;=:B2 AND &quot;CON&quot;.&quot;LIVE_DATE&quot;&lt;=:B1 AND &quot;CON&quot;.&quot;ACTIVE_FLAG&quot;=:B3 AND &quot;CON&quot;.&quot;EFFECTIVE_START_DATE&quot;&lt;=:B1)" time="1"/>
                                                                        </PlanElements>
                                                                      </PlanElement>
                                                                    </PlanElements>
                                                                  </PlanElement>
                                                                </PlanElements>
                                                              </PlanElement>
                                                              <PlanElement id="39" operation="PX RECEIVE" object_node=":Q1009" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="10" cardinality="1,422" bytes="163,530" cpu_cost="2,785,401" io_cost="9" time="1">
                                                                <PlanElements>
                                                                  <PlanElement object_ID="16" id="40" operation="PX SEND" option="HASH" object_node=":Q1008" object_owner="SYS" object_name=":TQ10008" other_tag="PARALLEL_TO_PARALLEL" search_columns="0" cost="10" cardinality="1,422" bytes="163,530" distribution="HASH" cpu_cost="2,785,401" io_cost="9" time="1">
                                                                    <PlanElements>
                                                                      <PlanElement id="41" operation="HASH JOIN" option="BUFFERED" object_node=":Q1008" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="10" cardinality="1,422" bytes="163,530" cpu_cost="2,785,401" io_cost="9" access_predicates="&quot;CPM&quot;.&quot;PROGRAM_PKG_GRP_ID&quot;=&quot;PPG&quot;.&quot;PROGRAM_PACKAGE_GROUP_ID&quot;" time="1">
                                                                        <PlanElements>
                                                                          <PlanElement id="42" operation="PX RECEIVE" object_node=":Q1008" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="6" cardinality="514" bytes="39,578" cpu_cost="1,309,318" io_cost="6" time="1">
                                                                            <PlanElements>
                                                                              <PlanElement object_ID="17" id="43" operation="PX SEND" option="HASH" object_node=":Q1005" object_owner="SYS" object_name=":TQ10005" other_tag="PARALLEL_TO_PARALLEL" search_columns="0" cost="6" cardinality="514" bytes="39,578" distribution="HASH" cpu_cost="1,309,318" io_cost="6" time="1">
                                                                                <PlanElements>
                                                                                  <PlanElement id="44" operation="HASH JOIN" option="BUFFERED" object_node=":Q1005" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="6" cardinality="514" bytes="39,578" cpu_cost="1,309,318" io_cost="6" access_predicates="&quot;RCU&quot;.&quot;ISO_COUNTRY_CODE_2&quot;=&quot;PPG&quot;.&quot;ISO_BASE_COUNTRY_CODE&quot;" time="1">
                                                                                    <PlanElements>
                                                                                      <PlanElement id="45" operation="PX RECEIVE" object_node=":Q1005" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="4" cardinality="250" bytes="9,750" cpu_cost="637,731" io_cost="4" time="1">
                                                                                        <PlanElements>
                                                                                          <PlanElement object_ID="18" id="46" operation="PX SEND" option="HASH" object_node=":Q1003" object_owner="SYS" object_name=":TQ10003" other_tag="PARALLEL_TO_PARALLEL" search_columns="0" cost="4" cardinality="250" bytes="9,750" distribution="HASH" cpu_cost="637,731" io_cost="4" time="1">
                                                                                            <PlanElements>
                                                                                              <PlanElement id="47" operation="HASH JOIN" option="BUFFERED" object_node=":Q1003" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="4" cardinality="250" bytes="9,750" cpu_cost="637,731" io_cost="4" access_predicates="&quot;RCU&quot;.&quot;CURRENCY_ID&quot;=&quot;RCO_1&quot;.&quot;CURRENCY_ID&quot;" time="1">
                                                                                                <PlanElements>
                                                                                                  <PlanElement id="48" operation="PX RECEIVE" object_node=":Q1003" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="2" cardinality="250" bytes="4,750" cpu_cost="17,597" io_cost="2" time="1">
                                                                                                    <PlanElements>
                                                                                                      <PlanElement object_ID="19" id="49" operation="PX SEND" option="HASH" object_node=":Q1001" object_owner="SYS" object_name=":TQ10001" other_tag="PARALLEL_TO_PARALLEL" search_columns="0" cost="2" cardinality="250" bytes="4,750" distribution="HASH" cpu_cost="17,597" io_cost="2" time="1">
                                                                                                        <PlanElements>
                                                                                                          <PlanElement id="50" operation="PX BLOCK" option="ITERATOR" object_node=":Q1001" other_tag="PARALLEL_COMBINED_WITH_CHILD" search_columns="0" cost="2" cardinality="250" bytes="4,750" partition_id="50" partition_start="KEY" partition_stop="KEY" cpu_cost="17,597" io_cost="2" time="1">
                                                                                                            <PlanElements>
                                                                                                              <PlanElement object_ID="20" id="51" operation="TABLE ACCESS" option="FULL" object_node=":Q1001" object_owner="RDD_OWNER" object_name="RDS_COUNTRY" object_type="TABLE" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="2" cardinality="250" bytes="4,750" partition_id="50" partition_start="KEY" partition_stop="KEY" cpu_cost="17,597" io_cost="2" qblock_name="SEL$C772B8D1" access_predicates=":Z&gt;=:Z AND :Z&lt;=:Z" filter_predicates="&quot;RCU&quot;.&quot;REFERENCE_DATA_CYCLE_DATE&quot;=:B1" time="1"/>
                                                                                                            </PlanElements>
                                                                                                          </PlanElement>
                                                                                                        </PlanElements>
                                                                                                      </PlanElement>
                                                                                                    </PlanElements>
                                                                                                  </PlanElement>
                                                                                                  <PlanElement id="52" operation="PX RECEIVE" object_node=":Q1003" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="2" cardinality="183" bytes="3,660" cpu_cost="9,494" io_cost="2" time="1">
                                                                                                    <PlanElements>
                                                                                                      <PlanElement object_ID="21" id="53" operation="PX SEND" option="HASH" object_node=":Q1002" object_owner="SYS" object_name=":TQ10002" other_tag="PARALLEL_TO_PARALLEL" search_columns="0" cost="2" cardinality="183" bytes="3,660" distribution="HASH" cpu_cost="9,494" io_cost="2" time="1">
                                                                                                        <PlanElements>
                                                                                                          <PlanElement id="54" operation="PX BLOCK" option="ITERATOR" object_node=":Q1002" other_tag="PARALLEL_COMBINED_WITH_CHILD" search_columns="0" cost="2" cardinality="183" bytes="3,660" partition_id="54" partition_start="KEY" partition_stop="KEY" cpu_cost="9,494" io_cost="2" time="1">
                                                                                                            <PlanElements>
                                                                                                              <PlanElement object_ID="11" id="55" operation="TABLE ACCESS" option="FULL" object_node=":Q1002" object_owner="RDD_OWNER" object_name="RDS_CURRENCY" object_type="TABLE" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="2" cardinality="183" bytes="3,660" partition_id="54" partition_start="KEY" partition_stop="KEY" cpu_cost="9,494" io_cost="2" qblock_name="SEL$C772B8D1" access_predicates=":Z&gt;=:Z AND :Z&lt;=:Z" filter_predicates="&quot;RCO_1&quot;.&quot;REFERENCE_DATA_CYCLE_DATE&quot;=:B1" time="1"/>
                                                                                                            </PlanElements>
                                                                                                          </PlanElement>
                                                                                                        </PlanElements>
                                                                                                      </PlanElement>
                                                                                                    </PlanElements>
                                                                                                  </PlanElement>
                                                                                                </PlanElements>
                                                                                              </PlanElement>
                                                                                            </PlanElements>
                                                                                          </PlanElement>
                                                                                        </PlanElements>
                                                                                      </PlanElement>
                                                                                      <PlanElement id="56" operation="PX RECEIVE" object_node=":Q1005" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="2" cardinality="514" bytes="19,532" cpu_cost="56,686" io_cost="2" time="1">
                                                                                        <PlanElements>
                                                                                          <PlanElement object_ID="22" id="57" operation="PX SEND" option="HASH" object_node=":Q1004" object_owner="SYS" object_name=":TQ10004" other_tag="PARALLEL_TO_PARALLEL" search_columns="0" cost="2" cardinality="514" bytes="19,532" distribution="HASH" cpu_cost="56,686" io_cost="2" time="1">
                                                                                            <PlanElements>
                                                                                              <PlanElement id="58" operation="PX BLOCK" option="ITERATOR" object_node=":Q1004" other_tag="PARALLEL_COMBINED_WITH_CHILD" search_columns="0" cost="2" cardinality="514" bytes="19,532" partition_id="58" partition_start="KEY" partition_stop="KEY" cpu_cost="56,686" io_cost="2" time="1">
                                                                                                <PlanElements>
                                                                                                  <PlanElement object_ID="23" id="59" operation="TABLE ACCESS" option="FULL" object_node=":Q1004" object_owner="RDD_OWNER" object_name="RDS_PROGRAM_PACKAGE_GROUP" object_type="TABLE" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="2" cardinality="514" bytes="19,532" partition_id="58" partition_start="KEY" partition_stop="KEY" cpu_cost="56,686" io_cost="2" qblock_name="SEL$C772B8D1" access_predicates=":Z&gt;=:Z AND :Z&lt;=:Z" filter_predicates="&quot;PPG&quot;.&quot;REFERENCE_DATA_CYCLE_DATE&quot;=:B1" time="1"/>
                                                                                                </PlanElements>
                                                                                              </PlanElement>
                                                                                            </PlanElements>
                                                                                          </PlanElement>
                                                                                        </PlanElements>
                                                                                      </PlanElement>
                                                                                    </PlanElements>
                                                                                  </PlanElement>
                                                                                </PlanElements>
                                                                              </PlanElement>
                                                                            </PlanElements>
                                                                          </PlanElement>
                                                                          <PlanElement id="60" operation="PX RECEIVE" object_node=":Q1008" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="3" cardinality="1,954" bytes="74,252" cpu_cost="830,584" io_cost="3" time="1">
                                                                            <PlanElements>
                                                                              <PlanElement object_ID="24" id="61" operation="PX SEND" option="HASH" object_node=":Q1006" object_owner="SYS" object_name=":TQ10006" other_tag="PARALLEL_TO_PARALLEL" search_columns="0" cost="3" cardinality="1,954" bytes="74,252" distribution="HASH" cpu_cost="830,584" io_cost="3" time="1">
                                                                                <PlanElements>
                                                                                  <PlanElement id="62" operation="PX BLOCK" option="ITERATOR" object_node=":Q1006" other_tag="PARALLEL_COMBINED_WITH_CHILD" search_columns="0" cost="3" cardinality="1,954" bytes="74,252" cpu_cost="830,584" io_cost="3" time="1">
                                                                                    <PlanElements>
                                                                                      <PlanElement object_ID="25" id="63" operation="TABLE ACCESS" option="FULL" object_node=":Q1006" object_owner="REF_OWNER" object_name="CONTRACT_PPG_MAP" object_type="TABLE" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="3" cardinality="1,954" bytes="74,252" cpu_cost="830,584" io_cost="3" qblock_name="SEL$C772B8D1" access_predicates=":Z&gt;=:Z AND :Z&lt;=:Z" filter_predicates="(&quot;CPM&quot;.&quot;EFFECTIVE_END_DATE&quot;&gt;:B1 AND &quot;CPM&quot;.&quot;WORKFLOW_STATUS&quot;=:B2 AND &quot;CPM&quot;.&quot;ACTIVE_FLAG&quot;=:B3 AND &quot;CPM&quot;.&quot;LIVE_DATE&quot;&lt;=:B1 AND &quot;CPM&quot;.&quot;EFFECTIVE_START_DATE&quot;&lt;=:B1)" time="1"/>
                                                                                    </PlanElements>
                                                                                  </PlanElement>
                                                                                </PlanElements>
                                                                              </PlanElement>
                                                                            </PlanElements>
                                                                          </PlanElement>
                                                                        </PlanElements>
                                                                      </PlanElement>
                                                                    </PlanElements>
                                                                  </PlanElement>
                                                                </PlanElements>
                                                              </PlanElement>
                                                            </PlanElements>
                                                          </PlanElement>
                                                        </PlanElements>
                                                      </PlanElement>
                                                    </PlanElements>
                                                  </PlanElement>
                                                  <PlanElement id="64" operation="BUFFER" option="SORT" object_node=":Q1011" other_tag="PARALLEL_COMBINED_WITH_CHILD" search_columns="0">
                                                    <PlanElements>
                                                      <PlanElement id="65" operation="PX RECEIVE" object_node=":Q1011" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="4" cardinality="235,050" bytes="15,278,250" cpu_cost="26,620" io_cost="4" time="1">
                                                        <PlanElements>
                                                          <PlanElement object_ID="26" id="66" operation="PX SEND" option="HASH" object_owner="SYS" object_name=":TQ10000" other_tag="PARALLEL_FROM_SERIAL" search_columns="0" cost="4" cardinality="235,050" bytes="15,278,250" distribution="HASH" cpu_cost="26,620" io_cost="4" time="1">
                                                            <PlanElements>
                                                              <PlanElement id="67" operation="PARTITION LIST" option="SINGLE" search_columns="0" cost="4" cardinality="235,050" bytes="15,278,250" partition_id="67" partition_start="KEY" partition_stop="KEY" cpu_cost="26,620" io_cost="4" time="1">
                                                                <PlanElements>
                                                                  <PlanElement object_ID="8" id="68" operation="TABLE ACCESS" option="BY LOCAL INDEX ROWID" object_owner="RDD_OWNER" object_name="RDS_CLIENT_PROGRAM_PKG_GRP" object_type="TABLE" search_columns="0" cost="4" cardinality="235,050" bytes="15,278,250" partition_id="67" partition_start="KEY" partition_stop="KEY" cpu_cost="26,620" io_cost="4" qblock_name="SEL$C772B8D1" time="1">
                                                                    <PlanElements>
                                                                      <PlanElement object_ID="7" id="69" operation="INDEX" option="RANGE SCAN" object_owner="RDD_OWNER" object_name="I_CPP_IDX_1" object_type="INDEX" search_columns="1" cost="3" cardinality="1" partition_id="67" partition_start="KEY" partition_stop="KEY" cpu_cost="21,564" io_cost="3" qblock_name="SEL$C772B8D1" access_predicates="&quot;CPPG&quot;.&quot;REFERENCE_DATA_CYCLE_DATE&quot;=:B1" time="1"/>
                                                                    </PlanElements>
                                                                  </PlanElement>
                                                                </PlanElements>
                                                              </PlanElement>
                                                            </PlanElements>
                                                          </PlanElement>
                                                        </PlanElements>
                                                      </PlanElement>
                                                    </PlanElements>
                                                  </PlanElement>
                                                </PlanElements>
                                              </PlanElement>
                                            </PlanElements>
                                          </PlanElement>
                                        </PlanElements>
                                      </PlanElement>
                                      <PlanElement id="70" operation="PX BLOCK" option="ITERATOR" object_node=":Q1013" other_tag="PARALLEL_COMBINED_WITH_CHILD" search_columns="0" cost="171" cardinality="46,810" bytes="2,668,170" cpu_cost="44,214,651" io_cost="169" time="1">
                                        <PlanElements>
                                          <PlanElement object_ID="27" id="71" operation="TABLE ACCESS" option="FULL" object_node=":Q1013" object_owner="REF_OWNER" object_name="FX_CLIENT_MARGIN" object_type="TABLE" other_tag="PARALLEL_COMBINED_WITH_PARENT" search_columns="0" cost="171" cardinality="46,810" bytes="2,668,170" cpu_cost="44,214,651" io_cost="169" qblock_name="SEL$C772B8D1" access_predicates=":Z&gt;=:Z AND :Z&lt;=:Z" filter_predicates="(&quot;FCM&quot;.&quot;EFFECTIVE_END_DATE&quot;&gt;:B1 AND &quot;FCM&quot;.&quot;WORKFLOW_STATUS&quot;=:B2 AND &quot;FCM&quot;.&quot;ACTIVE_FLAG&quot;=:B3 AND &quot;FCM&quot;.&quot;LIVE_DATE&quot;&lt;=:B1 AND &quot;FCM&quot;.&quot;EFFECTIVE_START_DATE&quot;&lt;=:B1)" time="1"/>
                                        </PlanElements>
                                      </PlanElement>
                                    </PlanElements>
                                  </PlanElement>
                                </PlanElements>
                              </PlanElement>
                            </PlanElements>
                          </PlanElement>
                        </PlanElements>
                      </PlanElement>
                    </PlanElements>
                  </PlanElement>
                </PlanElements>
              </PlanElement>
            </PlanElements>
          </PlanElement>
        </PlanElements>
      </PlanElement>
    </PlanElements>
  </PlanElement>
</ExplainPlan>



  • Attachment: picture.doc
    (Size: 56.39KB, Downloaded 144 times)
Re: Insert Statement taking more than 2 hrs for 4 million records [message #673950 is a reply to message #673949] Wed, 19 December 2018 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 66249
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Unreadable.
Use SQL*Plus.

Re: Insert Statement taking more than 2 hrs for 4 million records [message #673951 is a reply to message #673949] Wed, 19 December 2018 08:09 Go to previous messageGo to next message
John Watson
Messages: 7811
Registered: January 2010
Location: Global Village
Senior Member
Do you really think that plan is readable?
However, one thing is clear from the first few lines of the XML mess: your insert is not parallel. You have probably not enabled parallel DML.

I wonder what it is about TOAD. Does it attract users of lower ability than those who use SQL*Plus, or does it cause otherwise capable people to stop thinking?
Re: Insert Statement taking more than 2 hrs for 4 million records [message #673972 is a reply to message #673951] Thu, 20 December 2018 05:10 Go to previous messageGo to next message
cookiemonster
Messages: 13497
Registered: September 2008
Location: Rainy Manchester
Senior Member
Sample bias - Toad users who do know what they're doing will post a readable plain text explain plan and you'll never know they use Toad.

EDIT: multiple typos <sigh>

[Updated on: Thu, 20 December 2018 05:11]

Report message to a moderator

Re: Insert Statement taking more than 2 hrs for 4 million records [message #674087 is a reply to message #673972] Thu, 03 January 2019 03:43 Go to previous messageGo to next message
srinivas.k2005
Messages: 394
Registered: August 2006
Senior Member
Sorry was away on vacation, does below help


Plan
INSERT STATEMENT  ALL_ROWSCost: 192                                                                                                              
    71 LOAD AS SELECT                                                                                                          
        70 PX COORDINATOR                                                                                                      
            69 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10014 :Q1014Cost: 192  Bytes: 7,906,512  Cardinality: 20,752                                                                                                  
                68 HASH JOIN RIGHT ANTI BUFFERED PARALLEL_COMBINED_WITH_PARENT :Q1014Cost: 192  Bytes: 7,906,512  Cardinality: 20,752                                                                                              
                    16 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1014Cost: 2  Bytes: 78  Cardinality: 1                                                                                          
                        15 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10012 :Q1012Cost: 2  Bytes: 78  Cardinality: 1                                                                                      
                            14 VIEW VIEW PARALLEL_COMBINED_WITH_PARENT VW_SQ_1 :Q1012Cost: 2  Bytes: 78  Cardinality: 1                                                                                  
                                13 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1012Cost: 2  Bytes: 231  Cardinality: 1                                                                              
                                    11 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1012Cost: 2  Bytes: 231  Cardinality: 1                                                                          
                                        8 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1012Cost: 2  Bytes: 214  Cardinality: 1                                                                      
                                            5 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1012Cost: 2  Bytes: 135  Cardinality: 1                                                                  
                                                2 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1012                                                            
                                                    1 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT REF_OWNER.FX_CLIENT_PROMOTION_DETAIL :Q1012Cost: 2  Bytes: 65  Cardinality: 1                                                          
                                                4 TABLE ACCESS BY INDEX ROWID TABLE PARALLEL_COMBINED_WITH_PARENT REF_OWNER.FX_CLIENT_PROMOTION :Q1012Cost: 0  Bytes: 70  Cardinality: 1                                                              
                                                    3 INDEX RANGE SCAN INDEX (UNIQUE) PARALLEL_COMBINED_WITH_PARENT REF_OWNER.FCP_PK :Q1012Cost: 0  Cardinality: 1                                                          
                                            7 TABLE ACCESS BY INDEX ROWID TABLE PARALLEL_COMBINED_WITH_PARENT REF_OWNER.FX_PROMOTION_CPPM_MAP :Q1012Cost: 0  Bytes: 79  Cardinality: 1                                                                  
                                                6 INDEX RANGE SCAN INDEX (UNIQUE) PARALLEL_COMBINED_WITH_PARENT REF_OWNER.FPC_UK_PRO_CLI_GRP_ID :Q1012Cost: 0  Cardinality: 1                                                              
                                        10 PARTITION LIST SINGLE PARALLEL_COMBINED_WITH_PARENT :Q1012Cost: 0  Cardinality: 1  Partition #: 18  Partitions determined by Key Values                                                                    
                                            9 INDEX RANGE SCAN INDEX PARALLEL_COMBINED_WITH_PARENT RDD_OWNER.I_CPP_IDX_1 :Q1012Cost: 0  Cardinality: 1  Partition #: 18  Partitions determined by Key Values                                                                
                                    12 TABLE ACCESS BY LOCAL INDEX ROWID TABLE PARALLEL_COMBINED_WITH_PARENT RDD_OWNER.RDS_CLIENT_PROGRAM_PKG_GRP :Q1012Cost: 0  Bytes: 17  Cardinality: 1  Partition #: 18  Partitions accessed #1                                                                        
                    67 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1014Cost: 189  Bytes: 6,287,856  Cardinality: 20,752                                                                                          
                        66 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10013 :Q1013Cost: 189  Bytes: 6,287,856  Cardinality: 20,752                                                                                      
                            65 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1013Cost: 189  Bytes: 6,287,856  Cardinality: 20,752                                                                                  
                                20 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1013Cost: 2  Bytes: 3,660  Cardinality: 183                                                                              
                                    19 PX SEND BROADCAST PARALLEL_TO_PARALLEL SYS.:TQ10010 :Q1010Cost: 2  Bytes: 3,660  Cardinality: 183                                                                          
                                        18 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1010Cost: 2  Bytes: 3,660  Cardinality: 183  Partition #: 26  Partitions determined by Key Values                                                                    
                                            17 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT RDD_OWNER.RDS_CURRENCY :Q1010Cost: 2  Bytes: 3,660  Cardinality: 183  Partition #: 26  Partitions determined by Key Values                                                                
                                64 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1013Cost: 187  Bytes: 5,872,816  Cardinality: 20,752                                                                              
                                    61 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1013Cost: 16  Bytes: 113,000  Cardinality: 500                                                                          
                                        60 PX SEND BROADCAST PARALLEL_TO_PARALLEL SYS.:TQ10011 :Q1011Cost: 16  Bytes: 113,000  Cardinality: 500                                                                      
                                            59 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1011Cost: 16  Bytes: 113,000  Cardinality: 500                                                                  
                                                52 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1011Cost: 12  Bytes: 120,267  Cardinality: 747                                                              
                                                    51 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10009 :Q1009Cost: 12  Bytes: 120,267  Cardinality: 747                                                          
                                                        50 HASH JOIN BUFFERED PARALLEL_COMBINED_WITH_PARENT :Q1009Cost: 12  Bytes: 120,267  Cardinality: 747                                                      
                                                            24 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1009Cost: 2  Bytes: 51,428  Cardinality: 1,118                                                  
                                                                23 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10007 :Q1007Cost: 2  Bytes: 51,428  Cardinality: 1,118                                              
                                                                    22 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1007Cost: 2  Bytes: 51,428  Cardinality: 1,118                                          
                                                                        21 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT REF_OWNER.CONTRACT :Q1007Cost: 2  Bytes: 51,428  Cardinality: 1,118                                      
                                                            49 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1009Cost: 10  Bytes: 151,800  Cardinality: 1,320                                                  
                                                                48 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10008 :Q1008Cost: 10  Bytes: 151,800  Cardinality: 1,320                                              
                                                                    47 HASH JOIN BUFFERED PARALLEL_COMBINED_WITH_PARENT :Q1008Cost: 10  Bytes: 151,800  Cardinality: 1,320                                          
                                                                        42 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1008Cost: 6  Bytes: 36,729  Cardinality: 477                                      
                                                                            41 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10005 :Q1005Cost: 6  Bytes: 36,729  Cardinality: 477                                  
                                                                                40 HASH JOIN BUFFERED PARALLEL_COMBINED_WITH_PARENT :Q1005Cost: 6  Bytes: 36,729  Cardinality: 477                              
                                                                                    35 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1005Cost: 4  Bytes: 9,750  Cardinality: 250                          
                                                                                        34 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10003 :Q1003Cost: 4  Bytes: 9,750  Cardinality: 250                      
                                                                                            33 HASH JOIN BUFFERED PARALLEL_COMBINED_WITH_PARENT :Q1003Cost: 4  Bytes: 9,750  Cardinality: 250                  
                                                                                                28 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1003Cost: 2  Bytes: 4,750  Cardinality: 250              
                                                                                                    27 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10001 :Q1001Cost: 2  Bytes: 4,750  Cardinality: 250          
                                                                                                        26 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1001Cost: 2  Bytes: 4,750  Cardinality: 250  Partition #: 50  Partitions determined by Key Values    
                                                                                                            25 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT RDD_OWNER.RDS_COUNTRY :Q1001Cost: 2  Bytes: 4,750  Cardinality: 250  Partition #: 50  Partitions determined by Key Values
                                                                                                32 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1003Cost: 2  Bytes: 3,660  Cardinality: 183              
                                                                                                    31 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10002 :Q1002Cost: 2  Bytes: 3,660  Cardinality: 183          
                                                                                                        30 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1002Cost: 2  Bytes: 3,660  Cardinality: 183  Partition #: 54  Partitions determined by Key Values    
                                                                                                            29 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT RDD_OWNER.RDS_CURRENCY :Q1002Cost: 2  Bytes: 3,660  Cardinality: 183  Partition #: 54  Partitions determined by Key Values
                                                                                    39 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1005Cost: 2  Bytes: 18,126  Cardinality: 477                          
                                                                                        38 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10004 :Q1004Cost: 2  Bytes: 18,126  Cardinality: 477                      
                                                                                            37 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1004Cost: 2  Bytes: 18,126  Cardinality: 477  Partition #: 58  Partitions determined by Key Values                
                                                                                                36 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT RDD_OWNER.RDS_PROGRAM_PACKAGE_GROUP :Q1004Cost: 2  Bytes: 18,126  Cardinality: 477  Partition #: 58  Partitions determined by Key Values            
                                                                        46 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1008Cost: 3  Bytes: 74,290  Cardinality: 1,955                                      
                                                                            45 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10006 :Q1006Cost: 3  Bytes: 74,290  Cardinality: 1,955                                  
                                                                                44 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1006Cost: 3  Bytes: 74,290  Cardinality: 1,955                              
                                                                                    43 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT REF_OWNER.CONTRACT_PPG_MAP :Q1006Cost: 3  Bytes: 74,290  Cardinality: 1,955                          
                                                58 BUFFER SORT PARALLEL_COMBINED_WITH_CHILD :Q1011                                                            
                                                    57 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1011Cost: 4  Bytes: 11,683,360  Cardinality: 179,744                                                          
                                                        56 PX SEND HASH PARALLEL_FROM_SERIAL SYS.:TQ10000 Cost: 4  Bytes: 11,683,360  Cardinality: 179,744                                                      
                                                            55 PARTITION LIST SINGLE  Cost: 4  Bytes: 11,683,360  Cardinality: 179,744  Partition #: 67  Partitions determined by Key Values                                                
                                                                54 TABLE ACCESS BY LOCAL INDEX ROWID TABLE RDD_OWNER.RDS_CLIENT_PROGRAM_PKG_GRP Cost: 4  Bytes: 11,683,360  Cardinality: 179,744  Partition #: 67  Partitions determined by Key Values                                            
                                                                    53 INDEX RANGE SCAN INDEX RDD_OWNER.I_CPP_IDX_1 Cost: 3  Cardinality: 1  Partition #: 67  Partitions determined by Key Values                                        
                                    63 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1013Cost: 171  Bytes: 2,668,170  Cardinality: 46,810                                                                          
                                        62 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT REF_OWNER.FX_CLIENT_MARGIN :Q1013Cost: 171  Bytes: 2,668,170  Cardinality: 46,810                                                                      


Re: Insert Statement taking more than 2 hrs for 4 million records [message #674089 is a reply to message #674087] Thu, 03 January 2019 04:20 Go to previous messageGo to next message
John Watson
Messages: 7811
Registered: January 2010
Location: Global Village
Senior Member
This is trolling, isn't it: being deliberately stupid in order to make people angry.

I shall lock the topic. If you have a genuine question, start a new topic and follow the forum guidelines for posting the necessary information.
Insert Statement taking more than 2 hrs for 4 million records [message #674091 is a reply to message #673926] Thu, 03 January 2019 05:04 Go to previous messageGo to next message
srinivas.k2005
Messages: 394
Registered: August 2006
Senior Member

Opening a new thread on my old topic based on experts advise

Hi,

I have an insert statement and it takes 2 hrs to get inserted, I have explain plan and the insert below. In Toad session browser  shows 
that Sort Output operation is taking more time under Long operations attached the image, but I cannot see any sort operation in the insert statement except the buffer sort. 
Can you please help me with this performance issue.

Below is the insert statement, Please let me know if you need more details

INSERT /*+ append parallel(6) */
      INTO  RDS_CLIENT_FX_MARGIN (CLIENT_PROGRAM_PKG_GRP_ID,
                                  REF_DATA_CYCLE_DAY_OF_WEEK,
                                  REFERENCE_DATA_CYCLE_DATE,
                                  CP_ORG_ID,
                                  CP_ORG_REG_OFFICE_ID,
                                  CP_ORG_BRANCH_OFFICE_ID,
                                  CP_PPG_ID,
                                  BASE_ISO_CURREANCY_CODE_3,
                                  QUOTE_ISO_CURREANCY_CODE_3,
                                  ISO_COUNTRY_CODE_2,
                                  CHANNEL_CODE,
                                  TRANSACTION_TYPE,
                                  LOW_TIER,
                                  HIGH_TIER,
                                  FX_CLIENT_MARGIN_PCT,
                                  FX_RATE_SERVER_FREQ_ID)
   SELECT CPPG.CLIENT_PROGRAM_PKG_GRP_ID,
          :B4,
          :B1,
          CPPG.CP_ORG_ID,
          CPPG.CP_ORG_REG_OFFICE_ID,
          CPPG.CP_ORG_BRANCH_OFFICE_ID,
          PPG.CP_PPG_ID,
          DECODE (PPG.MCP_FLAG,
                  1, PPG.ISO_CARD_CURRENCY_CODE,
                  RCO_1.ISO_CURRENCY_CODE),
          RCO_2.ISO_CURRENCY_CODE,
          PPG.ISO_BASE_COUNTRY_CODE,
          FCM.CHANNEL_CODE,
          FCM.TRANSACTION_TYPE,
          FCM.LOW_TIER,
          FCM.HIGH_TIER,
          FCM.FX_CLIENT_MARGIN_PCT,
          DECODE (FCM.TRANSACTION_TYPE,
                  1, CPPG.FX_RATE_SERVER_FREQ_LOAD,
                  2, CPPG.FX_RATE_SERVER_FREQ_RELOAD,
                  3, CPPG.FX_RATE_SERVER_FREQ_UNLOAD,
                  4, CPPG.FX_RATE_SERVER_FREQ_CASHOUT,
                  5, CPPG.FX_RATE_SERVER_FREQ_PURSETRANS,
                  CPPG.FX_RATE_SERVER_FREQ_LOAD)
     FROM RDS_CLIENT_PROGRAM_PKG_GRP CPPG,
          RDS_PROGRAM_PACKAGE_GROUP PPG,
          CONTRACT CON,
          CONTRACT_PPG_MAP CPM,
          FX_CLIENT_MARGIN FCM,
          RDS_COUNTRY RCU,
          RDS_CURRENCY RCO_1,
          RDS_CURRENCY RCO_2
    WHERE     CPPG.REFERENCE_DATA_CYCLE_DATE = :B1
          AND CPPG.REF_DATA_CYCLE_DAY_OF_WEEK = :B4
          AND PPG.REFERENCE_DATA_CYCLE_DATE = :B1
          AND PPG.REF_DATA_CYCLE_DAY_OF_WEEK = :B4
         AND RCU.REFERENCE_DATA_CYCLE_DATE = :B1
          AND RCU.REF_DATA_CYCLE_DAY_OF_WEEK = :B4
          AND RCO_1.REFERENCE_DATA_CYCLE_DATE = :B1
          AND RCO_1.REF_DATA_CYCLE_DAY_OF_WEEK = :B4
          AND RCO_2.REFERENCE_DATA_CYCLE_DATE = :B1
          AND RCO_2.REF_DATA_CYCLE_DAY_OF_WEEK = :B4
          AND CPPG.PROGRAM_PACKAGE_GROUP_ID = PPG.PROGRAM_PACKAGE_GROUP_ID
          AND (   (    CON.CONTRACT_LEVEL = 1
                   AND CON.ASSOC_ORG_HIERARCHY_ID =
                          CPPG.CLIENT_ORGANISATION_ID)
               OR (    CON.CONTRACT_LEVEL = 2
                   AND CON.ASSOC_ORG_HIERARCHY_ID =
                          CPPG.CLIENT_REGIONAL_OFFICE_ID))
          AND CON.CONTRACT_ID = FCM.CONTRACT_ID
          AND CON.PRODUCT_TYPE_ID = PPG.PRODUCT_TYPE_ID
          AND RCU.ISO_COUNTRY_CODE_2 = PPG.ISO_BASE_COUNTRY_CODE
          AND RCU.CURRENCY_ID = RCO_1.CURRENCY_ID
          AND FCM.QUOTE_CURRENCY_ID = RCO_2.CURRENCY_ID
          AND CON.ACTIVE_FLAG = :B3
          AND CON.LIVE_DATE <= :B1
          AND CON.WORKFLOW_STATUS = :B2
          AND CON.EFFECTIVE_START_DATE <= :B1
          AND CON.EFFECTIVE_END_DATE > :B1
          AND FCM.ACTIVE_FLAG = :B3
          AND FCM.LIVE_DATE <= :B1
          AND FCM.WORKFLOW_STATUS = :B2
          AND FCM.EFFECTIVE_START_DATE <= :B1
          AND FCM.EFFECTIVE_END_DATE > :B1
          AND CPM.ACTIVE_FLAG = :B3
          AND CPM.LIVE_DATE <= :B1
          AND CPM.WORKFLOW_STATUS = :B2
          AND CPM.EFFECTIVE_START_DATE <= :B1
          AND CPM.EFFECTIVE_END_DATE > :B1
          AND CON.CONTRACT_ID = CPM.CONTRACT_ID
          AND CPM.PROGRAM_PKG_GRP_ID = PPG.PROGRAM_PACKAGE_GROUP_ID
          AND NOT EXISTS
                     (SELECT 1
                        FROM FX_PROMOTION_CPPM_MAP PCM,
                             RDS_CLIENT_PROGRAM_PKG_GRP CPPG_1,
                             FX_CLIENT_PROMOTION FCP,
                             FX_CLIENT_PROMOTION_DETAIL CPD
                       WHERE     CPPG_1.REFERENCE_DATA_CYCLE_DATE = :B1
                             AND CPPG_1.REF_DATA_CYCLE_DAY_OF_WEEK = :B4
                             AND PCM.CLIENT_PROGRAM_PKG_GRP_ID =
                                    CPPG_1.CLIENT_PROGRAM_PKG_GRP_ID
                             AND PCM.ACTIVE_FLAG = :B3
                             AND PCM.LIVE_DATE <= :B1
                             AND PCM.WORKFLOW_STATUS = :B2
                             AND PCM.EFFECTIVE_START_DATE <= :B1
                             AND PCM.EFFECTIVE_END_DATE > :B1
                             AND PCM.FX_CLIENT_PROMOTION_ID =
                                    FCP.FX_CLIENT_PROMOTION_ID
                             AND FCP.ACTIVE_FLAG = :B3
                             AND FCP.WORKFLOW_STATUS = :B2
                             AND FCP.EFFECTIVE_START_DATE <= :B1
                             AND FCP.EFFECTIVE_END_DATE > :B1
                             AND FCP.FX_CLIENT_PROMOTION_ID =
                                    CPD.FX_CLIENT_PROMOTION_ID
                             AND CPPG.CLIENT_PROGRAM_PKG_GRP_ID =
                                    CPPG_1.CLIENT_PROGRAM_PKG_GRP_ID
                             AND FCM.QUOTE_CURRENCY_ID =
                                    FCP.QUOTE_CURRENCY_ID
                             AND FCM.CHANNEL_CODE = CPD.CHANNEL_CODE
                             AND FCM.TRANSACTION_TYPE = CPD.TRANSACTION_TYPE
                             AND FCM.LOW_TIER = CPD.LOW_TIER
                             AND FCM.HIGH_TIER = CPD.HIGH_TIER)



Explan Plan below
INSERT STATEMENT  ALL_ROWSCost: 192                                                                                                              
1 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT REF_OWNER.FX_CLIENT_PROMOTION_DETAIL :Q1012Cost: 2  Bytes: 65  Cardinality: 1                                                          
2 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1012                                                            
3 INDEX RANGE SCAN INDEX (UNIQUE) PARALLEL_COMBINED_WITH_PARENT REF_OWNER.FCP_PK :Q1012Cost: 0  Cardinality: 1                                                          
4 TABLE ACCESS BY INDEX ROWID TABLE PARALLEL_COMBINED_WITH_PARENT REF_OWNER.FX_CLIENT_PROMOTION :Q1012Cost: 0  Bytes: 70  Cardinality: 1                                                              
5 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1012Cost: 2  Bytes: 135  Cardinality: 1                                                                  
6 INDEX RANGE SCAN INDEX (UNIQUE) PARALLEL_COMBINED_WITH_PARENT REF_OWNER.FPC_UK_PRO_CLI_GRP_ID :Q1012Cost: 0  Cardinality: 1                                                              
7 TABLE ACCESS BY INDEX ROWID TABLE PARALLEL_COMBINED_WITH_PARENT REF_OWNER.FX_PROMOTION_CPPM_MAP :Q1012Cost: 0  Bytes: 79  Cardinality: 1                                                                  
8 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1012Cost: 2  Bytes: 214  Cardinality: 1                                                                      
9 INDEX RANGE SCAN INDEX PARALLEL_COMBINED_WITH_PARENT RDD_OWNER.I_CPP_IDX_1 :Q1012Cost: 0  Cardinality: 1  Partition #: 18  Partitions determined by Key Values                                                                
10 PARTITION LIST SINGLE PARALLEL_COMBINED_WITH_PARENT :Q1012Cost: 0  Cardinality: 1  Partition #: 18  Partitions determined by Key Values                                                                    
11 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1012Cost: 2  Bytes: 231  Cardinality: 1                                                                          
12 TABLE ACCESS BY LOCAL INDEX ROWID TABLE PARALLEL_COMBINED_WITH_PARENT RDD_OWNER.RDS_CLIENT_PROGRAM_PKG_GRP :Q1012Cost: 0  Bytes: 17  Cardinality: 1  Partition #: 18  Partitions accessed #1                                                                        
13 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1012Cost: 2  Bytes: 231  Cardinality: 1                                                                              
14 VIEW VIEW PARALLEL_COMBINED_WITH_PARENT VW_SQ_1 :Q1012Cost: 2  Bytes: 78  Cardinality: 1                                                                                  
15 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10012 :Q1012Cost: 2  Bytes: 78  Cardinality: 1                                                                                      
16 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1014Cost: 2  Bytes: 78  Cardinality: 1                                                                                          
17 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT RDD_OWNER.RDS_CURRENCY :Q1010Cost: 2  Bytes: 3,660  Cardinality: 183  Partition #: 26  Partitions determined by Key Values                                                                
18 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1010Cost: 2  Bytes: 3,660  Cardinality: 183  Partition #: 26  Partitions determined by Key Values                                                                    
19 PX SEND BROADCAST PARALLEL_TO_PARALLEL SYS.:TQ10010 :Q1010Cost: 2  Bytes: 3,660  Cardinality: 183                                                                          
20 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1013Cost: 2  Bytes: 3,660  Cardinality: 183                                                                              
21 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT REF_OWNER.CONTRACT :Q1007Cost: 2  Bytes: 51,428  Cardinality: 1,118                                      
22 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1007Cost: 2  Bytes: 51,428  Cardinality: 1,118                                          
23 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10007 :Q1007Cost: 2  Bytes: 51,428  Cardinality: 1,118                                              
24 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1009Cost: 2  Bytes: 51,428  Cardinality: 1,118                                                  
25 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT RDD_OWNER.RDS_COUNTRY :Q1001Cost: 2  Bytes: 4,750  Cardinality: 250  Partition #: 50  Partitions determined by Key Values
26 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1001Cost: 2  Bytes: 4,750  Cardinality: 250  Partition #: 50  Partitions determined by Key Values    
27 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10001 :Q1001Cost: 2  Bytes: 4,750  Cardinality: 250          
28 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1003Cost: 2  Bytes: 4,750  Cardinality: 250              
29 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT RDD_OWNER.RDS_CURRENCY :Q1002Cost: 2  Bytes: 3,660  Cardinality: 183  Partition #: 54  Partitions determined by Key Values
30 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1002Cost: 2  Bytes: 3,660  Cardinality: 183  Partition #: 54  Partitions determined by Key Values    
31 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10002 :Q1002Cost: 2  Bytes: 3,660  Cardinality: 183          
32 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1003Cost: 2  Bytes: 3,660  Cardinality: 183              
33 HASH JOIN BUFFERED PARALLEL_COMBINED_WITH_PARENT :Q1003Cost: 4  Bytes: 9,750  Cardinality: 250                  
34 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10003 :Q1003Cost: 4  Bytes: 9,750  Cardinality: 250                      
35 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1005Cost: 4  Bytes: 9,750  Cardinality: 250                          
36 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT RDD_OWNER.RDS_PROGRAM_PACKAGE_GROUP :Q1004Cost: 2  Bytes: 18,126  Cardinality: 477  Partition #: 58  Partitions determined by Key Values            
37 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1004Cost: 2  Bytes: 18,126  Cardinality: 477  Partition #: 58  Partitions determined by Key Values                
38 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10004 :Q1004Cost: 2  Bytes: 18,126  Cardinality: 477                      
39 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1005Cost: 2  Bytes: 18,126  Cardinality: 477                          
40 HASH JOIN BUFFERED PARALLEL_COMBINED_WITH_PARENT :Q1005Cost: 6  Bytes: 36,729  Cardinality: 477                              
41 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10005 :Q1005Cost: 6  Bytes: 36,729  Cardinality: 477                                  
42 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1008Cost: 6  Bytes: 36,729  Cardinality: 477                                      
43 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT REF_OWNER.CONTRACT_PPG_MAP :Q1006Cost: 3  Bytes: 74,290  Cardinality: 1,955                          
44 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1006Cost: 3  Bytes: 74,290  Cardinality: 1,955                              
45 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10006 :Q1006Cost: 3  Bytes: 74,290  Cardinality: 1,955                                  
46 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1008Cost: 3  Bytes: 74,290  Cardinality: 1,955                                      
47 HASH JOIN BUFFERED PARALLEL_COMBINED_WITH_PARENT :Q1008Cost: 10  Bytes: 151,800  Cardinality: 1,320                                          
48 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10008 :Q1008Cost: 10  Bytes: 151,800  Cardinality: 1,320                                              
49 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1009Cost: 10  Bytes: 151,800  Cardinality: 1,320                                                  
50 HASH JOIN BUFFERED PARALLEL_COMBINED_WITH_PARENT :Q1009Cost: 12  Bytes: 120,267  Cardinality: 747                                                      
51 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10009 :Q1009Cost: 12  Bytes: 120,267  Cardinality: 747                                                          
52 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1011Cost: 12  Bytes: 120,267  Cardinality: 747                                                              
53 INDEX RANGE SCAN INDEX RDD_OWNER.I_CPP_IDX_1 Cost: 3  Cardinality: 1  Partition #: 67  Partitions determined by Key Values                                        
54 TABLE ACCESS BY LOCAL INDEX ROWID TABLE RDD_OWNER.RDS_CLIENT_PROGRAM_PKG_GRP Cost: 4  Bytes: 11,683,360  Cardinality: 179,744  Partition #: 67  Partitions determined by Key Values                                            
55 PARTITION LIST SINGLE  Cost: 4  Bytes: 11,683,360  Cardinality: 179,744  Partition #: 67  Partitions determined by Key Values                                                
56 PX SEND HASH PARALLEL_FROM_SERIAL SYS.:TQ10000 Cost: 4  Bytes: 11,683,360  Cardinality: 179,744                                                      
57 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1011Cost: 4  Bytes: 11,683,360  Cardinality: 179,744                                                          
58 BUFFER SORT PARALLEL_COMBINED_WITH_CHILD :Q1011                                                            
59 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1011Cost: 16  Bytes: 113,000  Cardinality: 500                                                                  
60 PX SEND BROADCAST PARALLEL_TO_PARALLEL SYS.:TQ10011 :Q1011Cost: 16  Bytes: 113,000  Cardinality: 500                                                                      
61 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1013Cost: 16  Bytes: 113,000  Cardinality: 500                                                                          
62 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT REF_OWNER.FX_CLIENT_MARGIN :Q1013Cost: 171  Bytes: 2,668,170  Cardinality: 46,810 
63 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1013Cost: 171  Bytes: 2,668,170  Cardinality: 46,810                                                                          
64 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1013Cost: 187  Bytes: 5,872,816  Cardinality: 20,752                                                                              
65 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1013Cost: 189  Bytes: 6,287,856  Cardinality: 20,752                                                                                  
66 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10013 :Q1013Cost: 189  Bytes: 6,287,856  Cardinality: 20,752                                                                                      
67 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1014Cost: 189  Bytes: 6,287,856  Cardinality: 20,752                                                                                          
68 HASH JOIN RIGHT ANTI BUFFERED PARALLEL_COMBINED_WITH_PARENT :Q1014Cost: 192  Bytes: 7,906,512  Cardinality: 20,752                                                                                              
69 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10014 :Q1014Cost: 192  Bytes: 7,906,512  Cardinality: 20,752                                                                                                  
70 PX COORDINATOR                                                                                                      
71 LOAD AS SELECT                                                                                                          

Thanks,
SRK

Re: Insert Statement taking more than 2 hrs for 4 million records [message #674092 is a reply to message #674091] Thu, 03 January 2019 05:17 Go to previous messageGo to next message
cookiemonster
Messages: 13497
Registered: September 2008
Location: Rainy Manchester
Senior Member
The way to get an explain plan that the rest of us can actually read is to use sqlplus and do this:
SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL>

Re: Insert Statement taking more than 2 hrs for 4 million records [message #674093 is a reply to message #674089] Thu, 03 January 2019 05:19 Go to previous messageGo to next message
cookiemonster
Messages: 13497
Registered: September 2008
Location: Rainy Manchester
Senior Member
John Watson wrote on Thu, 03 January 2019 10:20
This is trolling, isn't it: being deliberately stupid in order to make people angry.

I shall lock the topic. If you have a genuine question, start a new topic and follow the forum guidelines for posting the necessary information.
My default assumption here is the OP is simply unaware of how to generate an explain plan the rest of us can read.
I suspect you'd be surprised with how many coders out there don't know how to do it.
I've given a method and unlocked the thread.
Re: Insert Statement taking more than 2 hrs for 4 million records [message #674113 is a reply to message #674093] Fri, 04 January 2019 04:54 Go to previous messageGo to next message
srinivas.k2005
Messages: 394
Registered: August 2006
Senior Member
Thanks a lot Cookiemonster, extracted the information as you mentioned.

I have an insert statement and it takes 2 hrs to get inserted, I have explain plan and the insert below. In Toad session browser shows
that Sort Output operation is taking more time under Long operations attached the image, but I cannot see any sort operation in the insert statement except the buffer sort.
Can you please help me with this performance issue.

Below is the insert statement, Please let me know if you need more details


INSERT /*+ append parallel(6) */
      INTO  RDS_CLIENT_FX_MARGIN (CLIENT_PROGRAM_PKG_GRP_ID,
                                  REF_DATA_CYCLE_DAY_OF_WEEK,
                                  REFERENCE_DATA_CYCLE_DATE,
                                  CP_ORG_ID,
                                  CP_ORG_REG_OFFICE_ID,
                                  CP_ORG_BRANCH_OFFICE_ID,
                                  CP_PPG_ID,
                                  BASE_ISO_CURREANCY_CODE_3,
                                  QUOTE_ISO_CURREANCY_CODE_3,
                                  ISO_COUNTRY_CODE_2,
                                  CHANNEL_CODE,
                                  TRANSACTION_TYPE,
                                  LOW_TIER,
                                  HIGH_TIER,
                                  FX_CLIENT_MARGIN_PCT,
                                  FX_RATE_SERVER_FREQ_ID)
   SELECT CPPG.CLIENT_PROGRAM_PKG_GRP_ID,
          :B4,
          :B1,
          CPPG.CP_ORG_ID,
          CPPG.CP_ORG_REG_OFFICE_ID,
          CPPG.CP_ORG_BRANCH_OFFICE_ID,
          PPG.CP_PPG_ID,
          DECODE (PPG.MCP_FLAG,
                  1, PPG.ISO_CARD_CURRENCY_CODE,
                  RCO_1.ISO_CURRENCY_CODE),
          RCO_2.ISO_CURRENCY_CODE,
          PPG.ISO_BASE_COUNTRY_CODE,
          FCM.CHANNEL_CODE,
          FCM.TRANSACTION_TYPE,
          FCM.LOW_TIER,
          FCM.HIGH_TIER,
          FCM.FX_CLIENT_MARGIN_PCT,
          DECODE (FCM.TRANSACTION_TYPE,
                  1, CPPG.FX_RATE_SERVER_FREQ_LOAD,
                  2, CPPG.FX_RATE_SERVER_FREQ_RELOAD,
                  3, CPPG.FX_RATE_SERVER_FREQ_UNLOAD,
                  4, CPPG.FX_RATE_SERVER_FREQ_CASHOUT,
                  5, CPPG.FX_RATE_SERVER_FREQ_PURSETRANS,
                  CPPG.FX_RATE_SERVER_FREQ_LOAD)
     FROM RDS_CLIENT_PROGRAM_PKG_GRP CPPG,
          RDS_PROGRAM_PACKAGE_GROUP PPG,
          CONTRACT CON,
          CONTRACT_PPG_MAP CPM,
          FX_CLIENT_MARGIN FCM,
          RDS_COUNTRY RCU,
          RDS_CURRENCY RCO_1,
          RDS_CURRENCY RCO_2
    WHERE     CPPG.REFERENCE_DATA_CYCLE_DATE = :B1
          AND CPPG.REF_DATA_CYCLE_DAY_OF_WEEK = :B4
          AND PPG.REFERENCE_DATA_CYCLE_DATE = :B1
          AND PPG.REF_DATA_CYCLE_DAY_OF_WEEK = :B4
         AND RCU.REFERENCE_DATA_CYCLE_DATE = :B1
          AND RCU.REF_DATA_CYCLE_DAY_OF_WEEK = :B4
          AND RCO_1.REFERENCE_DATA_CYCLE_DATE = :B1
          AND RCO_1.REF_DATA_CYCLE_DAY_OF_WEEK = :B4
          AND RCO_2.REFERENCE_DATA_CYCLE_DATE = :B1
          AND RCO_2.REF_DATA_CYCLE_DAY_OF_WEEK = :B4
          AND CPPG.PROGRAM_PACKAGE_GROUP_ID = PPG.PROGRAM_PACKAGE_GROUP_ID
          AND (   (    CON.CONTRACT_LEVEL = 1
                   AND CON.ASSOC_ORG_HIERARCHY_ID =
                          CPPG.CLIENT_ORGANISATION_ID)
               OR (    CON.CONTRACT_LEVEL = 2
                   AND CON.ASSOC_ORG_HIERARCHY_ID =
                          CPPG.CLIENT_REGIONAL_OFFICE_ID))
          AND CON.CONTRACT_ID = FCM.CONTRACT_ID
          AND CON.PRODUCT_TYPE_ID = PPG.PRODUCT_TYPE_ID
          AND RCU.ISO_COUNTRY_CODE_2 = PPG.ISO_BASE_COUNTRY_CODE
          AND RCU.CURRENCY_ID = RCO_1.CURRENCY_ID
          AND FCM.QUOTE_CURRENCY_ID = RCO_2.CURRENCY_ID
          AND CON.ACTIVE_FLAG = :B3
          AND CON.LIVE_DATE <= :B1
          AND CON.WORKFLOW_STATUS = :B2
          AND CON.EFFECTIVE_START_DATE <= :B1
          AND CON.EFFECTIVE_END_DATE > :B1
          AND FCM.ACTIVE_FLAG = :B3
          AND FCM.LIVE_DATE <= :B1
          AND FCM.WORKFLOW_STATUS = :B2
          AND FCM.EFFECTIVE_START_DATE <= :B1
          AND FCM.EFFECTIVE_END_DATE > :B1
          AND CPM.ACTIVE_FLAG = :B3
          AND CPM.LIVE_DATE <= :B1
          AND CPM.WORKFLOW_STATUS = :B2
          AND CPM.EFFECTIVE_START_DATE <= :B1
          AND CPM.EFFECTIVE_END_DATE > :B1
          AND CON.CONTRACT_ID = CPM.CONTRACT_ID
          AND CPM.PROGRAM_PKG_GRP_ID = PPG.PROGRAM_PACKAGE_GROUP_ID
          AND NOT EXISTS
                     (SELECT 1
                        FROM FX_PROMOTION_CPPM_MAP PCM,
                             RDS_CLIENT_PROGRAM_PKG_GRP CPPG_1,
                             FX_CLIENT_PROMOTION FCP,
                             FX_CLIENT_PROMOTION_DETAIL CPD
                       WHERE     CPPG_1.REFERENCE_DATA_CYCLE_DATE = :B1
                             AND CPPG_1.REF_DATA_CYCLE_DAY_OF_WEEK = :B4
                             AND PCM.CLIENT_PROGRAM_PKG_GRP_ID =
                                    CPPG_1.CLIENT_PROGRAM_PKG_GRP_ID
                             AND PCM.ACTIVE_FLAG = :B3
                             AND PCM.LIVE_DATE <= :B1
                             AND PCM.WORKFLOW_STATUS = :B2
                             AND PCM.EFFECTIVE_START_DATE <= :B1
                             AND PCM.EFFECTIVE_END_DATE > :B1
                             AND PCM.FX_CLIENT_PROMOTION_ID =
                                    FCP.FX_CLIENT_PROMOTION_ID
                             AND FCP.ACTIVE_FLAG = :B3
                             AND FCP.WORKFLOW_STATUS = :B2
                             AND FCP.EFFECTIVE_START_DATE <= :B1
                             AND FCP.EFFECTIVE_END_DATE > :B1
                             AND FCP.FX_CLIENT_PROMOTION_ID =
                                    CPD.FX_CLIENT_PROMOTION_ID
                             AND CPPG.CLIENT_PROGRAM_PKG_GRP_ID =
                                    CPPG_1.CLIENT_PROGRAM_PKG_GRP_ID
                             AND FCM.QUOTE_CURRENCY_ID =
                                    FCP.QUOTE_CURRENCY_ID
                             AND FCM.CHANNEL_CODE = CPD.CHANNEL_CODE
                             AND FCM.TRANSACTION_TYPE = CPD.TRANSACTION_TYPE
                             AND FCM.LOW_TIER = CPD.LOW_TIER
                             AND FCM.HIGH_TIER = CPD.HIGH_TIER)

Plan hash value: 240986277

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                        | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                                 |                            |       |       |   165 (100)|          |       |       |        |      |            |
|   1 |  LOAD AS SELECT                                  |                            |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR                                 |                            |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)                           | :TQ10014                   | 26091 |  9707K|   165   (2)| 00:00:01 |       |       |  Q1,14 | P->S | QC (RAND)  |
|   4 |     HASH JOIN RIGHT ANTI BUFFERED                |                            | 26091 |  9707K|   165   (2)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
|   5 |      PX RECEIVE                                  |                            |     1 |    78 |     2   (0)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
|   6 |       PX SEND HASH                               | :TQ10012                   |     1 |    78 |     2   (0)| 00:00:01 |       |       |  Q1,12 | P->P | HASH       |
|   7 |        VIEW                                      | VW_SQ_1                    |     1 |    78 |     2   (0)| 00:00:01 |       |       |  Q1,12 | PCWP |            |
|   8 |         NESTED LOOPS                             |                            |     1 |   231 |     2   (0)| 00:00:01 |       |       |  Q1,12 | PCWP |            |
|   9 |          NESTED LOOPS                            |                            |     1 |   231 |     2   (0)| 00:00:01 |       |       |  Q1,12 | PCWP |            |
|  10 |           NESTED LOOPS                           |                            |     1 |   214 |     2   (0)| 00:00:01 |       |       |  Q1,12 | PCWP |            |
|  11 |            NESTED LOOPS                          |                            |     1 |   135 |     2   (0)| 00:00:01 |       |       |  Q1,12 | PCWP |            |
|  12 |             PX BLOCK ITERATOR                    |                            |       |       |            |          |       |       |  Q1,12 | PCWC |            |
|  13 |              TABLE ACCESS FULL                   | FX_CLIENT_PROMOTION_DETAIL |     1 |    65 |     2   (0)| 00:00:01 |       |       |  Q1,12 | PCWP |            |
|  14 |             TABLE ACCESS BY INDEX ROWID          | FX_CLIENT_PROMOTION        |     1 |    70 |     0   (0)|          |       |       |  Q1,12 | PCWP |            |
|  15 |              INDEX RANGE SCAN                    | FCP_PK                     |     1 |       |     0   (0)|          |       |       |  Q1,12 | PCWP |            |
|  16 |            TABLE ACCESS BY INDEX ROWID           | FX_PROMOTION_CPPM_MAP      |     1 |    79 |     0   (0)|          |       |       |  Q1,12 | PCWP |            |
|  17 |             INDEX RANGE SCAN                     | FPC_UK_PRO_CLI_GRP_ID      |     1 |       |     0   (0)|          |       |       |  Q1,12 | PCWP |            |
|  18 |           PARTITION LIST SINGLE                  |                            |     1 |       |     0   (0)|          |   KEY |   KEY |  Q1,12 | PCWP |            |
|  19 |            INDEX RANGE SCAN                      | I_CPP_IDX_1                |     1 |       |     0   (0)|          |   KEY |   KEY |  Q1,12 | PCWP |            |
|  20 |          TABLE ACCESS BY LOCAL INDEX ROWID       | RDS_CLIENT_PROGRAM_PKG_GRP |     1 |    17 |     0   (0)|          |     1 |     1 |  Q1,12 | PCWP |            |
|  21 |      PX RECEIVE                                  |                            | 26091 |  7720K|   162   (2)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
|  22 |       PX SEND HASH                               | :TQ10013                   | 26091 |  7720K|   162   (2)| 00:00:01 |       |       |  Q1,13 | P->P | HASH       |
|  23 |        HASH JOIN                                 |                            | 26091 |  7720K|   162   (2)| 00:00:01 |       |       |  Q1,13 | PCWP |            |
|  24 |         PX RECEIVE                               |                            |   183 |  3660 |     2   (0)| 00:00:01 |       |       |  Q1,13 | PCWP |            |
|  25 |          PX SEND BROADCAST                       | :TQ10010                   |   183 |  3660 |     2   (0)| 00:00:01 |       |       |  Q1,10 | P->P | BROADCAST  |
|  26 |           PX BLOCK ITERATOR                      |                            |   183 |  3660 |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,10 | PCWC |            |
|  27 |            TABLE ACCESS FULL                     | RDS_CURRENCY               |   183 |  3660 |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,10 | PCWP |            |
|  28 |         HASH JOIN                                |                            | 26091 |  7210K|   160   (2)| 00:00:01 |       |       |  Q1,13 | PCWP |            |
|  29 |          PX RECEIVE                              |                            |   454 |   100K|    16   (7)| 00:00:01 |       |       |  Q1,13 | PCWP |            |
|  30 |           PX SEND BROADCAST                      | :TQ10011                   |   454 |   100K|    16   (7)| 00:00:01 |       |       |  Q1,11 | P->P | BROADCAST  |
|  31 |            HASH JOIN                             |                            |   454 |   100K|    16   (7)| 00:00:01 |       |       |  Q1,11 | PCWP |            |
|  32 |             PX RECEIVE                           |                            |   914 |   143K|    12   (9)| 00:00:01 |       |       |  Q1,11 | PCWP |            |
|  33 |              PX SEND HASH                        | :TQ10009                   |   914 |   143K|    12   (9)| 00:00:01 |       |       |  Q1,09 | P->P | HASH       |
|  34 |               HASH JOIN BUFFERED                 |                            |   914 |   143K|    12   (9)| 00:00:01 |       |       |  Q1,09 | PCWP |            |
|  35 |                PX RECEIVE                        |                            |  1092 | 50232 |     2   (0)| 00:00:01 |       |       |  Q1,09 | PCWP |            |
|  36 |                 PX SEND HASH                     | :TQ10007                   |  1092 | 50232 |     2   (0)| 00:00:01 |       |       |  Q1,07 | P->P | HASH       |
|  37 |                  PX BLOCK ITERATOR               |                            |  1092 | 50232 |     2   (0)| 00:00:01 |       |       |  Q1,07 | PCWC |            |
|  38 |                   TABLE ACCESS FULL              | CONTRACT                   |  1092 | 50232 |     2   (0)| 00:00:01 |       |       |  Q1,07 | PCWP |            |
|  39 |                PX RECEIVE                        |                            |  1581 |   177K|    10  (10)| 00:00:01 |       |       |  Q1,09 | PCWP |            |
|  40 |                 PX SEND HASH                     | :TQ10008                   |  1581 |   177K|    10  (10)| 00:00:01 |       |       |  Q1,08 | P->P | HASH       |
|  41 |                  HASH JOIN BUFFERED              |                            |  1581 |   177K|    10  (10)| 00:00:01 |       |       |  Q1,08 | PCWP |            |
|  42 |                   PX RECEIVE                     |                            |   572 | 44044 |     6   (0)| 00:00:01 |       |       |  Q1,08 | PCWP |            |
|  43 |                    PX SEND HASH                  | :TQ10005                   |   572 | 44044 |     6   (0)| 00:00:01 |       |       |  Q1,05 | P->P | HASH       |
|  44 |                     HASH JOIN BUFFERED           |                            |   572 | 44044 |     6   (0)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
|  45 |                      PX RECEIVE                  |                            |   250 |  9750 |     4   (0)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
|  46 |                       PX SEND HASH               | :TQ10003                   |   250 |  9750 |     4   (0)| 00:00:01 |       |       |  Q1,03 | P->P | HASH       |
|  47 |                        HASH JOIN BUFFERED        |                            |   250 |  9750 |     4   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  48 |                         PX RECEIVE               |                            |   250 |  4750 |     2   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  49 |                          PX SEND HASH            | :TQ10001                   |   250 |  4750 |     2   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |
|  50 |                           PX BLOCK ITERATOR      |                            |   250 |  4750 |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,01 | PCWC |            |
|  51 |                            TABLE ACCESS FULL     | RDS_COUNTRY                |   250 |  4750 |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,01 | PCWP |            |
|  52 |                         PX RECEIVE               |                            |   183 |  3660 |     2   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  53 |                          PX SEND HASH            | :TQ10002                   |   183 |  3660 |     2   (0)| 00:00:01 |       |       |  Q1,02 | P->P | HASH       |
|  54 |                           PX BLOCK ITERATOR      |                            |   183 |  3660 |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,02 | PCWC |            |
|  55 |                            TABLE ACCESS FULL     | RDS_CURRENCY               |   183 |  3660 |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,02 | PCWP |            |
|  56 |                      PX RECEIVE                  |                            |   572 | 21736 |     2   (0)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
|  57 |                       PX SEND HASH               | :TQ10004                   |   572 | 21736 |     2   (0)| 00:00:01 |       |       |  Q1,04 | P->P | HASH       |
|  58 |                        PX BLOCK ITERATOR         |                            |   572 | 21736 |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,04 | PCWC |            |
|  59 |                         TABLE ACCESS FULL        | RDS_PROGRAM_PACKAGE_GROUP  |   572 | 21736 |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,04 | PCWP |            |
|  60 |                   PX RECEIVE                     |                            |  1874 | 71212 |     3   (0)| 00:00:01 |       |       |  Q1,08 | PCWP |            |
|  61 |                    PX SEND HASH                  | :TQ10006                   |  1874 | 71212 |     3   (0)| 00:00:01 |       |       |  Q1,06 | P->P | HASH       |
|  62 |                     PX BLOCK ITERATOR            |                            |  1874 | 71212 |     3   (0)| 00:00:01 |       |       |  Q1,06 | PCWC |            |
|  63 |                      TABLE ACCESS FULL           | CONTRACT_PPG_MAP           |  1874 | 71212 |     3   (0)| 00:00:01 |       |       |  Q1,06 | PCWP |            |
|  64 |             BUFFER SORT                          |                            |       |       |            |          |       |       |  Q1,11 | PCWC |            |
|  65 |              PX RECEIVE                          |                            |   220K|    13M|     4   (0)| 00:00:01 |       |       |  Q1,11 | PCWP |            |
|  66 |               PX SEND HASH                       | :TQ10000                   |   220K|    13M|     4   (0)| 00:00:01 |       |       |        | S->P | HASH       |
|  67 |                PARTITION LIST SINGLE             |                            |   220K|    13M|     4   (0)| 00:00:01 |   KEY |   KEY |        |      |            |
|  68 |                 TABLE ACCESS BY LOCAL INDEX ROWID| RDS_CLIENT_PROGRAM_PKG_GRP |   220K|    13M|     4   (0)| 00:00:01 |   KEY |   KEY |        |      |            |
|  69 |                  INDEX RANGE SCAN                | I_CPP_IDX_1                |     1 |       |     3   (0)| 00:00:01 |   KEY |   KEY |        |      |            |
|  70 |          PX BLOCK ITERATOR                       |                            | 61439 |  3419K|   144   (2)| 00:00:01 |       |       |  Q1,13 | PCWC |            |
|  71 |           TABLE ACCESS FULL                      | FX_CLIENT_MARGIN           | 61439 |  3419K|   144   (2)| 00:00:01 |       |       |  Q1,13 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=6)
   - Degree of Parallelism is 6 because of hint


[Updated on: Fri, 04 January 2019 04:56]

Report message to a moderator

Re: Insert Statement taking more than 2 hrs for 4 million records [message #674114 is a reply to message #674113] Fri, 04 January 2019 05:00 Go to previous messageGo to next message
cookiemonster
Messages: 13497
Registered: September 2008
Location: Rainy Manchester
Senior Member
Did you generate that explain plan on the DB where it's running slow? I ask because that plan thinks it's going to be super fast.
If didn't, do so and post the correct plan here.
Re: Insert Statement taking more than 2 hrs for 4 million records [message #674115 is a reply to message #674113] Fri, 04 January 2019 05:04 Go to previous messageGo to next message
John Watson
Messages: 7811
Registered: January 2010
Location: Global Village
Senior Member
A plan one can read!

As I said before, the point that jumps out is that you are not doing a parallel insert. I also told you the reason.
Re: Insert Statement taking more than 2 hrs for 4 million records [message #674117 is a reply to message #674114] Fri, 04 January 2019 06:14 Go to previous messageGo to next message
srinivas.k2005
Messages: 394
Registered: August 2006
Senior Member
Hi,

Yes it is from PROD, even i was surprised initially to see it running so long with all good in the plan hence posted here. While it runs i monitored in session browser of toad on what exactly it is doing and it shows me below sessions running parallel, it is the image i attached earlier. More time was in buffer sort and shows the time against the operation of reading the blocks. I have changed the attached image to a table as below.

% Complete	Message	Elapsed                                 seconds
100	        Sort/Merge :  77112 out of 77112 blocks done	32
100	        Sort/Merge :  69900 out of 69900 blocks done	32
100	        Sort Output : 91019 out of 91019 blocks done	2132
100	        Sort Output : 91024 out of 91024 blocks done	2030
100	        Sort Output : 77093 out of 77093 blocks done	1330
60	        Sort Output : 41749 out of 69865 blocks done	766
100	        Sort/Merge :  91044 out of 91044 blocks done	29
100	        Sort/Merge :  91078 out of 91078 blocks done	51

Thanks,
SRK
Re: Insert Statement taking more than 2 hrs for 4 million records [message #674118 is a reply to message #674115] Fri, 04 January 2019 06:18 Go to previous messageGo to next message
srinivas.k2005
Messages: 394
Registered: August 2006
Senior Member
Sorry John, missed it. The table definition has NOPARALLEL i will change it and try in lower environment.

Thanks,
SRK
Re: Insert Statement taking more than 2 hrs for 4 million records [message #674119 is a reply to message #674118] Fri, 04 January 2019 06:25 Go to previous messageGo to next message
John Watson
Messages: 7811
Registered: January 2010
Location: Global Village
Senior Member
srinivas.k2005 wrote on Fri, 04 January 2019 12:18
Sorry John, missed it. The table definition has NOPARALLEL i will change it and try in lower environment.

Thanks,
SRK
Why are you doing this? I shall repeat my advice, in the hope that you will read it this time:Quote:
You have probably not enabled parallel DML.
I am also deeply suspicious of that execution plan. Did you generate it the way Cookie Monster asked you to? Or some other way? The reason I ask it that a lot of information that should be there is missing. Particularly, the predicate section. You must show that, so that you can see where the cardinality estimates are going wrong. And they are for certain going wrong. How do I know that? Because you claim that 4000000 rows (please do not say "record" when you mean "row") are inserted, but the plan shows only 26091 rows.
Re: Insert Statement taking more than 2 hrs for 4 million records [message #674120 is a reply to message #674119] Fri, 04 January 2019 08:01 Go to previous messageGo to next message
srinivas.k2005
Messages: 394
Registered: August 2006
Senior Member
Yes, i shared the same steps to DBA. I will check and come back with more details.
Re: Insert Statement taking more than 2 hrs for 4 million records [message #674171 is a reply to message #674120] Tue, 08 January 2019 11:19 Go to previous messageGo to next message
srinivas.k2005
Messages: 394
Registered: August 2006
Senior Member
Hi,

Requested information from DBA from PROD, he shared me below information, this includes predicate section. On the row count in below case it is 35K and earlier it was 28K rows, when I started looking this a month back it was around 4 million rows, fact was it was taking 2 hours and more before a month and recent stats it is taking 1 hr for 25k to 35k to complete the insertion.

Worked on enabling Parallel to the table in Stage. Usually, it takes 2 mins after enabling parallel it completed in 1.5min, will take a lot of time to get this change to PROD.

In meantime, the PROD Plan below looks good to me, and 25K to 35 K records taking 1 hour is really a problem which I am not able to figure it out correctly. Please need your help


 
Plan hash value: 3244715335
 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                        | Name                       | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |  OMem |  1Mem |  O/1/M   |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                                 |                            |        |       |   192 (100)|          |       |       |        |      |            |       |       |          |
|   1 |  LOAD AS SELECT                                  |                            |        |       |            |          |       |       |        |      |            |   256K|   256K|          |
|   2 |   PX COORDINATOR                                 |                            |        |       |            |          |       |       |        |      |            |       |       |          |
|   3 |    PX SEND QC (RANDOM)                           | :TQ10014                   |  35312 |    12M|   192   (3)| 00:00:02 |       |       |  Q1,14 | P->S | QC (RAND)  |       |       |          |
|*  4 |     HASH JOIN RIGHT ANTI BUFFERED                |                            |  35312 |    12M|   192   (3)| 00:00:02 |       |       |  Q1,14 | PCWP |            |  1080K|  1080K|          |
|   5 |      PX RECEIVE                                  |                            |      1 |    78 |     2   (0)| 00:00:01 |       |       |  Q1,14 | PCWP |            |       |       |          |
|   6 |       PX SEND HASH                               | :TQ10012                   |      1 |    78 |     2   (0)| 00:00:01 |       |       |  Q1,12 | P->P | HASH       |       |       |          |
|   7 |        VIEW                                      | VW_SQ_1                    |      1 |    78 |     2   (0)| 00:00:01 |       |       |  Q1,12 | PCWP |            |       |       |          |
|   8 |         NESTED LOOPS                             |                            |      1 |   231 |     2   (0)| 00:00:01 |       |       |  Q1,12 | PCWP |            |       |       |          |
|   9 |          NESTED LOOPS                            |                            |      1 |   214 |     2   (0)| 00:00:01 |       |       |  Q1,12 | PCWP |            |       |       |          |
|  10 |           NESTED LOOPS                           |                            |      1 |   135 |     2   (0)| 00:00:01 |       |       |  Q1,12 | PCWP |            |       |       |          |
|  11 |            PX BLOCK ITERATOR                     |                            |        |       |            |          |       |       |  Q1,12 | PCWC |            |       |       |          |
|* 12 |             TABLE ACCESS FULL                    | FX_CLIENT_PROMOTION_DETAIL |      1 |    65 |     2   (0)| 00:00:01 |       |       |  Q1,12 | PCWP |            |       |       |          |
|* 13 |            TABLE ACCESS BY INDEX ROWID           | FX_CLIENT_PROMOTION        |      1 |    70 |     0   (0)|          |       |       |  Q1,12 | PCWP |            |       |       |          |
|* 14 |             INDEX RANGE SCAN                     | FCP_PK                     |      1 |       |     0   (0)|          |       |       |  Q1,12 | PCWP |            |       |       |          |
|* 15 |           TABLE ACCESS BY INDEX ROWID            | FX_PROMOTION_CPPM_MAP      |      1 |    79 |     0   (0)|          |       |       |  Q1,12 | PCWP |            |       |       |          |
|* 16 |            INDEX RANGE SCAN                      | FPC_UK_PRO_CLI_GRP_ID      |      1 |       |     0   (0)|          |       |       |  Q1,12 | PCWP |            |       |       |          |
|  17 |          PARTITION LIST SINGLE                   |                            |      1 |    17 |     0   (0)|          |   KEY |   KEY |  Q1,12 | PCWP |            |       |       |          |
|* 18 |           INDEX RANGE SCAN                       | I_CPP_IDX_1                |      1 |    17 |     0   (0)|          |     4 |     4 |  Q1,12 | PCWP |            |       |       |          |
|  19 |      PX RECEIVE                                  |                            |  35312 |    10M|   190   (3)| 00:00:02 |       |       |  Q1,14 | PCWP |            |       |       |          |
|  20 |       PX SEND HASH                               | :TQ10013                   |  35312 |    10M|   190   (3)| 00:00:02 |       |       |  Q1,13 | P->P | HASH       |       |       |          |
|* 21 |        HASH JOIN                                 |                            |  35312 |    10M|   190   (3)| 00:00:02 |       |       |  Q1,13 | PCWP |            |  1185K|  1185K|          |
|  22 |         PX RECEIVE                               |                            |    183 |  3660 |     2   (0)| 00:00:01 |       |       |  Q1,13 | PCWP |            |       |       |          |
|  23 |          PX SEND BROADCAST                       | :TQ10010                   |    183 |  3660 |     2   (0)| 00:00:01 |       |       |  Q1,10 | P->P | BROADCAST  |       |       |          |
|  24 |           PX BLOCK ITERATOR                      |                            |    183 |  3660 |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,10 | PCWC |            |       |       |          |
|* 25 |            TABLE ACCESS FULL                     | RDS_CURRENCY               |    183 |  3660 |     2   (0)| 00:00:01 |     4 |     4 |  Q1,10 | PCWP |            |       |       |          |
|* 26 |         HASH JOIN                                |                            |  35312 |  9759K|   188   (3)| 00:00:02 |       |       |  Q1,13 | PCWP |            |   856K|   856K|          |
|  27 |          PX RECEIVE                              |                            |    851 |   187K|    16   (7)| 00:00:01 |       |       |  Q1,13 | PCWP |            |       |       |          |
|  28 |           PX SEND BROADCAST                      | :TQ10011                   |    851 |   187K|    16   (7)| 00:00:01 |       |       |  Q1,11 | P->P | BROADCAST  |       |       |          |
|* 29 |            HASH JOIN                             |                            |    851 |   187K|    16   (7)| 00:00:01 |       |       |  Q1,11 | PCWP |            |  1040K|  1040K|          |
|  30 |             PX RECEIVE                           |                            |    924 |   145K|    12   (9)| 00:00:01 |       |       |  Q1,11 | PCWP |            |       |       |          |
|  31 |              PX SEND HASH                        | :TQ10009                   |    924 |   145K|    12   (9)| 00:00:01 |       |       |  Q1,09 | P->P | HASH       |       |       |          |
|* 32 |               HASH JOIN BUFFERED                 |                            |    924 |   145K|    12   (9)| 00:00:01 |       |       |  Q1,09 | PCWP |            |  1234K|  1234K|          |
|  33 |                PX RECEIVE                        |                            |   1118 | 51428 |     2   (0)| 00:00:01 |       |       |  Q1,09 | PCWP |            |       |       |          |
|  34 |                 PX SEND HASH                     | :TQ10007                   |   1118 | 51428 |     2   (0)| 00:00:01 |       |       |  Q1,07 | P->P | HASH       |       |       |          |
|  35 |                  PX BLOCK ITERATOR               |                            |   1118 | 51428 |     2   (0)| 00:00:01 |       |       |  Q1,07 | PCWC |            |       |       |          |
|* 36 |                   TABLE ACCESS FULL              | CONTRACT                   |   1118 | 51428 |     2   (0)| 00:00:01 |       |       |  Q1,07 | PCWP |            |       |       |          |
|  37 |                PX RECEIVE                        |                            |   1632 |   183K|    10  (10)| 00:00:01 |       |       |  Q1,09 | PCWP |            |       |       |          |
|  38 |                 PX SEND HASH                     | :TQ10008                   |   1632 |   183K|    10  (10)| 00:00:01 |       |       |  Q1,08 | P->P | HASH       |       |       |          |
|* 39 |                  HASH JOIN BUFFERED              |                            |   1632 |   183K|    10  (10)| 00:00:01 |       |       |  Q1,08 | PCWP |            |  1063K|  1063K|          |
|  40 |                   PX RECEIVE                     |                            |    591 | 45507 |     6   (0)| 00:00:01 |       |       |  Q1,08 | PCWP |            |       |       |          |
|  41 |                    PX SEND HASH                  | :TQ10005                   |    591 | 45507 |     6   (0)| 00:00:01 |       |       |  Q1,05 | P->P | HASH       |       |       |          |
|* 42 |                     HASH JOIN BUFFERED           |                            |    591 | 45507 |     6   (0)| 00:00:01 |       |       |  Q1,05 | PCWP |            |  1313K|  1313K|          |
|  43 |                      PX RECEIVE                  |                            |    250 |  9750 |     4   (0)| 00:00:01 |       |       |  Q1,05 | PCWP |            |       |       |          |
|  44 |                       PX SEND HASH               | :TQ10003                   |    250 |  9750 |     4   (0)| 00:00:01 |       |       |  Q1,03 | P->P | HASH       |       |       |          |
|* 45 |                        HASH JOIN BUFFERED        |                            |    250 |  9750 |     4   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |  1730K|  1730K|          |
|  46 |                         PX RECEIVE               |                            |    250 |  4750 |     2   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |       |       |          |
|  47 |                          PX SEND HASH            | :TQ10001                   |    250 |  4750 |     2   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |       |       |          |
|  48 |                           PX BLOCK ITERATOR      |                            |    250 |  4750 |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,01 | PCWC |            |       |       |          |
|* 49 |                            TABLE ACCESS FULL     | RDS_COUNTRY                |    250 |  4750 |     2   (0)| 00:00:01 |     4 |     4 |  Q1,01 | PCWP |            |       |       |          |
|  50 |                         PX RECEIVE               |                            |    183 |  3660 |     2   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |       |       |          |
|  51 |                          PX SEND HASH            | :TQ10002                   |    183 |  3660 |     2   (0)| 00:00:01 |       |       |  Q1,02 | P->P | HASH       |       |       |          |
|  52 |                           PX BLOCK ITERATOR      |                            |    183 |  3660 |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,02 | PCWC |            |       |       |          |
|* 53 |                            TABLE ACCESS FULL     | RDS_CURRENCY               |    183 |  3660 |     2   (0)| 00:00:01 |     4 |     4 |  Q1,02 | PCWP |            |       |       |          |
|  54 |                      PX RECEIVE                  |                            |    591 | 22458 |     2   (0)| 00:00:01 |       |       |  Q1,05 | PCWP |            |       |       |          |
|  55 |                       PX SEND HASH               | :TQ10004                   |    591 | 22458 |     2   (0)| 00:00:01 |       |       |  Q1,04 | P->P | HASH       |       |       |          |
|  56 |                        PX BLOCK ITERATOR         |                            |    591 | 22458 |     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,04 | PCWC |            |       |       |          |
|* 57 |                         TABLE ACCESS FULL        | RDS_PROGRAM_PACKAGE_GROUP  |    591 | 22458 |     2   (0)| 00:00:01 |     4 |     4 |  Q1,04 | PCWP |            |       |       |          |
|  58 |                   PX RECEIVE                     |                            |   1955 | 74290 |     3   (0)| 00:00:01 |       |       |  Q1,08 | PCWP |            |       |       |          |
|  59 |                    PX SEND HASH                  | :TQ10006                   |   1955 | 74290 |     3   (0)| 00:00:01 |       |       |  Q1,06 | P->P | HASH       |       |       |          |
|  60 |                     PX BLOCK ITERATOR            |                            |   1955 | 74290 |     3   (0)| 00:00:01 |       |       |  Q1,06 | PCWC |            |       |       |          |
|* 61 |                      TABLE ACCESS FULL           | CONTRACT_PPG_MAP           |   1955 | 74290 |     3   (0)| 00:00:01 |       |       |  Q1,06 | PCWP |            |       |       |          |
|  62 |             BUFFER SORT                          |                            |        |       |            |          |       |       |  Q1,11 | PCWC |            |    28M|  1924K|          |
|  63 |              PX RECEIVE                          |                            |    235K|    14M|     4   (0)| 00:00:01 |       |       |  Q1,11 | PCWP |            |       |       |          |
|  64 |               PX SEND HASH                       | :TQ10000                   |    235K|    14M|     4   (0)| 00:00:01 |       |       |        | S->P | HASH       |       |       |          |
|  65 |                PARTITION LIST SINGLE             |                            |    235K|    14M|     4   (0)| 00:00:01 |   KEY |   KEY |        |      |            |       |       |          |
|  66 |                 TABLE ACCESS BY LOCAL INDEX ROWID| RDS_CLIENT_PROGRAM_PKG_GRP |    235K|    14M|     4   (0)| 00:00:01 |     4 |     4 |        |      |            |       |       |          |
|* 67 |                  INDEX RANGE SCAN                | I_CPP_IDX_1                |      1 |       |     3   (0)| 00:00:01 |     4 |     4 |        |      |            |       |       |          |
|  68 |          PX BLOCK ITERATOR                       |                            |  46810 |  2605K|   171   (2)| 00:00:01 |       |       |  Q1,13 | PCWC |            |       |       |          |
|* 69 |           TABLE ACCESS FULL                      | FX_CLIENT_MARGIN           |  46810 |  2605K|   171   (2)| 00:00:01 |       |       |  Q1,13 | PCWP |            |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$C772B8D1
   7 - SEL$683B0107 / VW_SQ_1@SEL$7511BFD2
   8 - SEL$683B0107
  12 - SEL$683B0107 / CPD@SEL$2
  13 - SEL$683B0107 / FCP@SEL$2
  14 - SEL$683B0107 / FCP@SEL$2
  15 - SEL$683B0107 / PCM@SEL$2
  16 - SEL$683B0107 / PCM@SEL$2
  18 - SEL$683B0107 / CPPG_1@SEL$2
  25 - SEL$C772B8D1 / RCO_2@SEL$1
  36 - SEL$C772B8D1 / CON@SEL$1
  49 - SEL$C772B8D1 / RCU@SEL$1
  53 - SEL$C772B8D1 / RCO_1@SEL$1
  57 - SEL$C772B8D1 / PPG@SEL$1
  61 - SEL$C772B8D1 / CPM@SEL$1
  66 - SEL$C772B8D1 / CPPG@SEL$1
  67 - SEL$C772B8D1 / CPPG@SEL$1
  69 - SEL$C772B8D1 / FCM@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('optimizer_dynamic_sampling' 6)
      ALL_ROWS
      SHARED(6)
      OUTLINE_LEAF(@"SEL$683B0107")
      OUTLINE_LEAF(@"SEL$C772B8D1")
      UNNEST(@"SEL$2")
      OUTLINE_LEAF(@"INS$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$7511BFD2")
      OUTLINE(@"SEL$1")
      FULL(@"INS$1" "RDS_CLIENT_FX_MARGIN"@"INS$1")
      FULL(@"SEL$C772B8D1" "RCU"@"SEL$1")
      FULL(@"SEL$C772B8D1" "RCO_1"@"SEL$1")
      FULL(@"SEL$C772B8D1" "PPG"@"SEL$1")
      FULL(@"SEL$C772B8D1" "CPM"@"SEL$1")
      FULL(@"SEL$C772B8D1" "CON"@"SEL$1")
      INDEX_RS_ASC(@"SEL$C772B8D1" "CPPG"@"SEL$1" ("RDS_CLIENT_PROGRAM_PKG_GRP"."REFERENCE_DATA_CYCLE_DATE" "RDS_CLIENT_PROGRAM_PKG_GRP"."CLIENT_PROGRAM_PKG_GRP_ID"))
      FULL(@"SEL$C772B8D1" "FCM"@"SEL$1")
      FULL(@"SEL$C772B8D1" "RCO_2"@"SEL$1")
      NO_ACCESS(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2")
      LEADING(@"SEL$C772B8D1" "RCU"@"SEL$1" "RCO_1"@"SEL$1" "PPG"@"SEL$1" "CPM"@"SEL$1" "CON"@"SEL$1" "CPPG"@"SEL$1" "FCM"@"SEL$1" "RCO_2"@"SEL$1" "VW_SQ_1"@"SEL$7511BFD2")
      USE_HASH(@"SEL$C772B8D1" "RCO_1"@"SEL$1")
      USE_HASH(@"SEL$C772B8D1" "PPG"@"SEL$1")
      USE_HASH(@"SEL$C772B8D1" "CPM"@"SEL$1")
      USE_HASH(@"SEL$C772B8D1" "CON"@"SEL$1")
      USE_HASH(@"SEL$C772B8D1" "CPPG"@"SEL$1")
      USE_HASH(@"SEL$C772B8D1" "FCM"@"SEL$1")
      USE_HASH(@"SEL$C772B8D1" "RCO_2"@"SEL$1")
      USE_HASH(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2")
      PQ_DISTRIBUTE(@"SEL$C772B8D1" "RCO_1"@"SEL$1" HASH HASH)
      PQ_DISTRIBUTE(@"SEL$C772B8D1" "PPG"@"SEL$1" HASH HASH)
      PQ_DISTRIBUTE(@"SEL$C772B8D1" "CPM"@"SEL$1" HASH HASH)
      PQ_DISTRIBUTE(@"SEL$C772B8D1" "CON"@"SEL$1" HASH HASH)
      PQ_DISTRIBUTE(@"SEL$C772B8D1" "CPPG"@"SEL$1" HASH HASH)
      PQ_DISTRIBUTE(@"SEL$C772B8D1" "FCM"@"SEL$1" BROADCAST NONE)
      PQ_DISTRIBUTE(@"SEL$C772B8D1" "RCO_2"@"SEL$1" NONE BROADCAST)
      PQ_DISTRIBUTE(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2" HASH HASH)
      SWAP_JOIN_INPUTS(@"SEL$C772B8D1" "CON"@"SEL$1")
      SWAP_JOIN_INPUTS(@"SEL$C772B8D1" "RCO_2"@"SEL$1")
      SWAP_JOIN_INPUTS(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2")
      FULL(@"SEL$683B0107" "CPD"@"SEL$2")
      INDEX_RS_ASC(@"SEL$683B0107" "FCP"@"SEL$2" ("FX_CLIENT_PROMOTION"."FX_CLIENT_PROMOTION_ID" "FX_CLIENT_PROMOTION"."CHANGE_SEQUENCE_NUMBER"))
      INDEX_RS_ASC(@"SEL$683B0107" "PCM"@"SEL$2" ("FX_PROMOTION_CPPM_MAP"."FX_CLIENT_PROMOTION_ID" "FX_PROMOTION_CPPM_MAP"."CLIENT_PROGRAM_PKG_GRP_ID" 
              "FX_PROMOTION_CPPM_MAP"."CHANGE_SEQUENCE_NUMBER"))
      INDEX(@"SEL$683B0107" "CPPG_1"@"SEL$2" ("RDS_CLIENT_PROGRAM_PKG_GRP"."REFERENCE_DATA_CYCLE_DATE" "RDS_CLIENT_PROGRAM_PKG_GRP"."CLIENT_PROGRAM_PKG_GRP_ID"))
      LEADING(@"SEL$683B0107" "CPD"@"SEL$2" "FCP"@"SEL$2" "PCM"@"SEL$2" "CPPG_1"@"SEL$2")
      USE_NL(@"SEL$683B0107" "FCP"@"SEL$2")
      USE_NL(@"SEL$683B0107" "PCM"@"SEL$2")
      USE_NL(@"SEL$683B0107" "CPPG_1"@"SEL$2")
      PQ_DISTRIBUTE(@"SEL$683B0107" "FCP"@"SEL$2" NONE BROADCAST)
      PQ_DISTRIBUTE(@"SEL$683B0107" "PCM"@"SEL$2" NONE BROADCAST)
      PQ_DISTRIBUTE(@"SEL$683B0107" "CPPG_1"@"SEL$2" NONE BROADCAST)
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("CPPG"."CLIENT_PROGRAM_PKG_GRP_ID"="ITEM_1" AND "FCM"."QUOTE_CURRENCY_ID"="ITEM_2" AND "FCM"."CHANNEL_CODE"="ITEM_3" AND "FCM"."TRANSACTION_TYPE"="ITEM_4" AND 
              "FCM"."LOW_TIER"="ITEM_5" AND "FCM"."HIGH_TIER"="ITEM_6")
  12 - access(:Z>=:Z AND :Z<=:Z)
  13 - filter(("FCP"."ACTIVE_FLAG"=1 AND "FCP"."WORKFLOW_STATUS"=3 AND "FCP"."EFFECTIVE_START_DATE"<=TO_DATE(' 2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "FCP"."EFFECTIVE_END_DATE">TO_DATE(' 2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
  14 - access("FCP"."FX_CLIENT_PROMOTION_ID"="CPD"."FX_CLIENT_PROMOTION_ID")
  15 - filter(("PCM"."ACTIVE_FLAG"=1 AND "PCM"."WORKFLOW_STATUS"=3 AND "PCM"."LIVE_DATE"<=TO_DATE(' 2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PCM"."EFFECTIVE_START_DATE"<=TO_DATE(' 
              2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PCM"."EFFECTIVE_END_DATE">TO_DATE(' 2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
  16 - access("PCM"."FX_CLIENT_PROMOTION_ID"="FCP"."FX_CLIENT_PROMOTION_ID")
  18 - access("CPPG_1"."REFERENCE_DATA_CYCLE_DATE"=TO_DATE(' 2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PCM"."CLIENT_PROGRAM_PKG_GRP_ID"="CPPG_1"."CLIENT_PROGRAM_PKG_GRP_ID")
  21 - access("FCM"."QUOTE_CURRENCY_ID"="RCO_2"."CURRENCY_ID")
  25 - access(:Z>=:Z AND :Z<=:Z)
       filter("RCO_2"."REFERENCE_DATA_CYCLE_DATE"=TO_DATE(' 2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  26 - access("CON"."CONTRACT_ID"="FCM"."CONTRACT_ID")
  29 - access("CPPG"."PROGRAM_PACKAGE_GROUP_ID"="PPG"."PROGRAM_PACKAGE_GROUP_ID")
       filter((("CON"."CONTRACT_LEVEL"=1 AND "CON"."ASSOC_ORG_HIERARCHY_ID"="CPPG"."CLIENT_ORGANISATION_ID") OR ("CON"."CONTRACT_LEVEL"=2 AND 
              "CON"."ASSOC_ORG_HIERARCHY_ID"="CPPG"."CLIENT_REGIONAL_OFFICE_ID")))
  32 - access("CON"."PRODUCT_TYPE_ID"="PPG"."PRODUCT_TYPE_ID" AND "CON"."CONTRACT_ID"="CPM"."CONTRACT_ID")
  36 - access(:Z>=:Z AND :Z<=:Z)
       filter(("CON"."EFFECTIVE_END_DATE">TO_DATE(' 2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CON"."WORKFLOW_STATUS"=3 AND "CON"."LIVE_DATE"<=TO_DATE(' 2019-01-09 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss') AND "CON"."ACTIVE_FLAG"=1 AND "CON"."EFFECTIVE_START_DATE"<=TO_DATE(' 2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
  39 - access("CPM"."PROGRAM_PKG_GRP_ID"="PPG"."PROGRAM_PACKAGE_GROUP_ID")
  42 - access("RCU"."ISO_COUNTRY_CODE_2"="PPG"."ISO_BASE_COUNTRY_CODE")
  45 - access("RCU"."CURRENCY_ID"="RCO_1"."CURRENCY_ID")
  49 - access(:Z>=:Z AND :Z<=:Z)
       filter("RCU"."REFERENCE_DATA_CYCLE_DATE"=TO_DATE(' 2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  53 - access(:Z>=:Z AND :Z<=:Z)
       filter("RCO_1"."REFERENCE_DATA_CYCLE_DATE"=TO_DATE(' 2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  57 - access(:Z>=:Z AND :Z<=:Z)
       filter("PPG"."REFERENCE_DATA_CYCLE_DATE"=TO_DATE(' 2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  61 - access(:Z>=:Z AND :Z<=:Z)
       filter(("CPM"."EFFECTIVE_END_DATE">TO_DATE(' 2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CPM"."WORKFLOW_STATUS"=3 AND "CPM"."ACTIVE_FLAG"=1 AND "CPM"."LIVE_DATE"<=TO_DATE(' 
              2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CPM"."EFFECTIVE_START_DATE"<=TO_DATE(' 2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
  67 - access("CPPG"."REFERENCE_DATA_CYCLE_DATE"=TO_DATE(' 2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  69 - access(:Z>=:Z AND :Z<=:Z)
       filter(("FCM"."EFFECTIVE_END_DATE">TO_DATE(' 2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "FCM"."WORKFLOW_STATUS"=3 AND "FCM"."ACTIVE_FLAG"=1 AND "FCM"."LIVE_DATE"<=TO_DATE(' 
              2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "FCM"."EFFECTIVE_START_DATE"<=TO_DATE(' 2019-01-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[96], SYSDEF[32720]
   2 - "CPPG"."CLIENT_PROGRAM_PKG_GRP_ID"[NUMBER,22], "FCM"."QUOTE_CURRENCY_ID"[NUMBER,22], "FCM"."CHANNEL_CODE"[NUMBER,22], "FCM"."TRANSACTION_TYPE"[NUMBER,22], "FCM"."LOW_TIER"[NUMBER,22], 
       "FCM"."HIGH_TIER"[NUMBER,22], "RCO_2"."CURRENCY_ID"[NUMBER,22], "FCM"."ACTIVE_FLAG"[NUMBER,22], "RCO_2"."ISO_CURRENCY_CODE"[CHARACTER,3], "RCO_2"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], 
       "CON"."CONTRACT_ID"[NUMBER,22], "FCM"."CONTRACT_ID"[NUMBER,22], "PPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], "CPPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], "CON"."PRODUCT_TYPE_ID"[NUMBER,22], 
       "PPG"."PRODUCT_TYPE_ID"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_CASHOUT"[NUMBER,22], "CPM"."CONTRACT_ID"[NUMBER,22], "CON"."EFFECTIVE_START_DATE"[DATE,7], "CON"."EFFECTIVE_END_DATE"[DATE,7], 
       "CON"."LIVE_DATE"[DATE,7], "CON"."WORKFLOW_STATUS"[NUMBER,22], "CON"."ACTIVE_FLAG"[NUMBER,22], "CON"."CONTRACT_LEVEL"[NUMBER,22], "CON"."ASSOC_ORG_HIERARCHY_ID"[NUMBER,22], 
       "CPM"."EFFECTIVE_END_DATE"[DATE,7], "CPM"."PROGRAM_PKG_GRP_ID"[NUMBER,22], "RCU"."ISO_COUNTRY_CODE_2"[CHARACTER,2], "PPG"."ISO_BASE_COUNTRY_CODE"[CHARACTER,2], "RCU"."CURRENCY_ID"[NUMBER,22], 
       "RCO_1"."CURRENCY_ID"[NUMBER,22], "RCU"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."ISO_CURRENCY_CODE"[CHARACTER,3], 
       "CPM"."EFFECTIVE_START_DATE"[DATE,7], "PPG"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "PPG"."CP_PPG_ID"[VARCHAR2,8], "PPG"."ISO_CARD_CURRENCY_CODE"[CHARACTER,3], "PPG"."MCP_FLAG"[NUMBER,22], 
       "CPM"."WORKFLOW_STATUS"[NUMBER,22], "CPM"."ACTIVE_FLAG"[NUMBER,22], "CPM"."LIVE_DATE"[DATE,7], "CPPG".ROWID[ROWID,10], "FCM"."EFFECTIVE_START_DATE"[DATE,7], 
       "CPPG"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "CPPG"."FX_RATE_SERVER_FREQ_PURSETRANS"[NUMBER,22], "CPPG"."CP_ORG_ID"[VARCHAR2,5], "CPPG"."CP_ORG_REG_OFFICE_ID"[VARCHAR2,5], 
       "CPPG"."CP_ORG_BRANCH_OFFICE_ID"[VARCHAR2,5], "CPPG"."CLIENT_REGIONAL_OFFICE_ID"[NUMBER,22], "CPPG"."CLIENT_ORGANISATION_ID"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_LOAD"[NUMBER,22], 
       "CPPG"."FX_RATE_SERVER_FREQ_RELOAD"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_UNLOAD"[NUMBER,22], "FCM"."LIVE_DATE"[DATE,7], "FCM"."EFFECTIVE_END_DATE"[DATE,7], 
       "FCM"."WORKFLOW_STATUS"[NUMBER,22], "FCM"."FX_CLIENT_MARGIN_PCT"[NUMBER,22]
   3 - (#keys=0) "CPPG"."CLIENT_PROGRAM_PKG_GRP_ID"[NUMBER,22], "FCM"."QUOTE_CURRENCY_ID"[NUMBER,22], "FCM"."CHANNEL_CODE"[NUMBER,22], "FCM"."TRANSACTION_TYPE"[NUMBER,22], 
       "FCM"."LOW_TIER"[NUMBER,22], "FCM"."HIGH_TIER"[NUMBER,22], "RCO_2"."CURRENCY_ID"[NUMBER,22], "FCM"."ACTIVE_FLAG"[NUMBER,22], "RCO_2"."ISO_CURRENCY_CODE"[CHARACTER,3], 
       "RCO_2"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "CON"."CONTRACT_ID"[NUMBER,22], "FCM"."CONTRACT_ID"[NUMBER,22], "PPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], 
       "CPPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], "CON"."PRODUCT_TYPE_ID"[NUMBER,22], "PPG"."PRODUCT_TYPE_ID"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_CASHOUT"[NUMBER,22], 
       "CPM"."CONTRACT_ID"[NUMBER,22], "CON"."EFFECTIVE_START_DATE"[DATE,7], "CON"."EFFECTIVE_END_DATE"[DATE,7], "CON"."LIVE_DATE"[DATE,7], "CON"."WORKFLOW_STATUS"[NUMBER,22], 
       "CON"."ACTIVE_FLAG"[NUMBER,22], "CON"."CONTRACT_LEVEL"[NUMBER,22], "CON"."ASSOC_ORG_HIERARCHY_ID"[NUMBER,22], "CPM"."EFFECTIVE_END_DATE"[DATE,7], "CPM"."PROGRAM_PKG_GRP_ID"[NUMBER,22], 
       "RCU"."ISO_COUNTRY_CODE_2"[CHARACTER,2], "PPG"."ISO_BASE_COUNTRY_CODE"[CHARACTER,2], "RCU"."CURRENCY_ID"[NUMBER,22], "RCO_1"."CURRENCY_ID"[NUMBER,22], 
       "RCU"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."ISO_CURRENCY_CODE"[CHARACTER,3], "CPM"."EFFECTIVE_START_DATE"[DATE,7], 
       "PPG"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "PPG"."CP_PPG_ID"[VARCHAR2,8], "PPG"."ISO_CARD_CURRENCY_CODE"[CHARACTER,3], "PPG"."MCP_FLAG"[NUMBER,22], "CPM"."WORKFLOW_STATUS"[NUMBER,22], 
       "CPM"."ACTIVE_FLAG"[NUMBER,22], "CPM"."LIVE_DATE"[DATE,7], "CPPG".ROWID[ROWID,10], "FCM"."EFFECTIVE_START_DATE"[DATE,7], "CPPG"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], 
       "CPPG"."FX_RATE_SERVER_FREQ_PURSETRANS"[NUMBER,22], "CPPG"."CP_ORG_ID"[VARCHAR2,5], "CPPG"."CP_ORG_REG_OFFICE_ID"[VARCHAR2,5], "CPPG"."CP_ORG_BRANCH_OFFICE_ID"[VARCHAR2,5], 
       "CPPG"."CLIENT_REGIONAL_OFFICE_ID"[NUMBER,22], "CPPG"."CLIENT_ORGANISATION_ID"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_LOAD"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_RELOAD"[NUMBER,22], 
       "CPPG"."FX_RATE_SERVER_FREQ_UNLOAD"[NUMBER,22], "FCM"."LIVE_DATE"[DATE,7], "FCM"."EFFECTIVE_END_DATE"[DATE,7], "FCM"."WORKFLOW_STATUS"[NUMBER,22], "FCM"."FX_CLIENT_MARGIN_PCT"[NUMBER,22]
   4 - (#keys=6) "CPPG"."CLIENT_PROGRAM_PKG_GRP_ID"[NUMBER,22], "FCM"."QUOTE_CURRENCY_ID"[NUMBER,22], "FCM"."CHANNEL_CODE"[NUMBER,22], "FCM"."TRANSACTION_TYPE"[NUMBER,22], 
       "FCM"."LOW_TIER"[NUMBER,22], "FCM"."HIGH_TIER"[NUMBER,22], "RCO_2"."CURRENCY_ID"[NUMBER,22], "FCM"."ACTIVE_FLAG"[NUMBER,22], "RCO_2"."ISO_CURRENCY_CODE"[CHARACTER,3], 
       "RCO_2"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "CON"."CONTRACT_ID"[NUMBER,22], "FCM"."CONTRACT_ID"[NUMBER,22], "PPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], 
       "CPPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], "CON"."PRODUCT_TYPE_ID"[NUMBER,22], "PPG"."PRODUCT_TYPE_ID"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_CASHOUT"[NUMBER,22], 
       "CPM"."CONTRACT_ID"[NUMBER,22], "CON"."EFFECTIVE_START_DATE"[DATE,7], "CON"."EFFECTIVE_END_DATE"[DATE,7], "CON"."LIVE_DATE"[DATE,7], "CON"."WORKFLOW_STATUS"[NUMBER,22], 
       "CON"."ACTIVE_FLAG"[NUMBER,22], "CON"."CONTRACT_LEVEL"[NUMBER,22], "CON"."ASSOC_ORG_HIERARCHY_ID"[NUMBER,22], "CPM"."EFFECTIVE_END_DATE"[DATE,7], "CPM"."PROGRAM_PKG_GRP_ID"[NUMBER,22], 
       "RCU"."ISO_COUNTRY_CODE_2"[CHARACTER,2], "PPG"."ISO_BASE_COUNTRY_CODE"[CHARACTER,2], "RCU"."CURRENCY_ID"[NUMBER,22], "RCO_1"."CURRENCY_ID"[NUMBER,22], 
       "RCU"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."ISO_CURRENCY_CODE"[CHARACTER,3], "CPM"."EFFECTIVE_START_DATE"[DATE,7], 
       "PPG"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "PPG"."CP_PPG_ID"[VARCHAR2,8], "PPG"."ISO_CARD_CURRENCY_CODE"[CHARACTER,3], "PPG"."MCP_FLAG"[NUMBER,22], "CPM"."WORKFLOW_STATUS"[NUMBER,22], 
       "CPM"."ACTIVE_FLAG"[NUMBER,22], "CPM"."LIVE_DATE"[DATE,7], "CPPG".ROWID[ROWID,10], "FCM"."EFFECTIVE_START_DATE"[DATE,7], "CPPG"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], 
       "CPPG"."FX_RATE_SERVER_FREQ_PURSETRANS"[NUMBER,22], "CPPG"."CP_ORG_ID"[VARCHAR2,5], "CPPG"."CP_ORG_REG_OFFICE_ID"[VARCHAR2,5], "CPPG"."CP_ORG_BRANCH_OFFICE_ID"[VARCHAR2,5], 
       "CPPG"."CLIENT_REGIONAL_OFFICE_ID"[NUMBER,22], "CPPG"."CLIENT_ORGANISATION_ID"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_LOAD"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_RELOAD"[NUMBER,22], 
       "CPPG"."FX_RATE_SERVER_FREQ_UNLOAD"[NUMBER,22], "FCM"."LIVE_DATE"[DATE,7], "FCM"."EFFECTIVE_END_DATE"[DATE,7], "FCM"."WORKFLOW_STATUS"[NUMBER,22], "FCM"."FX_CLIENT_MARGIN_PCT"[NUMBER,22]
   5 - "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22], "ITEM_3"[NUMBER,22], "ITEM_4"[NUMBER,22], "ITEM_5"[NUMBER,22], "ITEM_6"[NUMBER,22]
   6 - (#keys=6) "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22], "ITEM_3"[NUMBER,22], "ITEM_4"[NUMBER,22], "ITEM_5"[NUMBER,22], "ITEM_6"[NUMBER,22]
   7 - "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22], "ITEM_3"[NUMBER,22], "ITEM_4"[NUMBER,22], "ITEM_5"[NUMBER,22], "ITEM_6"[NUMBER,22]
   8 - "CPD"."FX_CLIENT_PROMOTION_ID"[NUMBER,22], "CPD"."CHANNEL_CODE"[NUMBER,22], "CPD"."TRANSACTION_TYPE"[NUMBER,22], "CPD"."LOW_TIER"[NUMBER,22], "CPD"."HIGH_TIER"[NUMBER,22], 
       "FCP".ROWID[ROWID,10], "FCP"."FX_CLIENT_PROMOTION_ID"[NUMBER,22], "FCP"."QUOTE_CURRENCY_ID"[NUMBER,22], "FCP"."ACTIVE_FLAG"[NUMBER,22], "FCP"."WORKFLOW_STATUS"[NUMBER,22], 
       "FCP"."EFFECTIVE_START_DATE"[DATE,7], "FCP"."EFFECTIVE_END_DATE"[DATE,7], "PCM".ROWID[ROWID,10], "PCM"."WORKFLOW_STATUS"[NUMBER,22], "PCM"."ACTIVE_FLAG"[NUMBER,22], "PCM"."LIVE_DATE"[DATE,7], 
       "PCM"."FX_CLIENT_PROMOTION_ID"[NUMBER,22], "PCM"."CLIENT_PROGRAM_PKG_GRP_ID"[NUMBER,22], "PCM"."EFFECTIVE_START_DATE"[DATE,7], "PCM"."EFFECTIVE_END_DATE"[DATE,7], "CPPG_1".ROWID[ROWID,10], 
       "CPPG_1"."CLIENT_PROGRAM_PKG_GRP_ID"[NUMBER,22], "CPPG_1"."REFERENCE_DATA_CYCLE_DATE"[DATE,7]
   9 - "CPD"."FX_CLIENT_PROMOTION_ID"[NUMBER,22], "CPD"."CHANNEL_CODE"[NUMBER,22], "CPD"."TRANSACTION_TYPE"[NUMBER,22], "CPD"."LOW_TIER"[NUMBER,22], "CPD"."HIGH_TIER"[NUMBER,22], 
       "FCP".ROWID[ROWID,10], "FCP"."FX_CLIENT_PROMOTION_ID"[NUMBER,22], "FCP"."QUOTE_CURRENCY_ID"[NUMBER,22], "FCP"."ACTIVE_FLAG"[NUMBER,22], "FCP"."WORKFLOW_STATUS"[NUMBER,22], 
       "FCP"."EFFECTIVE_START_DATE"[DATE,7], "FCP"."EFFECTIVE_END_DATE"[DATE,7], "PCM".ROWID[ROWID,10], "PCM"."WORKFLOW_STATUS"[NUMBER,22], "PCM"."ACTIVE_FLAG"[NUMBER,22], "PCM"."LIVE_DATE"[DATE,7], 
       "PCM"."FX_CLIENT_PROMOTION_ID"[NUMBER,22], "PCM"."CLIENT_PROGRAM_PKG_GRP_ID"[NUMBER,22], "PCM"."EFFECTIVE_START_DATE"[DATE,7], "PCM"."EFFECTIVE_END_DATE"[DATE,7]
  10 - "CPD"."FX_CLIENT_PROMOTION_ID"[NUMBER,22], "CPD"."CHANNEL_CODE"[NUMBER,22], "CPD"."TRANSACTION_TYPE"[NUMBER,22], "CPD"."LOW_TIER"[NUMBER,22], "CPD"."HIGH_TIER"[NUMBER,22], 
       "FCP".ROWID[ROWID,10], "FCP"."FX_CLIENT_PROMOTION_ID"[NUMBER,22], "FCP"."QUOTE_CURRENCY_ID"[NUMBER,22], "FCP"."ACTIVE_FLAG"[NUMBER,22], "FCP"."WORKFLOW_STATUS"[NUMBER,22], 
       "FCP"."EFFECTIVE_START_DATE"[DATE,7], "FCP"."EFFECTIVE_END_DATE"[DATE,7]
  11 - "CPD"."FX_CLIENT_PROMOTION_ID"[NUMBER,22], "CPD"."CHANNEL_CODE"[NUMBER,22], "CPD"."TRANSACTION_TYPE"[NUMBER,22], "CPD"."LOW_TIER"[NUMBER,22], "CPD"."HIGH_TIER"[NUMBER,22]
  12 - "CPD"."FX_CLIENT_PROMOTION_ID"[NUMBER,22], "CPD"."CHANNEL_CODE"[NUMBER,22], "CPD"."TRANSACTION_TYPE"[NUMBER,22], "CPD"."LOW_TIER"[NUMBER,22], "CPD"."HIGH_TIER"[NUMBER,22]
  13 - "FCP".ROWID[ROWID,10], "FCP"."FX_CLIENT_PROMOTION_ID"[NUMBER,22], "FCP"."QUOTE_CURRENCY_ID"[NUMBER,22], "FCP"."ACTIVE_FLAG"[NUMBER,22], "FCP"."WORKFLOW_STATUS"[NUMBER,22], 
       "FCP"."EFFECTIVE_START_DATE"[DATE,7], "FCP"."EFFECTIVE_END_DATE"[DATE,7]
  14 - "FCP".ROWID[ROWID,10], "FCP"."FX_CLIENT_PROMOTION_ID"[NUMBER,22]
  15 - "PCM".ROWID[ROWID,10], "PCM"."WORKFLOW_STATUS"[NUMBER,22], "PCM"."ACTIVE_FLAG"[NUMBER,22], "PCM"."LIVE_DATE"[DATE,7], "PCM"."FX_CLIENT_PROMOTION_ID"[NUMBER,22], 
       "PCM"."CLIENT_PROGRAM_PKG_GRP_ID"[NUMBER,22], "PCM"."EFFECTIVE_START_DATE"[DATE,7], "PCM"."EFFECTIVE_END_DATE"[DATE,7]
  16 - "PCM".ROWID[ROWID,10], "PCM"."FX_CLIENT_PROMOTION_ID"[NUMBER,22], "PCM"."CLIENT_PROGRAM_PKG_GRP_ID"[NUMBER,22]
  17 - "CPPG_1".ROWID[ROWID,10], "CPPG_1"."CLIENT_PROGRAM_PKG_GRP_ID"[NUMBER,22], "CPPG_1"."REFERENCE_DATA_CYCLE_DATE"[DATE,7]
  18 - "CPPG_1".ROWID[ROWID,10], "CPPG_1"."CLIENT_PROGRAM_PKG_GRP_ID"[NUMBER,22], "CPPG_1"."REFERENCE_DATA_CYCLE_DATE"[DATE,7]
  19 - "CPPG"."CLIENT_PROGRAM_PKG_GRP_ID"[NUMBER,22], "FCM"."QUOTE_CURRENCY_ID"[NUMBER,22], "FCM"."CHANNEL_CODE"[NUMBER,22], "FCM"."TRANSACTION_TYPE"[NUMBER,22], "FCM"."LOW_TIER"[NUMBER,22], 
       "FCM"."HIGH_TIER"[NUMBER,22], "RCO_2"."CURRENCY_ID"[NUMBER,22], "FCM"."ACTIVE_FLAG"[NUMBER,22], "RCO_2"."ISO_CURRENCY_CODE"[CHARACTER,3], "RCO_2"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], 
       "CON"."CONTRACT_ID"[NUMBER,22], "FCM"."CONTRACT_ID"[NUMBER,22], "PPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], "CPPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], "CON"."PRODUCT_TYPE_ID"[NUMBER,22], 
       "PPG"."PRODUCT_TYPE_ID"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_CASHOUT"[NUMBER,22], "CPM"."CONTRACT_ID"[NUMBER,22], "CON"."EFFECTIVE_START_DATE"[DATE,7], "CON"."EFFECTIVE_END_DATE"[DATE,7], 
       "CON"."LIVE_DATE"[DATE,7], "CON"."WORKFLOW_STATUS"[NUMBER,22], "CON"."ACTIVE_FLAG"[NUMBER,22], "CON"."CONTRACT_LEVEL"[NUMBER,22], "CON"."ASSOC_ORG_HIERARCHY_ID"[NUMBER,22], 
       "CPM"."EFFECTIVE_END_DATE"[DATE,7], "CPM"."PROGRAM_PKG_GRP_ID"[NUMBER,22], "RCU"."ISO_COUNTRY_CODE_2"[CHARACTER,2], "PPG"."ISO_BASE_COUNTRY_CODE"[CHARACTER,2], "RCU"."CURRENCY_ID"[NUMBER,22], 
       "RCO_1"."CURRENCY_ID"[NUMBER,22], "RCU"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."ISO_CURRENCY_CODE"[CHARACTER,3], 
       "CPM"."EFFECTIVE_START_DATE"[DATE,7], "PPG"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "PPG"."CP_PPG_ID"[VARCHAR2,8], "PPG"."ISO_CARD_CURRENCY_CODE"[CHARACTER,3], "PPG"."MCP_FLAG"[NUMBER,22], 
       "CPM"."WORKFLOW_STATUS"[NUMBER,22], "CPM"."ACTIVE_FLAG"[NUMBER,22], "CPM"."LIVE_DATE"[DATE,7], "CPPG".ROWID[ROWID,10], "FCM"."EFFECTIVE_START_DATE"[DATE,7], 
       "CPPG"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "CPPG"."FX_RATE_SERVER_FREQ_PURSETRANS"[NUMBER,22], "CPPG"."CP_ORG_ID"[VARCHAR2,5], "CPPG"."CP_ORG_REG_OFFICE_ID"[VARCHAR2,5], 
       "CPPG"."CP_ORG_BRANCH_OFFICE_ID"[VARCHAR2,5], "CPPG"."CLIENT_REGIONAL_OFFICE_ID"[NUMBER,22], "CPPG"."CLIENT_ORGANISATION_ID"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_LOAD"[NUMBER,22], 
       "CPPG"."FX_RATE_SERVER_FREQ_RELOAD"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_UNLOAD"[NUMBER,22], "FCM"."LIVE_DATE"[DATE,7], "FCM"."EFFECTIVE_END_DATE"[DATE,7], 
       "FCM"."WORKFLOW_STATUS"[NUMBER,22], "FCM"."FX_CLIENT_MARGIN_PCT"[NUMBER,22]
  20 - (#keys=6) "CPPG"."CLIENT_PROGRAM_PKG_GRP_ID"[NUMBER,22], "FCM"."QUOTE_CURRENCY_ID"[NUMBER,22], "FCM"."CHANNEL_CODE"[NUMBER,22], "FCM"."TRANSACTION_TYPE"[NUMBER,22], 
       "FCM"."LOW_TIER"[NUMBER,22], "FCM"."HIGH_TIER"[NUMBER,22], "RCO_2"."CURRENCY_ID"[NUMBER,22], "FCM"."ACTIVE_FLAG"[NUMBER,22], "RCO_2"."ISO_CURRENCY_CODE"[CHARACTER,3], 
       "RCO_2"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "CON"."CONTRACT_ID"[NUMBER,22], "FCM"."CONTRACT_ID"[NUMBER,22], "PPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], 
       "CPPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], "CON"."PRODUCT_TYPE_ID"[NUMBER,22], "PPG"."PRODUCT_TYPE_ID"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_CASHOUT"[NUMBER,22], 
       "CPM"."CONTRACT_ID"[NUMBER,22], "CON"."EFFECTIVE_START_DATE"[DATE,7], "CON"."EFFECTIVE_END_DATE"[DATE,7], "CON"."LIVE_DATE"[DATE,7], "CON"."WORKFLOW_STATUS"[NUMBER,22], 
       "CON"."ACTIVE_FLAG"[NUMBER,22], "CON"."CONTRACT_LEVEL"[NUMBER,22], "CON"."ASSOC_ORG_HIERARCHY_ID"[NUMBER,22], "CPM"."EFFECTIVE_END_DATE"[DATE,7], "CPM"."PROGRAM_PKG_GRP_ID"[NUMBER,22], 
       "RCU"."ISO_COUNTRY_CODE_2"[CHARACTER,2], "PPG"."ISO_BASE_COUNTRY_CODE"[CHARACTER,2], "RCU"."CURRENCY_ID"[NUMBER,22], "RCO_1"."CURRENCY_ID"[NUMBER,22], 
       "RCU"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."ISO_CURRENCY_CODE"[CHARACTER,3], "CPM"."EFFECTIVE_START_DATE"[DATE,7], 
       "PPG"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "PPG"."CP_PPG_ID"[VARCHAR2,8], "PPG"."ISO_CARD_CURRENCY_CODE"[CHARACTER,3], "PPG"."MCP_FLAG"[NUMBER,22], "CPM"."WORKFLOW_STATUS"[NUMBER,22], 
       "CPM"."ACTIVE_FLAG"[NUMBER,22], "CPM"."LIVE_DATE"[DATE,7], "CPPG".ROWID[ROWID,10], "FCM"."EFFECTIVE_START_DATE"[DATE,7], "CPPG"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], 
       "CPPG"."FX_RATE_SERVER_FREQ_PURSETRANS"[NUMBER,22], "CPPG"."CP_ORG_ID"[VARCHAR2,5], "CPPG"."CP_ORG_REG_OFFICE_ID"[VARCHAR2,5], "CPPG"."CP_ORG_BRANCH_OFFICE_ID"[VARCHAR2,5], 
       "CPPG"."CLIENT_REGIONAL_OFFICE_ID"[NUMBER,22], "CPPG"."CLIENT_ORGANISATION_ID"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_LOAD"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_RELOAD"[NUMBER,22], 
       "CPPG"."FX_RATE_SERVER_FREQ_UNLOAD"[NUMBER,22], "FCM"."LIVE_DATE"[DATE,7], "FCM"."EFFECTIVE_END_DATE"[DATE,7], "FCM"."WORKFLOW_STATUS"[NUMBER,22], "FCM"."FX_CLIENT_MARGIN_PCT"[NUMBER,22]
  21 - (#keys=1) "RCO_2"."CURRENCY_ID"[NUMBER,22], "FCM"."QUOTE_CURRENCY_ID"[NUMBER,22], "RCO_2"."ISO_CURRENCY_CODE"[CHARACTER,3], "RCO_2"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], 
       "CON"."CONTRACT_ID"[NUMBER,22], "FCM"."CONTRACT_ID"[NUMBER,22], "PPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], "CPPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], "CON"."PRODUCT_TYPE_ID"[NUMBER,22], 
       "PPG"."PRODUCT_TYPE_ID"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_CASHOUT"[NUMBER,22], "CPM"."CONTRACT_ID"[NUMBER,22], "CON"."EFFECTIVE_START_DATE"[DATE,7], "CON"."EFFECTIVE_END_DATE"[DATE,7], 
       "CON"."LIVE_DATE"[DATE,7], "CON"."WORKFLOW_STATUS"[NUMBER,22], "CON"."ACTIVE_FLAG"[NUMBER,22], "CON"."CONTRACT_LEVEL"[NUMBER,22], "CON"."ASSOC_ORG_HIERARCHY_ID"[NUMBER,22], 
       "CPM"."EFFECTIVE_END_DATE"[DATE,7], "CPM"."PROGRAM_PKG_GRP_ID"[NUMBER,22], "RCU"."ISO_COUNTRY_CODE_2"[CHARACTER,2], "PPG"."ISO_BASE_COUNTRY_CODE"[CHARACTER,2], "RCU"."CURRENCY_ID"[NUMBER,22], 
       "RCO_1"."CURRENCY_ID"[NUMBER,22], "RCU"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."ISO_CURRENCY_CODE"[CHARACTER,3], 
       "CPM"."EFFECTIVE_START_DATE"[DATE,7], "PPG"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "PPG"."CP_PPG_ID"[VARCHAR2,8], "PPG"."ISO_CARD_CURRENCY_CODE"[CHARACTER,3], "PPG"."MCP_FLAG"[NUMBER,22], 
       "CPM"."WORKFLOW_STATUS"[NUMBER,22], "CPM"."ACTIVE_FLAG"[NUMBER,22], "CPM"."LIVE_DATE"[DATE,7], "CPPG".ROWID[ROWID,10], "CPPG"."CLIENT_PROGRAM_PKG_GRP_ID"[NUMBER,22], 
       "CPPG"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "CPPG"."FX_RATE_SERVER_FREQ_PURSETRANS"[NUMBER,22], "CPPG"."CP_ORG_ID"[VARCHAR2,5], "CPPG"."CP_ORG_REG_OFFICE_ID"[VARCHAR2,5], 
       "CPPG"."CP_ORG_BRANCH_OFFICE_ID"[VARCHAR2,5], "CPPG"."CLIENT_REGIONAL_OFFICE_ID"[NUMBER,22], "CPPG"."CLIENT_ORGANISATION_ID"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_LOAD"[NUMBER,22], 
       "CPPG"."FX_RATE_SERVER_FREQ_RELOAD"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_UNLOAD"[NUMBER,22], "FCM"."LIVE_DATE"[DATE,7], "FCM"."EFFECTIVE_END_DATE"[DATE,7], "FCM"."CHANNEL_CODE"[NUMBER,22], 
       "FCM"."TRANSACTION_TYPE"[NUMBER,22], "FCM"."LOW_TIER"[NUMBER,22], "FCM"."HIGH_TIER"[NUMBER,22], "FCM"."FX_CLIENT_MARGIN_PCT"[NUMBER,22], "FCM"."WORKFLOW_STATUS"[NUMBER,22], 
       "FCM"."ACTIVE_FLAG"[NUMBER,22], "FCM"."EFFECTIVE_START_DATE"[DATE,7]
  22 - "RCO_2"."CURRENCY_ID"[NUMBER,22], "RCO_2"."ISO_CURRENCY_CODE"[CHARACTER,3], "RCO_2"."REFERENCE_DATA_CYCLE_DATE"[DATE,7]
  23 - (#keys=0) "RCO_2"."CURRENCY_ID"[NUMBER,22], "RCO_2"."ISO_CURRENCY_CODE"[CHARACTER,3], "RCO_2"."REFERENCE_DATA_CYCLE_DATE"[DATE,7]
  24 - "RCO_2"."CURRENCY_ID"[NUMBER,22], "RCO_2"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_2"."ISO_CURRENCY_CODE"[CHARACTER,3]
  25 - "RCO_2"."CURRENCY_ID"[NUMBER,22], "RCO_2"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_2"."ISO_CURRENCY_CODE"[CHARACTER,3]
  26 - (#keys=1) "CON"."CONTRACT_ID"[NUMBER,22], "FCM"."CONTRACT_ID"[NUMBER,22], "PPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], "CPPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], 
       "CON"."PRODUCT_TYPE_ID"[NUMBER,22], "PPG"."PRODUCT_TYPE_ID"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_CASHOUT"[NUMBER,22], "CPM"."CONTRACT_ID"[NUMBER,22], "CON"."EFFECTIVE_START_DATE"[DATE,7], 
       "CON"."EFFECTIVE_END_DATE"[DATE,7], "CON"."LIVE_DATE"[DATE,7], "CON"."WORKFLOW_STATUS"[NUMBER,22], "CON"."ACTIVE_FLAG"[NUMBER,22], "CON"."CONTRACT_LEVEL"[NUMBER,22], 
       "CON"."ASSOC_ORG_HIERARCHY_ID"[NUMBER,22], "CPM"."EFFECTIVE_END_DATE"[DATE,7], "CPM"."PROGRAM_PKG_GRP_ID"[NUMBER,22], "RCU"."ISO_COUNTRY_CODE_2"[CHARACTER,2], 
       "PPG"."ISO_BASE_COUNTRY_CODE"[CHARACTER,2], "RCU"."CURRENCY_ID"[NUMBER,22], "RCO_1"."CURRENCY_ID"[NUMBER,22], "RCU"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], 
       "RCO_1"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."ISO_CURRENCY_CODE"[CHARACTER,3], "CPM"."EFFECTIVE_START_DATE"[DATE,7], "PPG"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], 
       "PPG"."CP_PPG_ID"[VARCHAR2,8], "PPG"."ISO_CARD_CURRENCY_CODE"[CHARACTER,3], "PPG"."MCP_FLAG"[NUMBER,22], "CPM"."WORKFLOW_STATUS"[NUMBER,22], "CPM"."ACTIVE_FLAG"[NUMBER,22], 
       "CPM"."LIVE_DATE"[DATE,7], "CPPG".ROWID[ROWID,10], "CPPG"."CLIENT_PROGRAM_PKG_GRP_ID"[NUMBER,22], "CPPG"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], 
       "CPPG"."FX_RATE_SERVER_FREQ_PURSETRANS"[NUMBER,22], "CPPG"."CP_ORG_ID"[VARCHAR2,5], "CPPG"."CP_ORG_REG_OFFICE_ID"[VARCHAR2,5], "CPPG"."CP_ORG_BRANCH_OFFICE_ID"[VARCHAR2,5], 
       "CPPG"."CLIENT_REGIONAL_OFFICE_ID"[NUMBER,22], "CPPG"."CLIENT_ORGANISATION_ID"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_LOAD"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_RELOAD"[NUMBER,22], 
       "CPPG"."FX_RATE_SERVER_FREQ_UNLOAD"[NUMBER,22], "FCM"."LIVE_DATE"[DATE,7], "FCM"."QUOTE_CURRENCY_ID"[NUMBER,22], "FCM"."CHANNEL_CODE"[NUMBER,22], "FCM"."TRANSACTION_TYPE"[NUMBER,22], 
       "FCM"."LOW_TIER"[NUMBER,22], "FCM"."HIGH_TIER"[NUMBER,22], "FCM"."FX_CLIENT_MARGIN_PCT"[NUMBER,22], "FCM"."WORKFLOW_STATUS"[NUMBER,22], "FCM"."ACTIVE_FLAG"[NUMBER,22], 
       "FCM"."EFFECTIVE_START_DATE"[DATE,7], "FCM"."EFFECTIVE_END_DATE"[DATE,7]
  27 - "CON"."CONTRACT_ID"[NUMBER,22], "PPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], "CPPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], "CON"."PRODUCT_TYPE_ID"[NUMBER,22], 
       "PPG"."PRODUCT_TYPE_ID"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_CASHOUT"[NUMBER,22], "CPM"."CONTRACT_ID"[NUMBER,22], "CON"."EFFECTIVE_START_DATE"[DATE,7], "CON"."EFFECTIVE_END_DATE"[DATE,7], 
       "CON"."LIVE_DATE"[DATE,7], "CON"."WORKFLOW_STATUS"[NUMBER,22], "CON"."ACTIVE_FLAG"[NUMBER,22], "CON"."CONTRACT_LEVEL"[NUMBER,22], "CON"."ASSOC_ORG_HIERARCHY_ID"[NUMBER,22], 
       "CPM"."EFFECTIVE_END_DATE"[DATE,7], "CPM"."PROGRAM_PKG_GRP_ID"[NUMBER,22], "RCU"."ISO_COUNTRY_CODE_2"[CHARACTER,2], "PPG"."ISO_BASE_COUNTRY_CODE"[CHARACTER,2], "RCU"."CURRENCY_ID"[NUMBER,22], 
       "RCO_1"."CURRENCY_ID"[NUMBER,22], "RCU"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."ISO_CURRENCY_CODE"[CHARACTER,3], 
       "CPM"."EFFECTIVE_START_DATE"[DATE,7], "PPG"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "PPG"."CP_PPG_ID"[VARCHAR2,8], "PPG"."ISO_CARD_CURRENCY_CODE"[CHARACTER,3], "PPG"."MCP_FLAG"[NUMBER,22], 
       "CPM"."WORKFLOW_STATUS"[NUMBER,22], "CPM"."ACTIVE_FLAG"[NUMBER,22], "CPM"."LIVE_DATE"[DATE,7], "CPPG".ROWID[ROWID,10], "CPPG"."CLIENT_PROGRAM_PKG_GRP_ID"[NUMBER,22], 
       "CPPG"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "CPPG"."FX_RATE_SERVER_FREQ_PURSETRANS"[NUMBER,22], "CPPG"."CP_ORG_ID"[VARCHAR2,5], "CPPG"."CP_ORG_REG_OFFICE_ID"[VARCHAR2,5], 
       "CPPG"."CP_ORG_BRANCH_OFFICE_ID"[VARCHAR2,5], "CPPG"."CLIENT_REGIONAL_OFFICE_ID"[NUMBER,22], "CPPG"."CLIENT_ORGANISATION_ID"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_LOAD"[NUMBER,22], 
       "CPPG"."FX_RATE_SERVER_FREQ_RELOAD"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_UNLOAD"[NUMBER,22]
  28 - (#keys=0) "CON"."CONTRACT_ID"[NUMBER,22], "PPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], "CPPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], "CON"."PRODUCT_TYPE_ID"[NUMBER,22], 
       "PPG"."PRODUCT_TYPE_ID"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_CASHOUT"[NUMBER,22], "CPM"."CONTRACT_ID"[NUMBER,22], "CON"."EFFECTIVE_START_DATE"[DATE,7], "CON"."EFFECTIVE_END_DATE"[DATE,7], 
       "CON"."LIVE_DATE"[DATE,7], "CON"."WORKFLOW_STATUS"[NUMBER,22], "CON"."ACTIVE_FLAG"[NUMBER,22], "CON"."CONTRACT_LEVEL"[NUMBER,22], "CON"."ASSOC_ORG_HIERARCHY_ID"[NUMBER,22], 
       "CPM"."EFFECTIVE_END_DATE"[DATE,7], "CPM"."PROGRAM_PKG_GRP_ID"[NUMBER,22], "RCU"."ISO_COUNTRY_CODE_2"[CHARACTER,2], "PPG"."ISO_BASE_COUNTRY_CODE"[CHARACTER,2], "RCU"."CURRENCY_ID"[NUMBER,22], 
       "RCO_1"."CURRENCY_ID"[NUMBER,22], "RCU"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."ISO_CURRENCY_CODE"[CHARACTER,3], 
       "CPM"."EFFECTIVE_START_DATE"[DATE,7], "PPG"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "PPG"."CP_PPG_ID"[VARCHAR2,8], "PPG"."ISO_CARD_CURRENCY_CODE"[CHARACTER,3], "PPG"."MCP_FLAG"[NUMBER,22], 
       "CPM"."WORKFLOW_STATUS"[NUMBER,22], "CPM"."ACTIVE_FLAG"[NUMBER,22], "CPM"."LIVE_DATE"[DATE,7], "CPPG".ROWID[ROWID,10], "CPPG"."CLIENT_PROGRAM_PKG_GRP_ID"[NUMBER,22], 
       "CPPG"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "CPPG"."FX_RATE_SERVER_FREQ_PURSETRANS"[NUMBER,22], "CPPG"."CP_ORG_ID"[VARCHAR2,5], "CPPG"."CP_ORG_REG_OFFICE_ID"[VARCHAR2,5], 
       "CPPG"."CP_ORG_BRANCH_OFFICE_ID"[VARCHAR2,5], "CPPG"."CLIENT_REGIONAL_OFFICE_ID"[NUMBER,22], "CPPG"."CLIENT_ORGANISATION_ID"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_LOAD"[NUMBER,22], 
       "CPPG"."FX_RATE_SERVER_FREQ_RELOAD"[NUMBER,22], "CPPG"."FX_RATE_SERVER_FREQ_UNLOAD"[NUMBER,22]
  29 - (#keys=1) "PPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], "CPPG"."PROGRAM_PACKAGE_GROUP_ID"[NUMBER,22], "CON"."PRODUCT_TYPE_ID"[NUMBER,22], "PPG"."PRODUCT_TYPE_ID"[NUMBER,22], 
       "CON"."CONTRACT_ID"[NUMBER,22], "CPM"."CONTRACT_ID"[NUMBER,22], "CON"."EFFECTIVE_START_DATE"[DATE,7], "CON"."EFFECTIVE_END_DATE"[DATE,7], "CON"."LIVE_DATE"[DATE,7], 
       "CON"."WORKFLOW_STATUS"[NUMBER,22], "CON"."ACTIVE_FLAG"[NUMBER,22], "CON"."CONTRACT_LEVEL"[NUMBER,22], "CON"."ASSOC_ORG_HIERARCHY_ID"[NUMBER,22], "CPM"."EFFECTIVE_END_DATE"[DATE,7], 
       "CPM"."PROGRAM_PKG_GRP_ID"[NUMBER,22], "RCU"."ISO_COUNTRY_CODE_2"[CHARACTER,2], "PPG"."ISO_BASE_COUNTRY_CODE"[CHARACTER,2], "RCU"."CURRENCY_ID"[NUMBER,22], "RCO_1"."CURRENCY_ID"[NUMBER,22], 
       "RCU"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."REFERENCE_DATA_CYCLE_DATE"[DATE,7], "RCO_1"."ISO_CURRENCY_CODE"[CHARACTER,3], "CPM"."EFFE

Note
-----
   - dynamic sampling used for this statement (level=6)
   - Degree of Parallelism is 6 because of hint
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

[Updated on: Tue, 08 January 2019 11:22]

Report message to a moderator

Re: Insert Statement taking more than 2 hrs for 4 million records [message #674172 is a reply to message #674171] Tue, 08 January 2019 11:34 Go to previous messageGo to next message
John Watson
Messages: 7811
Registered: January 2010
Location: Global Village
Senior Member
This is getting boring. You have not enabled parallel DML. How many times have I said that? Look at the plan: the parallel to serial conversion occurs at operation 3, before the insert at operation 1. You do not enable parallel DML for a table, you enable it for your session. This is basic stuff. If your DBA does not understand it, you should send him for re-education.
Re: Insert Statement taking more than 2 hrs for 4 million records [message #674252 is a reply to message #674172] Mon, 14 January 2019 03:41 Go to previous message
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
Senior Member

Please monitor the session and check whether insert is going in parallel session?
If it's not going in for parallel session, try using hint /*+ DYNAMIC_SAMPLING(0)*/ in INSERT statement and test it.


Run below query to check whether dynamic sampling is enabled in both non PROD and PROD DB

select * from v$parameter where name like '%dynamic%';

Thanks,
Anil MK
Previous Topic: Determining a due date based on business hours
Next Topic: Spreadsheet-like Totals and Subtotals
Goto Forum:
  


Current Time: Wed Mar 20 10:21:38 CDT 2019