0

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?

Improve this question
1

1 Answer 1

Reset to default
0

Whenever the optimizer looks at a query to produce a plan the relevant statistics are examined and it will compare the cost of various plans and pick the cheapest. It doesn't look at all possible plans as it can 'shortcut' some options.

An execution plan will be produced whenever a hard parse occurs and this only happens when the query to be executed does not already exist in the shared pool.

So . . . the execution plan might change over time as your database may change over time, e.g. that small history table you had last year now has 20million rows rather than 10,000 so the optimiser decides to use an index instead of the full scan.

I've come across many 'tipping points' for the optimizer in the past but they are hard to predict at a granular level because we don't know exactly what algorithms the optimizer uses.

Oracle wont however change its execution plan 'just to see' if it already has a plan for the query.

Maybe the person you spoke to was referring to the new adaptive plans in Oracle 12c but that still doesn't mean Oracle will sometimes try a different plan. Look here for info on adaptive plans https://blogs.oracle.com/optimizer/entry/what_s_new_in_12c

Improve this answer
3
  • Oracle might actually create multiple plans for a single statement if that uses bind variables through a feature called "bind variable peeking". You can check v$sql to find all child cursors for a single statement.
    – user1822
    Commented May 27, 2016 at 8:53
  • @a_horse_with_no_name - damn, forgot all about bind variables.
    – BriteSponge
    Commented May 27, 2016 at 9:04
  • Thank you very much. This suits my understanding. Unfortunately I have not enough reputation to up vote you ;)
    – Seb
    Commented May 30, 2016 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.