Database Tuning (MOSC)

MOSC Banner

Bind Variable - Optimizer Explanation?

edited Nov 27, 2018 4:01AM in Database Tuning (MOSC) 6 commentsAnswered

Can't seem to figure out why the Optimizer uses access vs. filter with to_number() for the bind vs. using access("WDICUA"=KEY1)...      Overall, the query performs OK, but after the initial "consistent get", I'd expect the subsequent queries to use the first plan based  on cost.  I know bind variables can throw a curve ball, but the variable is declared as NUMBER?    Why does a BIND VARIABLE NUMBER not use the better plan?

11.2.0.4.0

If the KEY1 bind is delivered later, the FULL SCAN is always used.

What am I missing?

> set echo on

> set autotrace on

> select * from proddta910.f4812h

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