Re: Optimizer ignoring hints
From: Hans Forbrich <fuzzy.graybeard_at_gmail.com>
Date: Thu, 12 Sep 2013 10:15:37 -0600
Message-ID: <5231E8A9.6030600_at_gmail.com>
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.)
Date: Thu, 12 Sep 2013 10:15:37 -0600
Message-ID: <5231E8A9.6030600_at_gmail.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Sep 12 2013 - 18:15:37 CEST