Query running longer

Environment :- GI & RDBMS 19.11 + AIX P9 7.2

Batch Team noticed that one of their Informatica Workflow Job is running longer , expected run time is 18mints but its running for ~3Hrs and sometimes they had to kill and restart the job to finish. Upon checking and per batch team, there were two sets of sql queries and all the sqls are using literals and batch team confirmed that there are no increase in data volume. One set is using SUBPARTITION clause for table RXP_PRESCRIPTION_FILL and other is not.

excerpt of query below and attached both type of queries

Without SUBPARTITION

FROM RXP_PRESCRIPTION_FILL, FACILITY, RXP_FILL_USER, RXP_PRESCRIPTION, RXP_PATIENT, RXP_PATIENT_ADDRESS, SRD_ATTRIBUTE_VALUE, RXC_INTK_CROSS_REF WHERE RXP_PRESCRIPTION_FILL.FILL_STATUS = 7 AND RXP_PRESCRIPTION_FILL.RXC_FILL_SOLD_MARK_DATE BETWEEN TO_DATE('20221218' || '230000', 'YYYYMMDDHH24MISS') AND TO_DATE('20221220' || '055959', 'YYYYMMDDHH24MISS')  AND RXP_PRESCRIPTION_FILL.LAST_UPDATED_DATE BETWEEN        TO_DATE('20221218' || '230000', 'YYYYMMDDHH24MISS') AND        TO_DATE('20221218' || '235959', 'YYYYMMDDHH24MISS') With SUBPARTITION

FROM RXP_PRESCRIPTION_FILL SUBPARTITION (RPF_20221220_RPF_SP_09),        FACILITY,        RXP_FILL_USER,        RXP_PRESCRIPTION,        RXP_PATIENT,        RXP_PATIENT_ADDRESS,                SRD_ATTRIBUTE_VALUE, RXC_INTK_CROSS_REF WHERE RXP_PRESCRIPTION_FILL.FILL_STATUS = 7       AND RXP_PRESCRIPTION_FILL.RXC_FILL_SOLD_MARK_DATE BETWEEN        TO_DATE('20221218' || '230000', 'YYYYMMDDHH24MISS') AND        TO_DATE('20221220' || '055959', 'YYYYMMDDHH24MISS')    AND RXP_PRESCRIPTION_FILL.LAST_UPDATED_DATE BETWEEN        TO_DATE('20221219' || '000000', 'YYYYMMDDHH24MISS') AND        TO_DATE('20221219' || '235959', 'YYYYMMDDHH24MISS')    AND RXP_PRESCRIPTION_FILL.FACILITY_ID = FACILITY.FACILITY_ID note that the date (20221220) and sub-partition number (09) keeps change everyday

We were able to temporarily fix the queries using non-sub-partition by creating sql profiles with force matching signature to true.

Since the date and sub-partition number are changing everyday for subpartition queries, we are unable to use sql profiles here. Please let me know any suggestions to fix this queries temporarily.

Batch team updated that they will re-write the query and deploy in next release which will be in Feb 2023. I am looking for any way to fix this temporarily for time being.

Table RXP_PRESCRIPTION_FILL is locked and last statistics are gathered on July 2019. Batch team started complaining about this on Dec 03 2022 and there are no recent changes in this system.

Upgraded from 11.2.0.4.0 to 19.7.0.0.0
18-OCT-2020 Patch applied from 19.7.0.0.0 to 19.11.0.0.0  07-NOV-2021 Thanks,

Mani

--
http://www.freelists.org/webpage/oracle-l




text/plain attachment: non-subpart.txt



text/plain attachment: subpart.txt



text/plain attachment: sub-part.txt