Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Possible Tuning query

Prokopios PoulimenosMar 26 2024 — edited Mar 26 2024

Hi all,
I need some assistance in optimizing the performance of a query.

To provide some context, I have two tables structured in a MASTER-DETAIL relationship.
The first table is named MOVEMENT, and the second one is MOVEMENT_MESSAGE.
These tables are linked through the relationship MOVEMENT.ID = MOVEMENT_MESSAGE.MOVEMENT_ID.

Basic structure of each table

Additionally, the MOVEMENT table is partitioned by month using the following partitioning scheme:

PARTITION BY RANGE ("MIN_DATE") INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))

Furthermore, there are the following indexes

CREATE INDEX "IDX_MOV_STATE" ON "CSMIS2_USER"."MOVEMENT" ("STATE", "MIN_DATE", "MAX_DATE");
CREATE INDEX "IDX_MOVEMENT_MSG_ID" ON "CSMIS2_USER"."MOVEMENT_MESSAGE" ("MOVEMENT_ID") ;

I have the below query in order to check based on a state of movement and state_modification_date if a number of days is exceeded.

SELECT 
   mov_send, 
   mov_rec, 
   domain, 
   mrn, 
   state, 
   LISTAGG(SUBSTR(msg_type, 3, 4), '') WITHIN GROUP (ORDER BY SEND_DATE) msg_list, 
   LISTAGG(msg_send, '') WITHIN GROUP (ORDER BY SEND_DATE) send_list, 
   LISTAGG(msg_rec, '') WITHIN GROUP (ORDER BY SEND_DATE) rcv_list, 
   min_date, 
   max_date, 
   EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) AS days_stuck 
FROM (
   SELECT 
       a.SENDER mov_send, 
       a.RECEIVER mov_rec, 
       b.MSG_TYPE msg_type, 
       b.SENDER msg_send, 
       b.RECEIVER msg_rec, 
       a.MRN mrn, 
       b.SEND_DATE, 
       a.domain, 
       a.state, 
       a.min_date, 
       a.max_date, 
       a.state_modification_date 
   FROM MOVEMENT a 
   INNER JOIN MOVEMENT_MESSAGE b ON a.ID = b.MOVEMENT_ID 
   WHERE a.ID IN (
       SELECT id 
       FROM movement 
       WHERE min_date >= SYSDATE - 180
           AND min_date <= SYSDATE
           AND (
               (domain = 'NCTS' AND (
                   (state_modification_date IS NOT NULL AND state = 'Initial' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :1) OR 
                   (state_modification_date IS NOT NULL AND state = 'Incidentregistered' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :2) OR 
                   (state_modification_date IS NOT NULL AND state = 'Movementleftsecurityarea' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :3) OR 
                   (state_modification_date IS NOT NULL AND state = 'Notificationcrossingfrontier' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :4) OR 
                   (state_modification_date IS NOT NULL AND state = 'Unknown' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :5) OR 
                   (state_modification_date IS NOT NULL AND state = 'Underenquiryprocedure' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :6) OR 
                   (state_modification_date IS NOT NULL AND state = 'Released' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :7) OR 
                   (state_modification_date IS NOT NULL AND state = 'Recoveryrecommended' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :8) OR 
                   (state_modification_date IS NOT NULL AND state = 'Arrived' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :9) OR 
                   (state_modification_date IS NOT NULL AND state = 'Underrecoveryprocedure' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :10)
               )) 
               OR 
               (domain = 'ECS' AND (
                   (state_modification_date IS NOT NULL AND state = 'Initial' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :11) OR 
                   (state_modification_date IS NOT NULL AND state = 'UnderExitConfirmationRequest' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :12) OR 
                   (state_modification_date IS NOT NULL AND state = 'AwaitingforPCOControlDecision' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :13) OR 
                   (state_modification_date IS NOT NULL AND state = 'ReleasedforExport' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :14) OR 
                   (state_modification_date IS NOT NULL AND state = 'Unknown' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :15)
               ))
           )
   )
   AND IS_IECA = 0 
   AND ENV = 'OP'
) 
GROUP BY mov_send, mov_rec, mrn, domain, state, min_date, max_date, state_modification_date;

, where :1 until :15 are configuration binding variables.

This query takes about 406 seconds to be executed, so because the transaction timeout is set to 5 minutes, it fails.

Attached you can find the execution plan explain_plan.txt

Is there any way to improve the query ?
Additionally, if we assume that all parameters have the same value (let's say 3), can the restructuring process be simplified?

Thanks in advance for your time

Comments
Post Details
Added on Mar 26 2024
9 comments
141 views