Query re-optimization causes run time of at least 3x longer
Oracle Database 19.9, Linux 7.x on bare metal. Large query, 600+ lines (can post Monitor report if needed later). Many columns of most tables are in-memory (if that's somehow relevant). Runs in 7 minutes as expected:
But V$SQL shows:
When running again moments later with no other system activity, the plan does change to something horrible, runs for 20+ minutes then bombs on using 1 TB of TEMP tablespace.
And V$SQL shows that the second run is from a "reoptimized" child cursor:
So how do I figure out why "cardinality/statistics feedback" made subsequent executions horribly longer? Without actually disabling a useful feature otherwise???