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

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Mon, 21 Sep 2009 14:27:18 -0700
Message-ID: <64BAF54438380142A0BF94A23224A31E112B6887C5_at_ONEWS06.oneneck.corp>

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 3rd 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:

Bind variable captured in 10046 trace file:

Bind#17
  oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00   oacflg=00 fl2=1000000 frm=00 csi=00 siz=0 off=64   kxsbbbfp=2ad7369d36a0 bln=07 avl=07 flg=01   value="9/21/2009 0:0:0"

Bind variable captured in v$sql_bind_capture:

SYS_at_test500>select datatype_string, value_string from v$sql_bind_capture where hash_value = 2231884975 and name = ':P5A';

DATATYPE_STRING VALUE_STRING
--------------- --------------------

DATE 09/21/09 00:00:00 Coming from the 3rd party app, the estimated cardinality is only 2003 rows from the index and 11,128 (5% of num_rows) from the table:

SYS_at_test500>select num_rows from dba_tables where table_name = 'AR_DETAIL';

  NUM_ROWS

---

    222567

---

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |


-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             | 11128 |   717K|    45   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| AR_DETAIL   | 11128 |   717K|    45   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | AR_DETAIL_2 |  2003 |       |     8   (0)| 00:00:01 |


-------------------------------------------------------------------------------------------

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:

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

|   0 | SELECT STATEMENT   |           |   222K|    14M|   649   (1)| 00:00:08 |
|*  1 |  FILTER            |           |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| AR_DETAIL |   222K|    14M|   649   (1)| 00:00:08 |


--------------------------------------------------------------------------------

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.

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

Received on Mon Sep 21 2009 - 16:27:18 CDT