1. If the job never completes, the SQL execution is not optimal on the SaaS environment.
After profiling the SQL, the job runtime was brought down from hours to within 15 minutes completion time.
The SaaS database solution of ensuring the best SQL execution plan for this job can be applied to any environment for subsequent runs.
The database profiler selects a non-optimal plan based on the ALL_SYNONYMS table, therefore it is best to use USER_SYNONYMS table instead.
The following SQL is to be profiled:
SELECT SYNONYM_NAME FROM ALL_SYNONYMS WHERE TABLE_OWNER = :v0 and TABLE_NAME = :v1 and OWNER = :v2
An improvement will result as a result of using the SQL Tuning Advisor.
2. If the job eventually fails, the HDP (Hybrid Data Pipeline) server needs to be restarted in addition to applying the best execution plan.