Re: Optimizer ignoring hints
- From: Hans Forbrich <fuzzy.graybeard@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 12 Sep 2013 10:15:37 -0600
On 12/09/2013 8:16 AM, Hameed, Amir wrote:
> However, if I put the statement in a script and replace the bind variables
> with SYSDATE and use hints, the optimizer takes the hints and produces the
> plan I am trying to drive.
What do you mean 'put the statement in a script'? (I assume you mean,
run in SQL*Plus or Toad, but want to make sure.)
The optimizer *always* uses valid hints, but not necessarily in the way
expected. In loose terms, I usually say the hint "adjusts the cost" of
the hinted path, and that revised cost is used in the optimizer's
evaluation. That does NOT mean the optimizer will use the suggested
path, but rather that the path is considered 'more strongly'.
The fact that it is happy with SYSDATE seems to indicate the optimizer
is not getting enough information to make decent decision on those
specific columns. Do you have recent, accurate, statistics (and perhaps
histograms) for those date columns? Do you have samples of :b1 and :b2
used as input, and how do they fit into the column data range?
/Hans
--
//www.freelists.org/webpage/oracle-l
Other related posts:
