Re: Optimizer ignoring hints

From: jaromir nemec <>
Date: Fri, 13 Sep 2013 11:39:46 +0200 (CEST)
Message-ID: <>


> However, if I put the statement in a script and replace the bind
variables with SYSDATE ...

This is a frequent source of confusion as a literal (here SYSDATE) is used in the statement instead of a bind variable, which can lead to a different (probably better) plan.
You may try to explain plan using bind variables:

EXPLAIN PLAN SET STATEMENT_ID = 'jara1' into plan_table FOR SELECT * from dual where dummy = :x;

That should lead to the same (bad) execution plan, so you can start playing with hints and see their effect. Additionally, if you observe significant difference in the execution plan with BV and with literals you may proceed with topic as “BV peek” and “adaptive cursor sharing”

HTH Jaromir D.B. Nemec

Received on Fri Sep 13 2013 - 11:39:46 CEST

Original text of this message