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

From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
To: Stephane Faroult <sfaroult@xxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 22 Sep 2009 10:08:50 -0700

Thanks Stephane, but I just checked the table and it looks like none of the 
records are storing the time:

        select distinct to_char(acctg_date,'HH24:MI:SS') from ar_detail;

        TO_CHAR(
        --------
        00:00:00

Yes, you're right about the varchar2 in SQL*Plus:

        variable v1 varchar2(9);
        begin :v1 := '21-AUG-09';
        end;
        /

And I've tried both converting it explicitly:

        select /*batest*/ * from ar_detail where acctg_date < to_date(:v1);

And, letting Oracle do the implicit conversion itself:

        select /*batest*/ * from ar_detail where acctg_date < :v1;

With both of the above queries, Oracle does peek at the value and sees that 
'21-AUG-09' is greater than the max value of the column and so it does a full 
scan as expected, however for the 3rd party app, it seems to not peek and 
instead just guesses that it will only return 5% of the rows so it uses the 
index.  According to the 10046 trace and v$sql_bind_capture, the app does 
indeed declare the variable as type DATE, so there shouldn't be any conversion 
required that would affect the peeking, however in the 10053 trace, it says "No 
bind buffers allocated" for this and all other bind variables of this query.  
There are 19 bind variables in the query in total, but this one on the 
acctg_date seems to be the main one that is causing the poor plan choice so I'm 
focused on it for now.  I'm wondering if maybe there is a limit to the number 
of bind variables that will be peeked?  I know in Kerry Osborne's blog 
(http://kerryosborne.oracle-guy.com/2009/06/oracle-11g-adaptive-cursor-sharing-acs/)
 he found that there was a limit of 14 bind variables for Adaptive Cursor 
Sharing, so I'm wondering if there is a similar limit for bind variable peeking 
in general.

Regards,
Brandon

-----Original Message-----
From: Stephane Faroult [mailto:sfaroult@xxxxxxxxxxxx]
Sent: Monday, September 21, 2009 10:18 PM
To: Allen, Brandon
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Date bind variable being peeked, but not used for cardinality 
estimate

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
>
>
>

Privileged/Confidential Information may be contained in this message or 
attachments hereto. Please advise immediately if you or your employer do not 
consent to Internet email for messages of this kind. Opinions, conclusions and 
other information in this message that do not relate to the official business 
of this company shall be understood as neither given nor endorsed by it.

References:
- Date bind variable being peeked, but not used for cardinality estimate
  - From: Allen, Brandon
- Re: Date bind variable being peeked, but not used for cardinality estimate
  - From: Stephane Faroult

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

Other related posts: