Hi all,
I am responsible for an application monitoring of message transfers.
So, my most important table is a table called MSG_EXC_REC which contains records of all messages transferred between countries.
Here is the structure of this table:
CREATE TABLE MSG_EXC_REC
(
CCN_UNIQUE_ID VARCHAR2(250) NOT NULL,
MSG_ID VARCHAR2(48) NOT NULL,
MSG_TYPE VARCHAR2(32) NOT NULL,
MSG_SIZE NUMBER(15),
SENDER VARCHAR2(2) NOT NULL,
RECIPIENT VARCHAR2(2) NOT NULL,
CORREL_ID VARCHAR2(48),
DOMAIN VARCHAR2(20) NOT NULL,
MODIFICATION_DATE TIMESTAMP NOT NULL,
M_MIN_DATE TIMESTAMP NOT NULL,
M_MAX_DATE TIMESTAMP NOT NULL,
MSG_SEND_DATE TIMESTAMP,
MSG_REC_DATE TIMESTAMP,
PRIMARY KEY (CCN_UNIQUE_ID)
)
PARTITION BY RANGE (M_MIN_DATE)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION BEFORE_2010 VALUES LESS THAN (TO_DATE('01-01-2010', 'DD-MM-YYYY')))
ENABLE ROW MOVEMENT INITRANS 5;
To correlate the sender and receiver of messages, we employ the approach that the correl_id of the received message is the msg_id of a sent message.
At times, there are failures in sending or receiving messages. When the message type (msg_type) contains 906, 907 or 917, it indicates an error occurrence. These error messages are also stored in a table named ERROR_MESSAGE. The connection between the ERROR_MESSAGE table and MSG_EXC_REC relies on the MSG_ID, DOMAIN and MSG_TYPE fields.
CREATE TABLE "ERROR_MESSAGE" (
"ID" NUMBER(12,0) NOT NULL,
"CORREL_ID" VARCHAR2(48 BYTE),
"MSG_ID" VARCHAR2(48 BYTE) NOT NULL,
"MSG_REC_DATE" TIMESTAMP (6) NOT NULL,
"MSG_TYPE" VARCHAR2(32 BYTE) NOT NULL,
"SYNC_ID" NUMBER(12,0),
"DOMAIN" VARCHAR2(20 BYTE) NOT NULL,
"MSG_SIZE" NUMBER(10,0),
"XML_CONTENT" CLOB,
PRIMARY KEY ("ID")
);
We utilize 3 additional tables, namely ERROR_MESSAGE_906, ERROR_MESSAGE_907, and ERROR_MESSAGE_917, to gather specific information about errors based on the msg_type field. Below are the definitions for each of these tables:
CREATE TABLE "ERROR_MESSAGE_906" (
"ID" NUMBER(12,0) NOT NULL,
"ERROR_TYPE" VARCHAR2(2 BYTE) NOT NULL,
"ORIGINAL_VALUE" VARCHAR2(140 BYTE),
"POINTER" VARCHAR2(210 BYTE),
"REASON" VARCHAR2(6 BYTE),
"EM_ID" NUMBER(12,0) NOT NULL,
PRIMARY KEY ("ID"),
CONSTRAINT "ERRORMESSAGE906EMID" FOREIGN KEY ("EM_ID")
REFERENCES "ERROR_MESSAGE" ("ID")
);
CREATE TABLE "ERROR_MESSAGE_907" (
"ID" NUMBER(12,0) NOT NULL,
"ACTREC_IN" VARCHAR2(3 BYTE),
"ERROR_CODE_IN" VARCHAR2(2 BYTE),
"MSG_TYPE" VARCHAR2(6 BYTE),
"ACTREC_MSG" VARCHAR2(3 BYTE),
"ERROR_CODE_MSG" VARCHAR2(2 BYTE),
"EM_ID" NUMBER(12,0) NOT NULL,
PRIMARY KEY ("ID"),
CONSTRAINT "ERRORMESSAGE907EMID" FOREIGN KEY ("EM_ID")
REFERENCES "ERROR_MESSAGE" ("ID")
);
CREATE TABLE "ERROR_MESSAGE_917" (
"ID" NUMBER(12,0) NOT NULL,
"LOCATION" VARCHAR2(350 BYTE),
"LINE" VARCHAR2(9 BYTE),
"COLUMN_NUM" VARCHAR2(9 BYTE),
"REASON" VARCHAR2(350 BYTE),
"ORIGINAL_VALUE" VARCHAR2(350 BYTE),
"ERROR_CODE" VARCHAR2(2 BYTE),
"EM_ID" NUMBER(12,0) NOT NULL,
PRIMARY KEY ("ID"),
CONSTRAINT "ERRORMESSAGE917EMID" FOREIGN KEY ("EM_ID")
REFERENCES "ERROR_MESSAGE" ("ID")
);
In the initial stages of the system, we operated under the assumption that the msg_id was unique for every message. However, it seems that this may have been altered last year.
I encountered an issue with a materialized view we utilize. This materialized view is tasked with retrieving all error_messages, joining them with msg_exc_rec, and, if applicable, joining them again with msg_exc_rec to get some info for the original message. (We have also implemented some date limitations to enhance performance.)
CREATE MATERIALIZED VIEW "MV_ERROR_MESSAGES"
REFRESH COMPLETE ON DEMAND
AS SELECT TMPUNION.ID AS EM_ID
, replace(TMPUNION.DOMAIN, 'AES', 'ECS') AS DOMAIN
, TMPUNION.PHASE
, SUBSTR(TMPUNION.MSG_TYPE, 0, 6) AS MSG_TYPE
, TMPUNION.SIZE_CATEGORY
, TMPUNION.IS_ZIP
, TMPUNION.MSG_REC_DATE
, SUBSTR(TMPUNION.SEND_C_CODE, -least(length(TMPUNION.SEND_C_CODE), 2)) AS SEND_C_CODE
, SUBSTR(TMPUNION.REC_C_CODE, -least(length(TMPUNION.REC_C_CODE), 2)) AS REC_C_CODE
, MSG.CCN_UNIQUE_ID
, TMPUNION.ERROR_TYPE
, TMPUNION.ERROR_REASON
, TMPUNION.ERROR_POINTER
, MSG.ENV
, MSG.REJECTED_MESSAGE_TYPE
, MSG.REJECTED_MESSAGE_SENDER
, MSG.REJECTED_MESSAGE_RECIPIENT
, MSG.REJECTED_ID
, CASE
WHEN TMPUNION.MSG_TYPE LIKE 'CD9%' AND MSG.RECIPIENT_GW IN ('XXIP1','XXIP2','XXIP4','XXIP5','XXIP7','XXIP9','XXIB1','XXIB2','XXIB5','XXIB7') THEN 'EX'
WHEN TMPUNION.ERROR_REASON LIKE 'NCAv%' THEN 'EX'
WHEN TMPUNION.MSG_TYPE LIKE 'CD917%' AND MSG.SENDER_GW IN ('XXIP1','XXIP2','XXIP4','XXIP5','XXIP7','XXIP9','XXIB1','XXIB2','XXIB5','XXIB7') THEN 'ET' --Endogen ToBe
WHEN TMPUNION.ERROR_REASON = 'ieCAvB' THEN 'ET'
WHEN TMPUNION.MSG_TYPE LIKE 'CD907%' AND MSG.SENDER_GW IN ('XXIP1','XXIP2','XXIP4','XXIP5','XXIP7','XXIP9','XXIB1','XXIB2','XXIB5','XXIB7') THEN 'EL' --Endogen Legacy
WHEN TMPUNION.ERROR_REASON = 'ieCAvC' THEN 'EL'
ELSE 'IF' --No conversion
END AS IECA_STATUS
, TMPUNION.ERROR_ID AS ERROR_ID
, MSG.IS_IECA
FROM
(
SELECT MDI.ID, MDI.DOMAIN, MDI.PHASE, MDI.MSG_TYPE, MDI.SIZE_CATEGORY, MDI.IS_ZIP, MDI.MSG_REC_DATE, MDI.SEND_C_CODE, MDI.REC_C_CODE, MDI.MSG_ID, MD906.ERROR_TYPE AS ERROR_TYPE, MD906.REASON AS ERROR_REASON, MD906.POINTER AS ERROR_POINTER, MD906.ID AS ERROR_ID
FROM ERROR_MESSAGE MDI
INNER JOIN ERROR_MESSAGE_906 MD906 ON MDI.ID = MD906.EM_ID
UNION ALL
SELECT MDI.ID, MDI.DOMAIN, MDI.PHASE, MDI.MSG_TYPE, MDI.SIZE_CATEGORY, MDI.IS_ZIP, MDI.MSG_REC_DATE, MDI.SEND_C_CODE, MDI.REC_C_CODE, MDI.MSG_ID, MD907.ERROR_CODE_IN AS ERROR_TYPE, NULL AS ERROR_REASON, NULL AS ERROR_POINTER, MD907.ID AS ERROR_ID
FROM ERROR_MESSAGE MDI
INNER JOIN ERROR_MESSAGE_907 MD907 ON MDI.ID = MD907.EM_ID
UNION ALL
SELECT MDI.ID, MDI.DOMAIN, MDI.PHASE, MDI.MSG_TYPE,MDI.SIZE_CATEGORY, MDI.IS_ZIP, MDI.MSG_REC_DATE, MDI.SEND_C_CODE, MDI.REC_C_CODE, MDI.MSG_ID, MD917.ERROR_CODE AS ERROR_TYPE, MD917.REASON AS ERROR_REASON, NULL AS ERROR_POINTER, MD917.ID AS ERROR_ID
FROM ERROR_MESSAGE MDI
INNER JOIN ERROR_MESSAGE_917 MD917 ON MDI.ID = MD917.EM_ID
) TMPUNION
cross apply (
select CCN_UNIQUE_ID, ENV, REJECTED_ID, RECIPIENT_GW, SENDER_GW, IS_IECA
, REJECTED_MESSAGE_TYPE, REJECTED_MESSAGE_SENDER, REJECTED_MESSAGE_RECIPIENT
, ROW_NUM
from (
select MER.CCN_UNIQUE_ID, MER.ENV, MER.CORREL_ID as REJECTED_ID, MER.RECIPIENT_GW, MER.SENDER_GW, MER.IS_IECA
, SUBSTR(ORIGINAL_MER.MSG_TYPE, 0, 6) AS REJECTED_MESSAGE_TYPE, ORIGINAL_MER.SENDER AS REJECTED_MESSAGE_SENDER
, ORIGINAL_MER.RECIPIENT AS REJECTED_MESSAGE_RECIPIENT
, ROW_NUMBER()
OVER (partition by MER.DOMAIN, MER.ENV, MER.MSG_TYPE, MER.MSG_ID order by MER.M_MIN_DATE desc) as ROW_NUM
from MSG_EXC_REC MER
LEFT JOIN MSG_EXC_REC ORIGINAL_MER ON MER.CORREL_ID = ORIGINAL_MER.MSG_ID
AND MER.DOMAIN = ORIGINAL_MER.DOMAIN
AND MER.M_MIN_DATE >= ORIGINAL_MER.M_MIN_DATE
where TMPUNION.MSG_ID = MER.MSG_ID
AND replace(TMPUNION.DOMAIN, 'AES', 'ECS') = MER.DOMAIN
AND TMPUNION.MSG_TYPE = MER.MSG_TYPE
AND TMPUNION.MSG_REC_DATE BETWEEN MER.M_MIN_DATE - interval '1' hour
AND MER.M_MIN_DATE + interval '5' hour
AND MER.M_MIN_DATE BETWEEN MER.M_MIN_DATE - interval '4' year
AND SYSDATE
)
where ROW_NUM=1
) MSG;
As you can see I use the ROW_NUM = 1, as I want to join with exactly one message. This added dut to the issue with the multiple msg_ids for different messages.
The materialized view used to refresh in about 2:30 hours. However, recently, it has been taking nearly 7 hours. Upon investigation, I discovered that there are error_messages with the same msg_id. I found one msg_id that appears 100,000 times.
The execution plan is below
Do you have any ideas on how to enhance the definition of this materialized view? Is the cross apply operation I'm using too resource-intensive?
Alternatively, is there a method to exclude msg_ids from error_message that have more than 2000 rows?
Thank you in advance for any advice you can provide. Your input is greatly appreciated.