main reasons:
1) The optimizer statistics the EXPLAIN PLAN ends up using are different from the statistics the other session ended up using
2) Explain plan does not use bind variable peeking thus will not optimize for current bind variable values
3) Explain plan treats all bind variables as VARCHAR2, thus you ma have implicit datatype conversion happening during the plan execution, (meaning to_char,to_number functions are added around variables/columns) and this for example may make optimizer to ignore some indexes if you get unlucky.
1) The optimizer statistics the EXPLAIN PLAN ends up using are different from the statistics the other session ended up using
2) Explain plan does not use bind variable peeking thus will not optimize for current bind variable values
3) Explain plan treats all bind variables as VARCHAR2, thus you ma have implicit datatype conversion happening during the plan execution, (meaning to_char,to_number functions are added around variables/columns) and this for example may make optimizer to ignore some indexes if you get unlucky.
with the caveat:
Little correction to point number 3 - of course explain plan doesn't really execute the plan so the implicit datatype conversion you see is in the explained plan only, but if you actually execute the statement (with correct bind datatypes) then there's no implicit datatype conversion. And that's where the difference comes from...
From the presentation Friday at NoCOUG:
more examples on previous blog entry
No comments:
Post a Comment