Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

suggestions for performance tuning in query

BufossFeb 6 2025 — edited Feb 6 2025

Hello everyone,
I have the following relationship between two tables.

The tables are partitioned by a date column on a monthly basis.

I need to generate a report with two sheets, but the execution time is significantly high.

Below, I have provided the two queries.

-- query 1
WITH concatenated_countries AS (
   SELECT 'AT,BE,BG,CY,CZ,DE,DK,EE,EL,ES,FI,FR,HR,HU,IE,IT,LT,LU,LV,MT,NL,PL,PT,RO,SE,SI,SK,XI' str 
   FROM dual
),
all_countries AS (
   SELECT regexp_substr(
            str,
            '[^,]+',
            1,
            level
          ) country
   FROM concatenated_countries
   CONNECT BY level <= length(str) - length(replace(str, ',')) + 1
) 
SELECT mrn,
      CASE WHEN sender = 'EL' THEN 'GR' ELSE sender END AS "MS_OFFICE_OF_EXPORT",
      CASE WHEN receiver = 'EL' THEN 'GR' ELSE receiver END AS "MS_OFFICE_OF_EXIT",
      state,
      to_char(min_date, 'yyyyMM') AS "YearMonth",
      pattern
FROM (
   SELECT msg.mrn,
          mov.sender,
          mov.receiver,
          mov.state,
          mov.min_date,
          listagg(REGEXP_REPLACE(msg_type, '-MSG.[A-Z]+', '') ||
                 '-' || msg.sender || '-' || msg.recipient || '-' ||
                 CASE WHEN msg.msg_size > 400 THEN 'over' ELSE 'under' END,
                 '_' ON OVERFLOW TRUNCATE '...') 
          WITHIN GROUP (ORDER BY m_min_date, msg_type ASC) AS pattern
   FROM movement mov
   INNER JOIN msg_exc_rec msg ON (
       mov.mrn = msg.mrn
       AND mov.env = msg.env
       AND mov.domain = msg.domain
   )
   INNER JOIN all_countries ac ON (1=1)
   WHERE mov.domain = 'ECS'
   AND mov.sender = ac.COUNTRY
   AND mov.receiver NOT IN (ac.COUNTRY, 'OL')
   AND msg.is_ieca = 0
   AND mov.MIN_DATE < TO_DATE('20250101 000000', 'yyyyMMdd HH24miss')
   AND mov.MIN_DATE >= TO_DATE('20240601 000000', 'yyyyMMdd HH24miss')
   AND MOV.MRN NOT IN (
       SELECT msg2.MRN
       FROM MSG_EXC_REC msg2
       WHERE msg.DOMAIN = msg2.DOMAIN
       AND msg.ENV = msg2.ENV
       AND msg.MRN = msg2.MRN
       AND (
           (msg2.MSG_TYPE IN ('CD524C-MSG.ECS', 'CD524B-MSG.ECS')
            AND msg2.SENDER = ac.COUNTRY)
           OR (msg2.MSG_TYPE IN ('CD510A-MSG.ECS', 'CD510C-MSG.ECS')
               AND msg2.SENDER = ac.COUNTRY)
           OR (msg2.MSG_TYPE IN ('CD518C-MSG.ECS', 'CD518B-MSG.ECS')
               AND msg2.RECIPIENT = ac.COUNTRY)
           OR (msg2.MSG_TYPE IN ('CD901B-MSG.ECS')
               AND msg2.RECIPIENT = ac.COUNTRY)
       )
       AND msg2.M_MIN_DATE < TO_DATE('20250101 000000', 'yyyyMMdd HH24miss')
       AND msg2.M_MIN_DATE >= TO_DATE('20240601 000000', 'yyyyMMdd HH24miss')
       AND msg2.is_ieca = 0
   )
   AND MOV.MRN IN (
       SELECT msg3.MRN
       FROM MSG_EXC_REC msg3
       WHERE msg.DOMAIN = msg3.DOMAIN
       AND msg.ENV = msg3.ENV
       AND msg.MRN = msg3.MRN
       AND (
           (msg3.MSG_TYPE IN ('CD501B-MSG.ECS', 'CD501C-MSG.ECS')
            AND msg3.SENDER = ac.COUNTRY)
           OR (msg3.MSG_TYPE IN ('CD503B-MSG.ECS', 'CD503C-MSG.ECS')
               AND msg3.SENDER = ac.COUNTRY
               AND msg3.MSG_SIZE > 400)
       )
       AND msg3.M_MIN_DATE < TO_DATE('20250101 000000', 'yyyyMMdd HH24miss')
       AND msg3.M_MIN_DATE >= TO_DATE('20240601 000000', 'yyyyMMdd HH24miss')
       AND msg3.is_ieca = 0
   )
   GROUP BY msg.mrn, mov.sender, mov.receiver, mov.state, mov.min_date
)
ORDER BY mrn
FETCH FIRST 1048575 ROWS ONLY;
-- query 2
WITH concatenated_countries AS (
   SELECT 'AT,BE,BG,CY,CZ,DE,DK,EE,EL,ES,FI,FR,HR,HU,IE,IT,LT,LU,LV,MT,NL,PL,PT,RO,SE,SI,SK,XI' str 
   FROM dual
),
all_countries AS (
   SELECT regexp_substr(
            str,
            '[^,]+',
            1,
            level
          ) country
   FROM concatenated_countries
   CONNECT BY level <= length(str) - length(replace(str, ',')) + 1
) 
SELECT mrn,
      CASE WHEN sender = 'EL' THEN 'GR' ELSE sender END AS "MS_OFFICE_OF_EXPORT",
      CASE WHEN receiver = 'EL' THEN 'GR' ELSE receiver END AS "MS_OFFICE_OF_EXIT",
      state,
      to_char(min_date, 'yyyyMM') AS "YearMonth",
      pattern
FROM (
   SELECT msg.mrn,
          mov.sender,
          mov.receiver,
          mov.state,
          mov.min_date,
          listagg(REGEXP_REPLACE(msg_type, '-MSG.[A-Z]+', '') ||
                 '-' || msg.sender || '-' || msg.recipient || '-' ||
                 CASE WHEN msg.msg_size > 400 THEN 'over' ELSE 'under' END,
                 '_' ON OVERFLOW TRUNCATE '...') 
          WITHIN GROUP (ORDER BY m_min_date, msg_type ASC) AS pattern
   FROM movement mov
   INNER JOIN msg_exc_rec msg ON (
       mov.mrn = msg.mrn
       AND mov.env = msg.env
       AND mov.domain = msg.domain
   )
   INNER JOIN all_countries ac ON (1=1)
   WHERE mov.domain = 'ECS'
   AND mov.receiver = ac.COUNTRY
   AND mov.sender NOT IN (ac.COUNTRY, 'OL')
   AND msg.is_ieca = 0
   AND mov.MIN_DATE < TO_DATE('20250101 000000', 'yyyyMMdd HH24miss')
   AND mov.MIN_DATE >= TO_DATE('20240601 000000', 'yyyyMMdd HH24miss')
   AND MOV.MRN NOT IN (
       SELECT msg2.MRN
       FROM MSG_EXC_REC msg2
       WHERE msg.DOMAIN = msg2.DOMAIN
       AND msg.ENV = msg2.ENV
       AND msg.MRN = msg2.MRN
       AND (
           (msg2.MSG_TYPE IN ('CD524C-MSG.ECS', 'CD524B-MSG.ECS')
            AND msg2.RECIPIENT = ac.COUNTRY)
           OR (msg2.MSG_TYPE IN ('CD510A-MSG.ECS', 'CD510C-MSG.ECS')
               AND msg2.RECIPIENT = ac.COUNTRY)
           OR (msg2.MSG_TYPE IN ('CD518C-MSG.ECS', 'CD518B-MSG.ECS')
               AND msg2.SENDER = ac.COUNTRY)
       )
       AND msg2.M_MIN_DATE < TO_DATE('20250101 000000', 'yyyyMMdd HH24miss')
       AND msg2.M_MIN_DATE >= TO_DATE('20240601 000000', 'yyyyMMdd HH24miss')
       AND msg2.is_ieca = 0
   )
   AND MOV.MRN IN (
       SELECT msg3.MRN
       FROM MSG_EXC_REC msg3
       WHERE msg.DOMAIN = msg3.DOMAIN
       AND msg.ENV = msg3.ENV
       AND msg.MRN = msg3.MRN
       AND (
           (msg3.MSG_TYPE IN ('CD501B-MSG.ECS', 'CD501C-MSG.ECS')
            AND msg3.RECIPIENT = ac.COUNTRY)
           OR (msg3.MSG_TYPE IN ('CD503B-MSG.ECS', 'CD503C-MSG.ECS')
               AND msg3.RECIPIENT = ac.COUNTRY
               AND msg3.MSG_SIZE > 400)
       )
       AND msg3.M_MIN_DATE < TO_DATE('20250101 000000', 'yyyyMMdd HH24miss')
       AND msg3.M_MIN_DATE >= TO_DATE('20240601 000000', 'yyyyMMdd HH24miss')
       AND msg3.is_ieca = 0
   )
   GROUP BY msg.mrn, mov.sender, mov.receiver, mov.state, mov.min_date
)
ORDER BY mrn
FETCH FIRST 1048575 ROWS ONLY;

The execution duration for each query is
query 1 --> 56:50 MIN:SEC
query 2 --> 10:12:01 HH:MIN:SEC

The execution plans are as below

query 1


Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("from$_subquery$_012"."rowlimit_$$_rownumber"<=1048575)
3 - filter(ROW_NUMBER() OVER ( ORDER BY "MSG"."MRN")<=1048575)
7 - access("MOV"."SENDER"="AC"."COUNTRY")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
filter("MOV"."RECEIVER"<>"AC"."COUNTRY")
9 - filter(LEVEL<=28)
12 - filter("MOV"."MIN_DATE">=TIMESTAMP' 2024-06-01 00:00:00' AND "MOV"."DOMAIN"='ECS' AND "MOV"."RECEIVER"<>'OL' AND
"MOV"."MIN_DATE"<TIMESTAMP' 2025-01-01 00:00:00')
13 - filter("MSG"."IS_IECA"=0)
14 - access("MOV"."MRN"="MSG"."MRN" AND "MSG"."DOMAIN"='ECS' AND "MOV"."ENV"="MSG"."ENV")
filter( NOT EXISTS (SELECT 0 FROM "MSG_EXC_REC" "MSG2" WHERE :B1=:B2 AND "MSG2"."ENV"=:B3 AND "MSG2"."DOMAIN"=:B4 AND "MSG2"."MRN"=:B5
AND "MSG2"."M_MIN_DATE">=TIMESTAMP' 2024-06-01 00:00:00' AND ("MSG2"."RECIPIENT"=:B6 AND ("MSG2"."MSG_TYPE"='CD518B-MSG.ECS' OR
"MSG2"."MSG_TYPE"='CD518C-MSG.ECS') OR "MSG2"."SENDER"=:B7 AND ("MSG2"."MSG_TYPE"='CD524B-MSG.ECS' OR "MSG2"."MSG_TYPE"='CD524C-MSG.ECS') OR
"MSG2"."SENDER"=:B8 AND ("MSG2"."MSG_TYPE"='CD510A-MSG.ECS' OR "MSG2"."MSG_TYPE"='CD510C-MSG.ECS') OR "MSG2"."MSG_TYPE"='CD901B-MSG.ECS' AND
"MSG2"."RECIPIENT"=:B9) AND "MSG2"."IS_IECA"=0 AND "MSG2"."M_MIN_DATE"<TIMESTAMP' 2025-01-01 00:00:00'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
15 - filter(:B1=:B2)
16 - filter("MSG2"."M_MIN_DATE">=TIMESTAMP' 2024-06-01 00:00:00' AND ("MSG2"."RECIPIENT"=:B1 AND ("MSG2"."MSG_TYPE"='CD518B-MSG.ECS' OR
"MSG2"."MSG_TYPE"='CD518C-MSG.ECS') OR "MSG2"."SENDER"=:B2 AND ("MSG2"."MSG_TYPE"='CD524B-MSG.ECS' OR "MSG2"."MSG_TYPE"='CD524C-MSG.ECS') OR
"MSG2"."SENDER"=:B3 AND ("MSG2"."MSG_TYPE"='CD510A-MSG.ECS' OR "MSG2"."MSG_TYPE"='CD510C-MSG.ECS') OR "MSG2"."MSG_TYPE"='CD901B-MSG.ECS' AND
"MSG2"."RECIPIENT"=:B4) AND "MSG2"."IS_IECA"=0 AND "MSG2"."M_MIN_DATE"<TIMESTAMP' 2025-01-01 00:00:00')
17 - access("MSG2"."MRN"=:B1 AND "MSG2"."DOMAIN"=:B2 AND "MSG2"."ENV"=:B3)
18 - filter("MSG3"."M_MIN_DATE">=TIMESTAMP' 2024-06-01 00:00:00' AND "MSG3"."SENDER"="AC"."COUNTRY" AND
(("MSG3"."MSG_TYPE"='CD501B-MSG.ECS' OR "MSG3"."MSG_TYPE"='CD501C-MSG.ECS') OR "MSG3"."MSG_SIZE">400 AND ("MSG3"."MSG_TYPE"='CD503B-MSG.ECS'
OR "MSG3"."MSG_TYPE"='CD503C-MSG.ECS')) AND "MSG3"."IS_IECA"=0 AND "MSG3"."M_MIN_DATE"<TIMESTAMP' 2025-01-01 00:00:00')
19 - access("MSG"."MRN"="MSG3"."MRN" AND "MSG3"."DOMAIN"='ECS' AND "MSG"."ENV"="MSG3"."ENV")
filter("MOV"."MRN"="MSG3"."MRN" AND "MSG"."DOMAIN"="MSG3"."DOMAIN")

55 rows selected.

query 2

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("from$_subquery$_012"."rowlimit_$$_rownumber"<=1048575)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 - filter(ROW_NUMBER() OVER ( ORDER BY "MSG"."MRN")<=1048575)
10 - filter(LEVEL<=28)
13 - filter("MSG3"."DOMAIN"='ECS' AND (("MSG3"."MSG_TYPE"='CD501B-MSG.ECS' OR "MSG3"."MSG_TYPE"='CD501C-MSG.ECS') OR "MSG3"."MSG_SIZE">400
AND ("MSG3"."MSG_TYPE"='CD503B-MSG.ECS' OR "MSG3"."MSG_TYPE"='CD503C-MSG.ECS')) AND "MSG3"."IS_IECA"=0)
14 - access("MSG3"."M_MIN_DATE">=TIMESTAMP' 2024-06-01 00:00:00' AND "MSG3"."RECIPIENT"="AC"."COUNTRY" AND "MSG3"."M_MIN_DATE"<TIMESTAMP'
2025-01-01 00:00:00')
filter("MSG3"."RECIPIENT"="AC"."COUNTRY")
15 - filter("MOV"."MIN_DATE">=TIMESTAMP' 2024-06-01 00:00:00' AND "MOV"."RECEIVER"="AC"."COUNTRY" AND "MOV"."SENDER"<>"AC"."COUNTRY" AND
"MOV"."SENDER"<>'OL' AND "MOV"."MIN_DATE"<TIMESTAMP' 2025-01-01 00:00:00')
16 - access("MOV"."MRN"="MSG3"."MRN" AND "MOV"."DOMAIN"='ECS')
17 - access("MSG"."MRN"="MSG3"."MRN" AND "MSG"."DOMAIN"='ECS' AND "MSG"."ENV"="MSG3"."ENV")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
filter("MOV"."MRN"="MSG"."MRN" AND "MOV"."ENV"="MSG"."ENV" AND "MSG"."DOMAIN"="MSG3"."DOMAIN" AND NOT EXISTS (SELECT 0 FROM
"MSG_EXC_REC" "MSG2" WHERE :B1=:B2 AND "MSG2"."ENV"=:B3 AND "MSG2"."DOMAIN"=:B4 AND "MSG2"."MRN"=:B5 AND "MSG2"."M_MIN_DATE">=TIMESTAMP'
2024-06-01 00:00:00' AND ("MSG2"."SENDER"=:B6 AND ("MSG2"."MSG_TYPE"='CD518B-MSG.ECS' OR "MSG2"."MSG_TYPE"='CD518C-MSG.ECS') OR
"MSG2"."RECIPIENT"=:B7 AND ("MSG2"."MSG_TYPE"='CD524B-MSG.ECS' OR "MSG2"."MSG_TYPE"='CD524C-MSG.ECS') OR "MSG2"."RECIPIENT"=:B8 AND
("MSG2"."MSG_TYPE"='CD510A-MSG.ECS' OR "MSG2"."MSG_TYPE"='CD510C-MSG.ECS')) AND "MSG2"."IS_IECA"=0 AND "MSG2"."M_MIN_DATE"<TIMESTAMP'
2025-01-01 00:00:00'))
18 - filter(:B1=:B2)
19 - filter("MSG2"."M_MIN_DATE">=TIMESTAMP' 2024-06-01 00:00:00' AND ("MSG2"."SENDER"=:B1 AND ("MSG2"."MSG_TYPE"='CD518B-MSG.ECS' OR
"MSG2"."MSG_TYPE"='CD518C-MSG.ECS') OR "MSG2"."RECIPIENT"=:B2 AND ("MSG2"."MSG_TYPE"='CD524B-MSG.ECS' OR "MSG2"."MSG_TYPE"='CD524C-MSG.ECS')
OR "MSG2"."RECIPIENT"=:B3 AND ("MSG2"."MSG_TYPE"='CD510A-MSG.ECS' OR "MSG2"."MSG_TYPE"='CD510C-MSG.ECS')) AND "MSG2"."IS_IECA"=0 AND
"MSG2"."M_MIN_DATE"<TIMESTAMP' 2025-01-01 00:00:00')

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
20 - access("MSG2"."MRN"=:B1 AND "MSG2"."DOMAIN"=:B2 AND "MSG2"."ENV"=:B3)
21 - filter("MSG"."IS_IECA"=0)

57 rows selected.

As you can see, the execution plans for the first and second queries are different.
Could you suggest any ways to improve performance?

I was considering creating two materialized views—one for each sheet—but I believe 10 hours should be sufficient processing time.

Below, I have also listed the relevant indexes (displayed in execution plans):

IDX_MSG_EXC_MMINDATE_SEN_REC →
CREATE INDEX "IDX_MSG_EXC_MMINDATE_SEN_REC" ON "MSG_EXC_REC" ("M_MIN_DATE", "SENDER", "RECIPIENT");
IDX_MOVEMENT_MRN →
CREATE UNIQUE INDEX "IDX_MOVEMENT_MRN" ON "MOVEMENT" ("MRN", "DOMAIN", "ENV", "CRN");
IDX_MSG_EXC_REC_MRN_DOMAIN_ENV →
CREATE INDEX "IDX_MSG_EXC_REC_MRN_DOMAIN_ENV" ON "MSG_EXC_REC" ("MRN", "DOMAIN", "ENV");

Comments

chonewell Nov 12 2024

My Oracle Cloud tenant, cloud account, and secure email have no issues. Why haven't I received my password reset email for Oracle Cloud? This is very strange, and our attempts have not been able to solve the problem. May I ask who I should turn to for help?

L. Fernigrini Nov 12 2024

If your account is a paid one, open a Support ticket.

If it is a Free Tier then you will have to rely on help from the community. Most probable cause that you did not receive the password reset email is that your account has been stolen and the email has been changed.

chonewell Nov 13 2024

Thank you for your reply!
But when I chatted with the online customer service, they told me that my Oracle Cloud tenant, account, and email were all fine. So, there shouldn't be a problem of theft.
I have a free account, but who can I contact on the forum? I can only post, but no one on the forum can view my account permissions, right. I am currently trying to reset MFA, I don't know if it works.
It's quite ridiculous that I have a free account and can't enjoy any services, but how can I become a paid user if I can't log in to my account.

1 - 3

Post Details

Added on Feb 6 2025
5 comments
229 views