Re: Optimizer ignoring hints
From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Fri, 13 Sep 2013 11:39:46 +0200 (CEST)
Message-ID: <61153.213.162.65.111.1379065186.bloek_at_pwebmail5.utanet.at>
Hi,
Date: Fri, 13 Sep 2013 11:39:46 +0200 (CEST)
Message-ID: <61153.213.162.65.111.1379065186.bloek_at_pwebmail5.utanet.at>
Hi,
> 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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 13 2013 - 11:39:46 CEST