I had a talk with an external colleague about oracle "swinging in" its performance. In detail it was about, that oracle optimizes its execution plans for the same query by time.
I don't talk about filling the caches. What I mean is that oracle tries different execution plans and keeps the one with best performance. This means a query send the 100th time is probably less performant than a query sent the 10000th time.
The Query has to be 100% the same, so index is defined and no parameters change or bind variables are used. Oracle only optimizes by sometimes trying a different execution plan and comparing its performance to the previously executed plan.
I would really appreciate if some of the Oracle pros could make this clear for me because I doubt this statement. Does Oracle really randomly switch execution plans to check if there is a better solution?