Explain plan for long running query in oracle sql developer

0
votes

I have below select query which is long running and i want to view the query execution plan to understand why this query is long running and which statement in sql query affecting the query performance. I am using oracle sql developer and i checked the explain plan for the below query but did not understood it clearly which statement is effecting my query so as to optimize my query.

Select *
from PROVISIONING_LOG@FONIC_RETAIL PL 
JOIN PROVISIONING_TASK@FONIC_RETAIL PT ON PL.PROVISIONING_TASK_ID = PT.ID JOIN SERVICE@FONIC_RETAIL SER ON PT.SERVICE_ID = SER.ID
JOIN TEMP_WF_DEF_ALL TT ON SER.SUBSCRIPTION_ID = TT.SUBSCRIPTION_ID
where PT.CODE='MIGOPT_PACK' and PT.DESCRIPTION Like '%CVB Request' AND PT.PARAMETERS LIKE '%OPERATION=ADD%' AND PL.RESPONSE_TYPE IS NULL AND PL.REQUEST IS NOT NULL 
and ((to_char(PT.START_DATE,'YYYYMMDDHH24Mi') = to_char(TT.COMPLETE_DATE,'YYYYMMDDHH24Mi')) 
or (to_char(PT.START_DATE,'YYYYMMDDHH24Mi') = to_char(TT.COMPLETE_DATE + 1/1440,'YYYYMMDDHH24Mi'))) AND
PL.TIME_STAMP < SYSDATE - numtodsinterval (  30,'MINUTE' )
 and PL.TIME_STAMP > SYSDATE - numtodsinterval ( 4,'HOUR' )
 AND TT.START_DATE < SYSDATE - numtodsinterval (  30,'MINUTE' )
and TT.START_DATE > SYSDATE - numtodsinterval ( 4,'HOUR' )
 AND TT.WF_NAME IN
 ('Subscribe LIDL Community Flat',
'LDLMonatsFlatrate Subscribe');

Query execution plan for the above query: enter image description here

1
sqloracleperformancesql-execution-plan
Performing to_char on a table date field to use as a compare may inhibit index usage. What is the purpose of this? "to_char(PT.START_DATE,'YYYYMMDDHH24Mi') = to_char(TT.COMPLETE_DATE,'YYYYMMDDHH24Mi'))" You are converting what I assume is already a DATE type in the table to chars. - OldProgrammer
Hello I have remove those date condition now but still its long running. Actually when i did count it returns 3000 rows. - Andrew

1 Answers

2
votes

As you are using a mix of local tables and remote tables. If the tables on the remote database are larger than the ones on the local database then you might need to use the DRIVING_SITE hint so the smaller of the set of tables are moved to the database issuing the call.

DRIVING_SITE