0

When generating an explain plan for a query my system executes, I notice that if I leave filters in their parameterized form (e.g. "somecolumn=:param1") the explain plan is different when I replace the parameter with a real value (e.g. "somecolumn='real_value'). In my case, the explain plan with the database parameters ends up ignoring indexes and does full table scans on massive tables leading to high cost, bytes and estimated rows.

Why do the plans differ? Is Oracle using the less than desirable plan because the system uses bind parameters?

spots
  • 2,483
  • 5
  • 23
  • 38
  • 1
    With a bind variable Oracle can't estimate the number of rows that are affected by the (`where`) condition, therefor it uses an "one size fits all" execution plan. This is usually mitigated by a process called "bind variable" peeking in recent versoins. –  May 03 '16 at 13:40
  • So the plan I see when I generate the plan with database params is not the actual plan used? When examining explain plans for queries, should I be replacing the db params with "real" values so I can see an accurate plan? – spots May 03 '16 at 13:46
  • It's a double edged sword. Generally, you can avoid a couple of db steps by using :variables. It's only really worth doing if the query is executed a lot. The downside to not constructing queries this way is the possibility that code could be injected during the statement creation and submission process. – T Gray May 03 '16 at 14:05
  • My problem is that Oracle appears to generate a better query plan when NOT using :variables. I need to understand if the system is actually using the "good" query plan and why Oracle would choose full table scans over indexes just because of the presence of :variables. – spots May 03 '16 at 14:57

0 Answers0