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

  • From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 21 Sep 2009 14:27:18 -0700

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

Other related posts:

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