SQL Performance (MOSC)

MOSC Banner

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???

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center