Query optimization for HCM audit report with UNION ALL + UNPIVOT - need hints/index strategy

Summary:

My audit trail query (HCM objects with UNION ALL for 30+ audit tables) was taking 12 minutes with left join on tables per_all_assignments_m and per_all_people_f , but when I added fallback joins to per_all_assignments_m_ and per_all_people_f_ audit tables (to catch records not in current tables), performance degraded to 22 minutes

.

Content (please ensure you mask any confidential information):

the query structure is like ,
SELECT * FROM (
SELECT (audit_data + UNPIVOT) from ANC_PER_ABS_ENTRIES_ audit table
UNPIVOT col_value...

UNION ALL

SELECT (audit_data + UNPIVOT) from ANC_PERACCRUAL_DETAILS_ audit table