Hello,
Two weeks ago a Peoplesoft release in a 18c DB made INSERTs (that previously would execute within seconds) dramatically deteriorate (actual duration now: around half an hour).
I've found the cause (an original litteral hard-coded date in a greater-or-equal-to comparison with column date, now replaced with a sub-query - if I try removing this subquery, these inserts start performing very well again).
Now these inserts take place with same explain plan as before, with very bad consequences (one of tables joined is traversed thru its unique index). It is not possible to modify SQL code (add a hint for example). I know that if I specify a hint to use 2nd index of this table (non-unique) then an insert executes in 2 to 3 minutes: if I uploaded here a full 10053 trace with some explanation, would someone be able to figure out where and how optimizer ends up keeping the same unique index, and maybe tell me what missing/incomplete statistics make it keep using this now become bad unique index ?
In advance - thanks.
Regards,
Seb