PX query plan gets 2 more PX steps and now runs 12 hrs on 19c vs 10 minutes previously on 12.1.0.2
Query is this (table names masked):
select auditlog2.
from audit_log2
left outer join audit_log on audit_log2.access_instant = audit_log.access_instant
left outer join pat_enc pe on audit_log.csn = pe.pat_enc_csn_id
left outer join depts dep_csn on pe.effective_dept_id = dep_csn.department_id
where audit_log.access_time >= efn_din('t-1') and audit_log.access_time < efn_din('T')
and (dep_csn.serv_area_id in (30,50) or dep_csn.serv_area_id is null);
Perhaps I can rewrite the query to filter sooner, etc. BUT it now runs in 12 hours on 19c versus 10 minutes under 12.1.0.2. 19c has latest RU; all statistics up to date; EXECUTION PLAN has slight changes to PX steps, PLUS a new PX SEND/RECEIVE step. Data volumes slightly more now from daily ETL. Short of trying to create a SQL profile using the old hash value (SQL_ID is of course the same),