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

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Tue, 22 Sep 2009 10:08:50 -0700
Message-ID: <64BAF54438380142A0BF94A23224A31E112B74FE1E_at_ONEWS06.oneneck.corp>



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_at_roughsea.com] Sent: Monday, September 21, 2009 10:18 PM To: Allen, Brandon
Cc: oracle-l_at_freelists.org
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. i0zX+n{+i^ Received on Tue Sep 22 2009 - 12:08:50 CDT

Original text of this message