Hi,
I tried to remove quotation marks from OWB package (tried to rewrite it to readable code) using replace command (CTRL+R).
It takes very long time, about 30s for whole insert command, regardless if I am connected to database or not.
In previous version (12.5.0.99) it takes about 1s.
Replace " to nothing.
INSERT
/*+ APPEND PARALLEL(STG_ACCOUNT_COLLATERALS_X, DEFAULT, DEFAULT) */
INTO
“DWH_OWNER”.“STG_ACCOUNT_COLLATERALS_X”
(“ACCOLT_KEY”,
“ACC_KEY”,
“ACCTP_KEY”,
“COLT_KEY”,
“COLT_SOURCE_SYSTEM_ID”,
“COLTTP_KEY”,
“CURR_KEY”,
“ACCOLT_SOURCE_SYSTEM_ID”,
“ACCOLT_SOURCE_ID”,
“ACCOLT_DISCOUNTED_VALUE_FC”,
“ACCOLT_NOMINAL_VALUE_FC”,
“ACCOLT_UTILISED_VALUE_FC”,
“ACCOLT_LOAN_OBJECT_FLAG”,
“ACCOLT_ACKNOWLEDGED_FLAG”,
“ACCOLT_LIEN_SEQUENCE”,
“ACCOLT_SRC_VALID_FROM”,
“ACCOLT_SRC_VALID_TO”,
“ACCOLT_UPDATED_EFFECTIVE_DATE”,
“ACCOLT_INSERTED_DATETIME”,
“ACCOLT_UPDATED_DATETIME”,
“ACCOLT_INSERT_PROCESS_KEY”,
“ACCOLT_UPDATE_PROCESS_KEY”,
“ACCOLT_DELETED_FLAG”,
“ACCOLT_VALID_FROM”,
“ACCOLT_VALID_TO”)
(SELECT
(“LOOKUP_INPUT_SUBQUERY”.“ACCOLT_KEY$5”) “ACCOLT_KEY$4”,
(“LOOKUP_INPUT_SUBQUERY”.“ACC_KEY$5”) “ACC_KEY$4”,
(“LOOKUP_INPUT_SUBQUERY”.“ACCTP_KEY$5”) “ACCTP_KEY$4”,
(“LOOKUP_INPUT_SUBQUERY”.“COLT_KEY$5”) “COLT_KEY$4”,
(“LOOKUP_INPUT_SUBQUERY”.“COLT_SOURCE_SYSTEM_ID$5”) “COLT_SOURCE_SYSTEM_ID$4”,
(“LOOKUP_INPUT_SUBQUERY”.“COLTTP_KEY$5”) “COLTTP_KEY$4”,
(“LOOKUP_INPUT_SUBQUERY”.“CURR_KEY$5”) “CURR_KEY$4”,
(“LOOKUP_INPUT_SUBQUERY”.“ACLTH_SOURCE_SYSTEM_ID$5”) “ACLTH_SOURCE_SYSTEM_ID$4”,
(“LOOKUP_INPUT_SUBQUERY”.“ACLTH_SOURCE_ID$5”) “ACLTH_SOURCE_ID$4”,
(“LOOKUP_INPUT_SUBQUERY”.“ACLTH_DISCOUNTED_VALUE_FC$5”) “ACLTH_DISCOUNTED_VALUE_FC$4”,
(“LOOKUP_INPUT_SUBQUERY”.“ACLTH_NOMINAL_VALUE_FC$5”) “ACLTH_NOMINAL_VALUE_FC$4”,
(“LOOKUP_INPUT_SUBQUERY”.“ACLTH_UTILISED_VALUE_FC$5”) “ACLTH_UTILISED_VALUE_FC$4”,
(“LOOKUP_INPUT_SUBQUERY”.“ACLTH_LOAN_OBJECT_FLAG$5”) “ACLTH_LOAN_OBJECT_FLAG$4”,
(“LOOKUP_INPUT_SUBQUERY”.“ACLTH_ACKNOWLEDGED_FLAG$5”) “ACLTH_ACKNOWLEDGED_FLAG$4”,
(“LOOKUP_INPUT_SUBQUERY”.“ACLTH_LIEN_SEQUENCE$5”) “ACLTH_LIEN_SEQUENCE$4”,
(“LOOKUP_INPUT_SUBQUERY”.“ACLTH_SRC_VALID_FROM$5”) “ACLTH_SRC_VALID_FROM$4”,
(“LOOKUP_INPUT_SUBQUERY”.“ACLTH_SRC_VALID_TO$5”) “ACLTH_SRC_VALID_TO$4”,
MS_DWH_OUTEBV_STG_ACCCLTRL_M4A.“CONST_ACCCLTRL_0_DATE_DEF” “DATE_DEF”,
(“LOOKUP_INPUT_SUBQUERY”.“ACLTH_INSERTED_DATETIME$5”) “ACLTH_INSERTED_DATETIME$4”,
(“LOOKUP_INPUT_SUBQUERY”.“ACLTH_UPDATED_DATETIME$5”) “ACLTH_UPDATED_DATETIME$4”,
(“LOOKUP_INPUT_SUBQUERY”.“ACLTH_INSERT_PROCESS_KEY$5”) “ACLTH_INSERT_PROCESS_KEY$4”,
(“LOOKUP_INPUT_SUBQUERY”.“ACLTH_UPDATE_PROCESS_KEY$5”) “ACLTH_UPDATE_PROCESS_KEY$4”,
(“LOOKUP_INPUT_SUBQUERY”.“ACLTH_DELETED_FLAG$5”) “ACLTH_DELETED_FLAG$4”,
(“LOOKUP_INPUT_SUBQUERY”.“ACLTH_VALID_FROM$5”) “ACLTH_VALID_FROM$4”,
(“LOOKUP_INPUT_SUBQUERY”.“ACLTH_VALID_TO$5”) “ACLTH_VALID_TO$4”
FROM (SELECT
“DEDUP”.“ACCTP_KEY$6” “ACCTP_KEY$5”,
“DEDUP”.“ACCOLT_KEY$6” “ACCOLT_KEY$5”,
“DEDUP”.“ACLTH_SEQ$4” “ACLTH_SEQ$3”,
“DEDUP”.“COLT_KEY$6” “COLT_KEY$5”,
“DEDUP”.“COLT_SOURCE_SYSTEM_ID$6” “COLT_SOURCE_SYSTEM_ID$5”,
“DEDUP”.“ACC_KEY$6” “ACC_KEY$5”,
“DEDUP”.“COLTTP_KEY$6” “COLTTP_KEY$5”,
“DEDUP”.“CURR_KEY$6” “CURR_KEY$5”,
“DEDUP”.“ACLTH_SOURCE_SYSTEM_ID$6” “ACLTH_SOURCE_SYSTEM_ID$5”,
“DEDUP”.“ACLTH_SOURCE_ID$6” “ACLTH_SOURCE_ID$5”,
“DEDUP”.“ACLTH_DISCOUNTED_VALUE_FC$6” “ACLTH_DISCOUNTED_VALUE_FC$5”,
“DEDUP”.“ACLTH_NOMINAL_VALUE_FC$6” “ACLTH_NOMINAL_VALUE_FC$5”,
“DEDUP”.“ACLTH_UTILISED_VALUE_FC$6” “ACLTH_UTILISED_VALUE_FC$5”,
“DEDUP”.“ACLTH_SHORTENING_COEFFICIENT$4” “ACLTH_SHORTENING_COEFFICIENT$3”,
“DEDUP”.“ACLTH_LOAN_OBJECT_FLAG$6” “ACLTH_LOAN_OBJECT_FLAG$5”,
“DEDUP”.“ACLTH_LIEN_SEQUENCE$6” “ACLTH_LIEN_SEQUENCE$5”,
“DEDUP”.“ACLTH_SRC_VALID_FROM$6” “ACLTH_SRC_VALID_FROM$5”,
“DEDUP”.“ACLTH_SRC_VALID_TO$6” “ACLTH_SRC_VALID_TO$5”,
“DEDUP”.“ACLTH_CURRENT_FLAG$4” “ACLTH_CURRENT_FLAG$3”,
“DEDUP”.“ACLTH_VALID_FROM$6” “ACLTH_VALID_FROM$5”,
“DEDUP”.“ACLTH_VALID_TO$6” “ACLTH_VALID_TO$5”,
“DEDUP”.“ACLTH_INSERTED_DATETIME$6” “ACLTH_INSERTED_DATETIME$5”,
“DEDUP”.“ACLTH_UPDATED_DATETIME$6” “ACLTH_UPDATED_DATETIME$5”,
“DEDUP”.“ACLTH_INSERT_PROCESS_KEY$6” “ACLTH_INSERT_PROCESS_KEY$5”,
“DEDUP”.“ACLTH_UPDATE_PROCESS_KEY$6” “ACLTH_UPDATE_PROCESS_KEY$5”,
“DEDUP”.“ACLTH_ACKNOWLEDGED_FLAG$6” “ACLTH_ACKNOWLEDGED_FLAG$5”,
“DEDUP”.“ACLTH_DELETED_FLAG$6” “ACLTH_DELETED_FLAG$5”,
“DEDUP”.“P_EFFECTIVE_DATE$5” “P_EFFECTIVE_DATE$4”,
“DEDUP”.“P_PROCESS_KEY$5” “P_PROCESS_KEY$4”,
“DEDUP”.“RN$4” “RN$3”
FROM (SELECT
DISTINCT
“DWH_OWNER_ACCOUNT_CO15001463”.“ACCTP_KEY” “ACCTP_KEY$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACCOLT_KEY” “ACCOLT_KEY$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_SEQ” “ACLTH_SEQ$4”,
“DWH_OWNER_ACCOUNT_CO15001463”.“COLT_KEY” “COLT_KEY$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“COLT_SOURCE_SYSTEM_ID” “COLT_SOURCE_SYSTEM_ID$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACC_KEY” “ACC_KEY$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“COLTTP_KEY” “COLTTP_KEY$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“CURR_KEY” “CURR_KEY$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_SOURCE_SYSTEM_ID” “ACLTH_SOURCE_SYSTEM_ID$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_SOURCE_ID” “ACLTH_SOURCE_ID$6”,
NULL “ACLTH_DISCOUNTED_VALUE_CZK$1”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_DISCOUNTED_VALUE_FC” “ACLTH_DISCOUNTED_VALUE_FC$6”,
NULL “ACLTH_NOMINAL_VALUE_CZK$1”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_NOMINAL_VALUE_FC” “ACLTH_NOMINAL_VALUE_FC$6”,
NULL “ACLTH_UTILISED_VALUE_CZK$1”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_UTILISED_VALUE_FC” “ACLTH_UTILISED_VALUE_FC$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_SHORTENING_COEFFICIENT” “ACLTH_SHORTENING_COEFFICIENT$4”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_LOAN_OBJECT_FLAG” “ACLTH_LOAN_OBJECT_FLAG$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_LIEN_SEQUENCE” “ACLTH_LIEN_SEQUENCE$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_SRC_VALID_FROM” “ACLTH_SRC_VALID_FROM$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_SRC_VALID_TO” “ACLTH_SRC_VALID_TO$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_CURRENT_FLAG” “ACLTH_CURRENT_FLAG$4”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_VALID_FROM” “ACLTH_VALID_FROM$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_VALID_TO” “ACLTH_VALID_TO$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_INSERTED_DATETIME” “ACLTH_INSERTED_DATETIME$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_UPDATED_DATETIME” “ACLTH_UPDATED_DATETIME$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_INSERT_PROCESS_KEY” “ACLTH_INSERT_PROCESS_KEY$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_UPDATE_PROCESS_KEY” “ACLTH_UPDATE_PROCESS_KEY$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_ACKNOWLEDGED_FLAG” “ACLTH_ACKNOWLEDGED_FLAG$6”,
“DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_DELETED_FLAG” “ACLTH_DELETED_FLAG$6”,
(MS_DWH_OUTEBV_STG_ACCCLTRL_M4A.“P_EFFECTIVE_DATE”) “P_EFFECTIVE_DATE$5”,
(MS_DWH_OUTEBV_STG_ACCCLTRL_M4A.“P_PROCESS_KEY”) “P_PROCESS_KEY$5”,
(row_number() over(partition by “DWH_OWNER_ACCOUNT_CO15001463”.“ACCOLT_KEY” order by “DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_VALID_FROM” desc, “DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_VALID_TO” desc)) “RN$4”
FROM “DWH_OWNER”.“ACCOUNT_COLLATERAL_HISTORY” “DWH_OWNER_ACCOUNT_CO15001463” WHERE ( (MS_DWH_OUTEBV_STG_ACCCLTRL_M4A.“P_EFFECTIVE_DATE”) between “DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_VALID_FROM” AND “DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_VALID_TO” ) AND
( (MS_DWH_OUTEBV_STG_ACCCLTRL_M4A.“P_EFFECTIVE_DATE”) between “DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_SRC_VALID_FROM” AND “DWH_OWNER_ACCOUNT_CO15001463”.“ACLTH_SRC_VALID_TO” )) “DEDUP” ) “LOOKUP_INPUT_SUBQUERY” WHERE ( (“LOOKUP_INPUT_SUBQUERY”.“RN$3”) = 1 )
);