Re: Date bind variable being peeked, but not used for cardinality estimate

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: Brandon.Allen@xxxxxxxxxxx
  • Date: Tue, 22 Sep 2009 07:17:47 +0200

Brandon,

    Just out the top of my head, might it be an "include the time/don't
include the time" thing? If your date column includes the time, then in
terms of distinct values you probably have many, and if you have some
histogram over the column, it's probably a width-based one. Your
VALUE_STRING doesn't include any time part.I don't know if there is a
TRUNC() somewhere, or if it's a TO_CHAR() that is used, but it could
explain difficulties in getting the cardinality.
  Other point, you mention using a bind variable with SQL*Plus. That
means that you have used a VARCHAR2, and converted it to a date in your
query, since SQL*Plus has no DATE variable (even with 11gR2, I have just
checked). In the program, it's quite likely that the date value is bound
as a date, it can make a difference too.

SF

Allen, Brandon wrote:
>
> Hi list, I’m stumped on this one and hoping one of you will see
> something I’m missing.
>
>  
>
> I’ve got a query coming in from a 3^rd party app and it’s using a bind
> variable for a date.  I can see in the 10046 trace file and in
> v$sql_bind_capture that the bind variable is being captured, however
> the CBO seems to be ignoring the value and instead just calculating
> the cardinality using the default of 5% as you can see below:
>
>  
>
> (...)
>
>  
>
> If I run the query with a literal instead, or even with a bind
> variable via SQL*Plus, then the cardinality is estimated correctly and
> a full scan is used instead:
>
>  
>
> (...)
>
> I also don’t understand why it’s estimating only 2003 rows for the
> index, and then 11,128 rows for the table – how can that even be possible?
>
>  
>
> Thanks in advance for any ideas.
>
>  
>
> Brandon
>
>  
>



--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2009