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 |
srinivas.k2005
Messages: 404 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 #673949 is a reply to message #673927] |
Wed, 19 December 2018 03:39 |
srinivas.k2005
Messages: 404 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=""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"" 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>=:Z AND :Z<=: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="("FCP"."ACTIVE_FLAG"=:B3 AND "FCP"."WORKFLOW_STATUS"=:B2 AND "FCP"."EFFECTIVE_START_DATE"<=:B1 AND "FCP"."EFFECTIVE_END_DATE">: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=""FCP"."FX_CLIENT_PROMOTION_ID"="CPD"."FX_CLIENT_PROMOTION_ID""/>
</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="("PCM"."ACTIVE_FLAG"=:B3 AND "PCM"."WORKFLOW_STATUS"=:B2 AND "PCM"."LIVE_DATE"<=:B1 AND "PCM"."EFFECTIVE_START_DATE"<=:B1 AND "PCM"."EFFECTIVE_END_DATE">: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=""PCM"."FX_CLIENT_PROMOTION_ID"="FCP"."FX_CLIENT_PROMOTION_ID""/>
</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=""CPPG_1"."REFERENCE_DATA_CYCLE_DATE"=:B1 AND "PCM"."CLIENT_PROGRAM_PKG_GRP_ID"="CPPG_1"."CLIENT_PROGRAM_PKG_GRP_ID""/>
</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=""FCM"."QUOTE_CURRENCY_ID"="RCO_2"."CURRENCY_ID"" 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>=:Z AND :Z<=:Z" filter_predicates=""RCO_2"."REFERENCE_DATA_CYCLE_DATE"=: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=""CON"."CONTRACT_ID"="FCM"."CONTRACT_ID"" 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=""CPPG"."PROGRAM_PACKAGE_GROUP_ID"="PPG"."PROGRAM_PACKAGE_GROUP_ID"" filter_predicates="(("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"))" 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=""CON"."PRODUCT_TYPE_ID"="PPG"."PRODUCT_TYPE_ID" AND "CON"."CONTRACT_ID"="CPM"."CONTRACT_ID"" 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>=:Z AND :Z<=:Z" filter_predicates="("CON"."EFFECTIVE_END_DATE">:B1 AND "CON"."WORKFLOW_STATUS"=:B2 AND "CON"."LIVE_DATE"<=:B1 AND "CON"."ACTIVE_FLAG"=:B3 AND "CON"."EFFECTIVE_START_DATE"<=: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=""CPM"."PROGRAM_PKG_GRP_ID"="PPG"."PROGRAM_PACKAGE_GROUP_ID"" 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=""RCU"."ISO_COUNTRY_CODE_2"="PPG"."ISO_BASE_COUNTRY_CODE"" 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=""RCU"."CURRENCY_ID"="RCO_1"."CURRENCY_ID"" 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>=:Z AND :Z<=:Z" filter_predicates=""RCU"."REFERENCE_DATA_CYCLE_DATE"=: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>=:Z AND :Z<=:Z" filter_predicates=""RCO_1"."REFERENCE_DATA_CYCLE_DATE"=: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>=:Z AND :Z<=:Z" filter_predicates=""PPG"."REFERENCE_DATA_CYCLE_DATE"=: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>=:Z AND :Z<=:Z" filter_predicates="("CPM"."EFFECTIVE_END_DATE">:B1 AND "CPM"."WORKFLOW_STATUS"=:B2 AND "CPM"."ACTIVE_FLAG"=:B3 AND "CPM"."LIVE_DATE"<=:B1 AND "CPM"."EFFECTIVE_START_DATE"<=: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=""CPPG"."REFERENCE_DATA_CYCLE_DATE"=: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>=:Z AND :Z<=:Z" filter_predicates="("FCM"."EFFECTIVE_END_DATE">:B1 AND "FCM"."WORKFLOW_STATUS"=:B2 AND "FCM"."ACTIVE_FLAG"=:B3 AND "FCM"."LIVE_DATE"<=:B1 AND "FCM"."EFFECTIVE_START_DATE"<=: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 1777 times)
|
|
|
|
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 |
John Watson
Messages: 8951 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 #674087 is a reply to message #673972] |
Thu, 03 January 2019 03:43 |
srinivas.k2005
Messages: 404 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
|
|
|
|
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 |
srinivas.k2005
Messages: 404 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 |
cookiemonster
Messages: 13943 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 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
John Watson wrote on Thu, 03 January 2019 10:20This 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 |
srinivas.k2005
Messages: 404 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 #674117 is a reply to message #674114] |
Fri, 04 January 2019 06:14 |
srinivas.k2005
Messages: 404 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 #674119 is a reply to message #674118] |
Fri, 04 January 2019 06:25 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
srinivas.k2005 wrote on Fri, 04 January 2019 12:18Sorry 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 #674171 is a reply to message #674120] |
Tue, 08 January 2019 11:19 |
srinivas.k2005
Messages: 404 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 |
John Watson
Messages: 8951 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 |
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
|
|
|
Goto Forum:
Current Time: Sat Sep 28 16:11:15 CDT 2024
|